Shulou(Shulou.com)06/01 Report--

MariaDB 10.3 eliminates subquery updates that UPDATE does not support for the same table.

Let's look directly at the case.

CREATE TABLE T1 (C1 INT, c2 INT); INSERT INTO T1 VALUES (10 FROM 10), (20 FROM 20); UPDATE T1 SET c1=c1+1 WHERE c2 = (SELECT MAX (c2) FROM T1)

MySQL5.7 reports an error directly

MySQL 8.0 directly reported an error

MariaDB 10.3 updated successfully

At present, MySQL can only rewrite the SQL implementation, that is, the statement max allows it to generate a derivative table to pass.

UPDATE T1 a, (SELECT MAX (c2) as m_c2 FROM T1) as b SET a.c1=a.c1+1 WHERE a.c2=b.m_c2

-

Similarly, take a look at the DELETE delete operation.

DROP TABLE T1 create TABLE T1 (C1 INT, c2 INT); DELETE FROM T1 WHERE C1 IN (SELECT b.c1 FROM T1 b WHERE b.c2=0)

MySQL 8.0 directly reported an error

MariaDB 10.3 deleted successfully

Reference: https://jira.mariadb.org/browse/MDEV-12137

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.