I'm trying to update multiple columns in a MS SQL statement using a sub-query. A search led me to something like:
UPDATE table1
SET col1 = a.col1, col2 = a.col2, col3 = a.col3 FROM
(SELECT col1, col2, col3 from table2 where <expression>) AS a
WHERE table1.col1 <expression>
http://geekswithblogs.net/phoenix/archive/2009/10/13/update-multiple-columns-on-sql-server.aspx
My problem is that in the inner WHERE
expression I need a reference to a specific field in table1:
UPDATE table1
SET col1 = a.col1, col2 = a.col2, col3 = a.col3 FROM
(SELECT col1, col2, col3 from table2 where table1.col0 = table2.col0) AS a
WHERE table1.col1 <expression>
When I run that query I get "The multi-part identifier "table1.col0" could not be bound. ". Apparently when using that syntax SQL cannot bind the current table1 record in the subquery. Right now I am repeating the subquery for each field and using the syntax:
UPDATE table1
SET col1 = (subquery), col2 = (subquery)...
But that executes the subquery (which is very expensive) once per column, which I would like to avoid.
Any ideas?
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.
The UPDATE statement in SQL is used to update the data of an existing table in database. 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,...
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.
update table1 set code1 = ( select table2. code1 from table2 where table2. table2_Id = 1234 );
in sql server, you can use a from
clause in an update query. Join the tables as you would in a select. The table you are updating must be included in the joins.
update table_1
set field_1 = table_2.value_1
from table_1
inner join table_2
on (table_1.id = table_2.id)
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