Let's say I have table table1 with columns id, value and table2 with columns table1_id, value.
How would I write Postgresql query to update table1.value (whole table, not just one row) with table2.value if they are matching on table1.id = table2.table1_id?
Thank you for answers in advance!
You use a from clause. In Postgres, this looks like:
update table1
set col1 = . . .
from table2
where table1.id = table2.table1_id
SET option does not accept alias. The aliases are accepted in the WHERE clause.Came here for this answer. Tried it on my installation of postgresql v 12.6.
I got it to work. Since these questions and responses are recent, I thought I would share what I learned.
svc_99 and cli_00_main have some columns with identical names. One column cli_actno_new was complete in both tables, cli_grpno_new was complete in cli_00_main, but was about 75% missing in svc_99. I wanted to populate svc_99.
This command worked:
pmaa_00_00_01=# UPDATE svc_99 s SET cli_grpno_new = c.cli_grpno_new FROM cli_00_main c WHERE c.cli_actno_new = s.cli_actno_new AND COALESCE(s.cli_grpno_new, '') = '';
UPDATE 8686
The failures taught me some things so I have included them:
pmaa_00_00_01=# UPDATE svc_99 SET svc_99.cli_grpno_new = cli_00_main.cli_grpno_new FROM cli_00_main WHERE svc_99.cli_actno_new = cli_00_main.cli_actno_new AND COALESCE(svc_99.cli_grpno_new, '') = '';
ERROR: column "svc_99" of relation "svc_99" does not exist
LINE 1: UPDATE svc_99 SET svc_99.cli_grpno_new = cli_00_main.cli_grp...
^
pmaa_00_00_01=# UPDATE svc_99 SET cli_grpno_new = cli_00_main.cli_grpno_new FROM cli_00_main WHERE cli_actno_new = cli_00_main.cli_actno_new AND COALESCE(cli_grpno_new, '') = '';
ERROR: column reference "cli_actno_new" is ambiguous
LINE 1: ... cli_00_main.cli_grpno_new FROM cli_00_main WHERE cli_actno_...
^
pmaa_00_00_01=# UPDATE svc_99 s SET cli_grpno_new = cli_grpno_new FROM cli_00_main c WHERE c.cli_actno_new = s.cli_actno_new AND COALESCE(cli_grpno_new, '') = '';
ERROR: column reference "cli_grpno_new" is ambiguous
LINE 1: ...RE c.cli_actno_new = s.cli_actno_new AND COALESCE(cli_grpno_...
^
pmaa_00_00_01=# UPDATE svc_99 s SET cli_grpno_new = cli_grpno_new FROM cli_00_main c WHERE c.cli_actno_new = s.cli_actno_new AND COALESCE(s.cli_grpno_new, '') = '';
ERROR: column reference "cli_grpno_new" is ambiguous
LINE 1: UPDATE svc_99 s SET cli_grpno_new = cli_grpno_new FROM cli_0...
^
pmaa_00_00_01=# UPDATE svc_99 s SET svc_99.cli_grpno_new = cli_00_main.cli_grpno_new FROM cli_00_main c WHERE c.cli_actno_new = s.cli_actno_new AND COALESCE(s.cli_grpno_new, '') = '';
ERROR: invalid reference to FROM-clause entry for table "cli_00_main"
LINE 1: UPDATE svc_99 s SET svc_99.cli_grpno_new = cli_00_main.cli_g...
^
HINT: Perhaps you meant to reference the table alias "c".
pmaa_00_00_01=# UPDATE svc_99 s SET svc_99.cli_grpno_new = c.cli_grpno_new FROM cli_00_main c WHERE c.cli_actno_new = s.cli_actno_new AND COALESCE(s.cli_grpno_new, '') = '';
ERROR: column "svc_99" of relation "svc_99" does not exist
LINE 1: UPDATE svc_99 s SET svc_99.cli_grpno_new = c.cli_grpno_new F...
^
From the above I learned that the target columns should not be qualified, but if column names are ambigious, the source should be qualified and if this is the case, an alias may(must?) be used if present. I hope I got that right.
