XMLWordPrintable

    Details

    • Type: Technical task
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: OTHER
    • Labels:

      Description

      Implement Oracle-style multi-table UPDATE syntax:

      UPDATE (SELECT ... FROM t1,t2 WHERE ... ) SET ...;
      

      Example:

      DROP TABLE t1;
      DROP TABLE t2;
      CREATE TABLE t1 (id INT PRIMARY KEY, p1 INT);
      INSERT INTO t1 VALUES (1, 100);
      INSERT INTO t1 VALUES (2, 100);
      CREATE TABLE t2 (id INT, p2 INT);
      INSERT INTO t2 VALUES (1, 10);
      INSERT INTO t2 VALUES (1, 20);
      UPDATE (SELECT p1,p2 FROM t1,t2 WHERE t1.id=t2.id AND p1=100) SET p2=p2+1;
      SELECT * FROM t2;
      

      	ID	   P2
      ---------- ----------
      	 1	   11
      	 1	   21
      

      Note, Oracle has some limitations. If I rewrite the above UPDATE statement as:

      UPDATE (SELECT p1,p2 FROM t1,t2 WHERE t1.id=t2.id AND p1=100) SET p1=p1+1;
      

      it returns an error:

      UPDATE (SELECT p1,p2 FROM t1,t2 WHERE t1.id=t2.id AND p1=100) SET p1=p1+1
                                                                        *
      ERROR at line 1:
      ORA-01779: cannot modify a column which maps to a non key-preserved table
      

        Attachments

          Activity

            People

            Assignee:
            Unassigned
            Reporter:
            bar Alexander Barkov
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Dates

              Created:
              Updated: