-1

I want to add a column called "Sweep" that contains bools based on whether the "Result" was a sweep or not. So I want the value in the "Sweep" column to be True if the "Result" is '4-0' or '0-4' and False if it isn't.

This is a part of the table:

A part of the table

I tried this:

ALTER TABLE "NBA_finals_1950-2018"
ADD "Sweep" BOOL;

UPDATE "NBA_finals_1950-2018"
    SET "Sweep" = ("Result" = '4-0' OR "Result" = '0-4');

But for some reason, when I run this code...:

SELECT *
FROM "NBA_finals_1950-2018"
ORDER BY "Year";

...only one of the rows (last row) has the value True even though there are other rows where the result is a sweep ('4-0' or '0-4') as shown in the picture below.

enter image description here

I don't know why this is happening but I guess there is something wrong with the UPDATE...SET code. Please help. Thanks in advance.

NOTE: I am using PostgreSQL 13

RKS2
  • 25
  • 11
  • Unrelated to your problem, but: you should really avoid those dreaded quoted identifiers. They are much more trouble than they are worth it. https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_upper_case_table_or_column_names –  Jan 16 '21 at 17:56

1 Answers1

2

This would occur if the strings are not really what they look like -- this is often due to spaces at the beginning or end. Or perhaps to hyphens being different, or other look-alike characters.

You just need to find the right pattern. So so with a select. This returns no values:

select *
from "NBA_finals_1950-2018"
where "Result" in ('4-0', '0-4');

You can try:

where "Result" like '%0-4%' or
      "Result" like '%4-0%'

But, this should do what you want:

where "Result" like '%4%' and
      "Result" like '%0%'

because the numbers are all single digits.

You can incorporate this into the update statement.

Note: double quotes are a bad idea. I would recommend creating tables and columns without escaping the names.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 2
    If you look closely at the image you posted, there are two different-length hyphen characters in the data. The condition `"Result" like '%0%'` should yield what you want. – Steve Kass Jan 16 '21 at 18:08
  • Thank you but I used double quotes because I used double quotes in all other rows (I didn't know it was a bad idea before) – RKS2 Jan 17 '21 at 01:23
  • Steve Kass Thank you I didn't notice that. – RKS2 Jan 17 '21 at 01:25
  • `UPDATE "NBA_finals_1950-2018" SET "Result" = REPLACE("Result", '–', '-') WHERE "Result" LIKE '%–%';` - It's better to make sure all rows have the same type of hyphen so I used this. – RKS2 Jan 17 '21 at 03:00
  • @SuchirKumaravel . . . I agree. – Gordon Linoff Jan 17 '21 at 03:10