Multi-Table Association update for Oracle and SqlServer

An Update statement cannot Update multiple tables unless the trigger is used for implicit Update. In table Update operations, you need to reference the table to be updated in expressions in many cases.

An Update statement cannot Update multiple tables unless the trigger is used for implicit Update. In table Update operations, you need to reference the table to be updated in expressions in many cases.

An Update statement cannot Update multiple tables unless the trigger is used to implicitly Update the table, in many cases, data outside the table to be updated must be referenced in the expression.

I. ms SQL Server multi-Table Association update

SQL server provides the update from clause to connect the table to be updated with other data sources. Although only one table can be updated, by connecting the table to be updated with other data sources, you can reference data other than the table to be updated in the update expression.

General format:

Update a set field 1 = B Table field expression, Field 2 = B Table field expression from B WHERE logical expression

For example:

UPDATE dbo. Table2

SET dbo. Table2.ColB = dbo. Table2.ColB + dbo. Table1.ColB

FROM dbo. Table2

Inner join dbo. Table1

ON (dbo. Table2.ColA = dbo. Table1.ColA );

The actual update operation is performed on the table to be updated, rather than on the new result set formed by the from clause.

Ii. Oracle multi-Table Association update

Oracle does not have the update from syntax. There are two implementation methods:

1. subquery:

Update

SET field 1 = (select field expression from B WHERE ...),

Field 2 = (select field expression from B WHERE ...)

WHERE logical expression

UPDATE multiple fields:

Statement 1:

UPDATE table_1

SET col_x1 = (SELECT B. col_y1 FROM table_2 B WHERE B. col_n = a. col_m ),

Col_x2 = (SELECT B. col_y2 FROM table_2 B WHERE B. col_n = a. col_m)

Where exists (SELECT 1 FROM table_2 B WHERE B. col_n = a. col_m)

Or

UPDATE table_1

SET col_x1 = (SELECT B. col_y1 FROM table_2 B WHERE B. col_n = a. col_m ),

Col_x2 = (SELECT B. col_y2 FROM table_2 B WHERE B. col_n = a. col_m)

WHERE a. col_m = (SELECT B. col_n FROM table_2 B WHERE B. col_n = a. col_m)

Statement 2:

UPDATE table_1

SET (col_x1, col_x2) = (SELECT B. col_y1, B. col_y2 FROM table_2 B WHERE B. col_n = a. col_m)

Where exists (SELECT 1 FROM table_2 B WHERE B. col_n = a. col_m );

Or

UPDATE table_1

SET (col_x1, col_x2) = (SELECT B. col_y1, B. col_y2 FROM table_2 B WHERE B. col_n =. col_m) WHERE. col_m = (SELECT B. col_n FROM table_2 B WHERE B. col_n =. col_m );

Note:

1. The subquery value can only be a unique value, not a multi-value.

2. In most cases, the where EXISTS clause at the end is important; otherwise, an error is returned. The where EXISTS clause can be replaced by another method, as shown above. The last clause limits the updated records of table a. If this clause is absent, for a record in Table a, if the corresponding record cannot be associated in Table B, the updated field of the record will be updated to null. The where EXISTS clause is used to exclude updating the records in this condition in table.

2. View:

UPDATE (select a. name aname, B. name bname from a, B where a. ID = B. ID)

Set aname = BNAME;

Restrictions on view update:

If a view is connected to multiple tables, the user's ability to update view records is limited. The base table of the view cannot be updated unless update only involves one table and the View column contains the entire primary key of the updated table.

Iii. Oracle multi-Table Association Deletion

The from clause of Delete in Oracle does not support multi-table join. It can only be performed through subqueries:

Delete from Table a where exists (SELECT 1 FROM Table B where Table A. empid = Table B. empid)

Or

Delete from Table a where Table A. empid in (SELECT empid WHERE Table B)