PL/SQL parser - Phase 2 (MDEV-10764)

[MDEV-10573] Oracle style multi-table UPDATE syntax Created: 2016-08-17  Updated: 2019-02-14

Status: Open
Project: MariaDB Server
Component/s: OTHER
Affects Version/s: None
Fix Version/s: None

Type: Technical task Priority: Major
Reporter: Alexander Barkov Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: Compatibility


 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



 Comments   
Comment by Michael Widenius [ 2016-08-18 ]

This looks like something that can be done only at the parser level where we convert Oracle syntax to MariaDB multi-table update syntax.

Generated at Thu Feb 08 07:43:15 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.