UPDATE TABLE where column IS NULL isn't working

Data Science
1

I’m on the PostgreSQL project called " Building an Inventory Database with PostgreSQL".
For step 3, the instructions say…

" The parts table is missing values in the description column. Alter the table so that all rows have a value for description` ."

After trying on my own AND copying and pasting the Hint which was…

UPDATE parts
SET description = 'None Available' 
WHERE description IS NULL;

…my table still isn’t replacing null values with “None Available”. Just to be safe I made sure that they weren’t all just blank spaces so I also tried…

UPDATE parts
SET description = 'None Available' 
WHERE description = '';

This didn’t work either. Is my syntax wrong or is this a bug?

4 Likes
2

I think you’re on the right lines but there’s actually a single whitespace in the “empty” rows. You can catch it if you visually select/highlight it (how I found it). Try using ' ' instead.

6 Likes
3

Still does not work! it’s quite annoying that I can’t do this!

4

You’ll need to provide a little more information for anyone who’s willing to assist; it’s not clear what you’ve tried and what you expected to happen.

5

I have the same problem. and your suggestion did not work

6

Could you post your code please?

1 Like
7

here are my code and screenshot of the results, as you can see the null values are not filled!

SELECT * FROM parts
LIMIT 10;

ALTER TABLE parts
ALTER COLUMN code SET NOT NULL;
 
ALTER TABLE parts
ADD UNIQUE(code);

UPDATE parts
SET description = 'None Available' 
WHERE description = NULL;

SELECT * FROM parts;

image
image1364×1710 238 KB

8

Here with empty string:

UPDATE parts
SET description = 'None Available' 
WHERE description = '';

image
image1354×1404 117 KB

9

With one space as string:

SELECT * FROM parts
LIMIT 10;

ALTER TABLE parts
ALTER COLUMN code SET NOT NULL;
 
ALTER TABLE parts
ADD UNIQUE(code);

UPDATE parts
SET description = 'None Available' 
WHERE description = ' ';

SELECT * FROM parts;

In this case, those empty ones disappear

image
image1354×1620 239 KB

1 Like
11

Disappear or just move relative to where you’re looking? View more of the list or if it’s too long look just query rows that match your new description.

3 Likes
12

this actually worked for me, they just moved to the bottom of the list with updated descriptions :slight_smile:

1 Like
13

thanks for the tip, I was so confused why this wasn’t working with NULL… surely this is a bug in the exercise??

2 Likes
14

This solution only deletes the rows for some reason.

15

It’s mentioned in other comments but have you checked the entire query? The order of the updated rows is changed by the time you query. If rows have actually been removed then you’ll need to explain a bit more about what you did.

1 Like
16

Ah, yes I see them at the bottom. That makes sense now that i think about it. I think SQL adds to the end of the list by default when updating. For clarity’s sake I had basically done this:

Sorry about that!

1 Like
17

I saw that too using a ‘’ (two single quotes) and also tried ’ ’ to no effect I also tried to rename the space
UPDATE parts

SET description = NULL

WHERE description = ‘’;

UPDATE parts

SET description = NULL

WHERE description = ’ ';

UPDATE parts

SET description = ‘Need Description’

WHERE description IS NULL;

Nothing please help

18

Just tried running it and it seems fine. You’re not trying to insert NULLs though, double check the instructions. The issue is that the data contains a single space character so you must match against it (instead of NULL) and replace it with the phrase the instructions provide. The structure of the query given in the hint should put you on the right track.

1 Like
19

thanks for the help, I needed to let it sink in.

20

Now I understand, they all go to the bottom of the table… I was going crazy. :smiley: :smiley: a big thanks.

2 Likes
21

try adding OR:
UPDATE parts SET description = ‘None Available’

WHERE description IS NULL OR description = ’ ';

SELECT * FROM parts;