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: 1052Column 'col1' in field list is ambiguousid 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? 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.In this article, we are going to look at four scenarios for Oracle cross table update. Category_ID) where exists ( select * from Categories b where b. These columns uniquely identify a record in a table.Suppose we have two tables Categories and Categories_Test. The common column in the two tables is CATEGORY_ID. Please note that query below is used for illustration purpose because Category_ID alone is primary key.Cross table update (also known as correlated update, or multiple table update) in Oracle uses non-standard SQL syntax format (non ANSI standard) to update rows in another table. Update data in table A based on two or more common columns in table B.The differences in syntax are quite dramatic compared to other database systems like MS SQL Server or My SQL. Updates based on two or more common columns are normally used for tables where multiple columns work together as a primary key (known as composite primary key).
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. This is what I had in mind and though I learned from the theory.
I tried to update the tables in that view and I get the error. Seeing all the records in the view where unit 71054 I tried the update without the where cause all the records was that unitupdate tmp_mfg_unit_2set t_cmnf='OTH'and still the same error UNION ALL view 'tmp_mfg_unit_2' is not updatable because the definition contains a disallowed construct. I deleted the declare and added '71054' to parts of the the view works perfectly.
I tried to update the tables in that view and I get the error. All the records in the view was of unit 71054 I tried the update without the where clause.update tmp_mfg_unit_2set t_cmnf='OTH'and still the same error UNION ALL view 'tmp_mfg_unit_2' is not updatable because the definition contains a disallowed construct.
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 ......).