Single Query to Update same column name in different tables with where condition

Question

Single Query to Update same column name in different tables with where condition

j_kathiresan

SSC Veteran

Points: 243
More actions
September 25, 2014 at 1:33 am

#311881

Hi,
I have two tables table1 and table2 and having a common column "col1"
When i ran the following query
UPDATE table1, table2 SET col1=FALSE WHERE id = 1;
getting the following error
Error Code: 1052
Column 'col1' in field list is ambiguous
id column exist in both the tables and need to update both the tables to false where the id is equivalent to 1.
could you please help me on how to achieve this in single query?
Thanks.
September 25, 2014 at 3:52 am

This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

HanShi SSC-Dedicated Points: 33507 More actions · Answer 1

with a UPDATE statement you can only update one single table at the time. You could filter the rows from that table using multiple tables with a JOIN statement in the FROM part.

UPDATE single_table

SET single_table.column_1 = new_values

, single_table.column_2 = new_value

FROM single_table

INNER JOIN other_table

ON single_table.column = other_table.column

The only way to update multiple tables in a single statement is when you create a view over these tables and use the option WITH SCHEMABINDING. Then you are able to update the columns by updating the view (UPDATE view SET ......).

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

HanShi (9/25/2014)
The only way to update multiple tables in a single statement is when you create a view over these tables and use the option WITH SCHEMABINDING. Then you are able to update the columns by updating the view (UPDATE view SET ......).

From Books Online (http://msdn.microsoft.com/en-us/library/ms187956.aspx):

Updatable Views
You can modify the data of an underlying base table through a view, as long as the following conditions are true:
Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.

Schemabinding is not required to make a view updatable.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

HanShi SSC-Dedicated Points: 33507 More actions · Answer 3

GilaMonster (9/25/2014)
HanShi (9/25/2014)
The only way to update multiple tables in a single statement is when you create a view over these tables and use the option WITH SCHEMABINDING. Then you are able to update the columns by updating the view (UPDATE view SET ......).
From Books Online (http://msdn.microsoft.com/en-us/library/ms187956.aspx):
Updatable Views
You can modify the data of an underlying base table through a view, as long as the following conditions are true:
Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.
Schemabinding is not required to make a view updatable.

I stand corrected, thanks Gail.

This is what I had in mind and though I learned from the theory. I never used it, so I don't have practical experience. Now I know I had it wrong in my head all the time :blush: :crying:.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **