UPDATE TABLE where column IS NULL isn't working

Get Help SQL
,
#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?

#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.

2 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?

#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

#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.

1 Like
#12

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

#13

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