Update multiple columns of table based on another table sql-CodePudding

I have two tables:

Table1:

column1|column2|column3|column4|column5
---------------------------------------
'test1'| null  |   12  |'old1' | 'gr1'
'test1'| 123   |  null |'old2' | 'gr2'
'test2'| 488   |  null |'old3' | 'gr3'
'test3'| 488   |  null |'old4' | 'gr4'

Table2: (it's a SELECT query)

column1|column2|column4|column5
-------------------------------
'test1'| 999   | 'new1'| 'gr2'
'test3'| 1355  | 'new4'| 'gr4'

I have created the second table as a query in order to update the values of the first, where the key of the first table is (column1, column5). So the table I am after is:

Table1:

column1|column2|column3|column4|column5
---------------------------------------
'test1'| null  |   12  |'old1' | 'gr1'
'test1'| 999   |  null |'new1' | 'gr2'
'test2'| 488   |  null |'old3' | 'gr3'
'test3'| 1355  |  null |'new4' | 'gr4'

How is this done?

CodePudding user response：

Can you try

UPDATE table1 t1 set (column2,column4) = (
    SELECT column2,column4
    FROM table2 t2
    WHERE t1.column1 = t2.column1 and t1.column5 = t2.column5
)

If you have table2 as a query, you can use WITH

WITH table2 (column1,column2,column4,column5) AS (
  <your select query>
),
UPDATE table1 t1 set (column2,column4) = (
    SELECT column2,column4
    FROM table2 t2
    WHERE t1.column1 = t2.column1 and t1.column5 = t2.column5
)

CodePudding user response：

The exakt SQL command will of course depend on the query which produces your second table. Assuming, this query would just be "SELECT * FROM yourtable2", you can do following update command to achieve your goal:

UPDATE yourtable 
SET 
column2 = x.column2, 
column4 = x.column4
FROM (SELECT * FROM yourtable2) x
WHERE yourtable.column1 = x.column1
AND yourtable.column5 = x.column5

Here you see this is working (in case the table "yourtable2" provides the correct data): db<>fiddle So, you can replace the "SELECT FROM yourtable2" by your query and it will work.