CODEWITHSUNDEEP
sqlpostgresql
Richard
Richard

Reputation: 8945

SQL Update error trying to update null column

I am using Postgres, and am trying to update a table for each row where a column is null.

UPDATE public.contact co
SET co.employee_id = (select e.employee_id from public.employee e where e.member_id = co.member_id)
WHERE co.employee_id = null

However, I get the following error:

ERROR:  column "co" of relation "contact" does not exist
LINE 2: SET co.employee_id = (select e.employee_id from public.emplo...
            ^
SQL state: 42703
Character: 30

Question

How do I construct some sql to update the null employee_id's?

Tables

**Contact**
contact_id
member_id
employee_id

**Member**
member_id

**Employee**
employee_id
member_id

Upvotes: 0

Views: 162

Answers (2)

user330315
user330315

Reputation:

Remove the prefix in the SET statement:

UPDATE public.contact co
   SET employee_id = ...
WHERE co.employee_id IS null

As UPDATE can only have one target table, and you can only change columns within that table, the prefix isn't needed nor allowed.

Upvotes: 2

Belayer
Belayer

Reputation: 14886

There are no updates because the condition "WHERE co.employee_id = null" never returns true.SQL boolean are 3-state value 'True, False, and Null' Further the test "Null=Null" does not return true; it returns Null. Change is to:

WHERE co.employee_id is null

Upvotes: 2

Related Questions