Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Update multiple columns in SQL with bound multi-part identifier

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?

like image 510
Santiago Palladino Avatar asked Jan 17 '10 21:01

Santiago Palladino


People also ask

How do you update multiple values in multiple columns in SQL?

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.

Can we update multiple columns in a single update statement in SQL?

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,...

How do you update a column with multiple values?

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.

How update table1 with table2 in SQL?

update table1 set code1 = ( select table2. code1 from table2 where table2. table2_Id = 1234 );


1 Answers

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)
like image 55
Ray Avatar answered Oct 19 '22 23:10

Ray