Update the results of a SELECT statement?

I haven't seen a formal name for this. The Oracle SQL Reference just refers to updating a subquery. I tend to think of it as a form of "view updating", with the subquery being in in-line view.

I haven't seen a formal name for this. The Oracle SQL Reference just refers to updating a subquery. I tend to think of it as a form of "view updating", with the subquery being in in-line view.

Yes, it works when a number of tables are joined, but subject to the rules of view updating. This means that only one of the view's base tables can be updated, and this table must be "key-preserved" in the view: i.e. Its rows should only be able to appear once in the view.

This requires that any other tables in the view (subquery) are referenced via foreign key constraints on the table to be updated. Some examples may help. Using the standard Oracle EMP and DEPT tables, with EMP.

EMPNO being defined as the primary key of EMP, and EMP. DEPTNO being defined as a foreign key to DEPT. DEPTNO, then this update is allowed: update (select emp.

Empno, emp. Ename, emp. Sal, dept.

Dname from emp join dept on dept. Deptno = emp. Deptno ) set sal = sal+100; But this is not: -- DEPT is not "key-preserved" - same DEPT row may appear -- several times in view update (select emp.

Ename, emp. Sal, dept. Deptno, dept.

Dname from emp join dept on dept. Deptno = emp. Deptno ) set dname = upper(dname); As for performance: the optimiser will (must) identify the base table to be updated during parsing, and joins to other table will be ignored since they do not have any bearing on the update to be performed - as this AUTOTRACE output shows: SQL> update (select emp.

Ename, emp. Sal, dept. Dname 2 from emp join dept on dept.

Deptno = emp. Deptno 3 ) 4 set sal = sal-1; 33 rows updated. Execution Plan ---------------------------------------------------------- Plan hash value: 1507993178 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | UPDATE STATEMENT | | 33 | 495 | 3 (0)| 00:00:01 | | 1 | UPDATE | EMP | | | | | | 2 | NESTED LOOPS | | 33 | 495 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| EMP | 33 | 396 | 3 (0)| 00:00:01 | |* 4 | INDEX UNIQUE SCAN| SYS_C0010666 | 1 | 3 | 0 (0)| 00:00:01 | ------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("EMP"."DEPTNO"="DEPT".

"DEPTNO") (Note that table DEPT is never accessed even though DEPT. DNAME appears in the subquery).

The form you mention has no specific name AFAIK. Just updating the result of a select statement. There is another form called Correlated update (with single or multicolumn update) UPDATE TABLE() SET = ( SELECT FROM WHERE ); The multicolumn form ... SET () = ( SELECT ... There is also a from which also returning of values called Update with returning clause And some specifics for updates with nested tables.

Best is to check at least this two pages Oracle® Database SQL Language Reference SELECT Oracle® Database SQL Language Reference UPDATE.

Thanks for comments, I thought this was standard Sql... :( For Oracle you can write an update on a table where you retrieve information with a join like: UPDATE ( SELECT * FROM table1 t1 LEFT JOIN table2 t2 ON t2. T1id = t1. ID ) SET t1.

Col1 = t2. Col2 For Sql Server, it's: UPDATE t1 SET col1 = t2. Col2 FROM table1 t1 LEFT JOIN table2 t2 on t2.

T1id = t1. Id If anyone knows a way to do this that works on Oracle, Sql Server and MySql I'd be interested.

– Thilo Jun 16 '09 at 6:56 Using dpriver.Com/pp/sqlformat. Htm it seems to work for MSSQL, but gives syntax errors for Oracle, MySQL or DB2 – Thilo Jun 16 '09 at 6:58 -1 No it does not work in Oracle. I'm not sure it is standard SQL either.

– Tony Andrews Jun 16 '09 at 8:44.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.