I would like to update multiple columns in a table based on values from a second table using a Select
statement to obtain the values like this:
UPDATE tbl1 SET (col1, col2, col3) = (SELECT colA, colB, colC FROM tbl2 WHERE tbl2.id = 'someid') WHERE tbl1.id = 'differentid'
However, it doesn't seem as though it's possible to 'SET' more than one column name - are there alternatives rather than writing separate update statements for each column?
UPDATE tbl1 SET col1 = (SELECT colA FROM tbl2 WHERE tbl2.id = 'someid') WHERE tbl1.id = 'differentid' UPDATE tbl1 SET col2 = (SELECT colB FROM tbl2 WHERE tbl2.id = 'someid') WHERE tbl1.id = 'differentid' UPDATE tbl1 SET col3 = (SELECT colC FROM tbl2 WHERE tbl2.id = 'someid') WHERE tbl1.id = 'differentid'
We can update multiple columns by specifying multiple columns after the SET command in the UPDATE statement. The UPDATE statement is always followed by the SET command, it specifies the column where the update is required.
First, specify the table name that you want to change data in the UPDATE clause. Second, assign a new value for the column that you want to update. In case you want to update data in multiple columns, each column = value pair is separated by a comma (,). Third, specify which rows you want to update in the WHERE clause.
We can update single columns as well as multiple columns using UPDATE statement as per our requirement. UPDATE table_name SET column1 = value1, column2 = value2,... WHERE condition; table_name: name of the table column1: name of first , second, third column.... value1: new value for first, second, third column....
update tbl1 set col1 = a.col1, col2 = a.col2, col3 = a.col3 from tbl2 a where tbl1.Id = 'someid' and a.Id = 'differentid'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With