Details
-
New Feature
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
Description
The current MariaDB (as of version 12.0) has some serious limitations to how views and derived tables can be used in UPDATE/DELETE statements.
Let's consider two tables t1, t2 and two views v1,
v2 :
create table t1 (a int); |
create table t2 (b int); |
insert into t1 values (3), (7), (1), (8), (2); |
insert into t2 values (4), (7), (1), (5), (2); |
create view v1 as select a from t1; |
create view v2 as select * from t1,t2 where t1.a=t2.b; |
The following limitations for UPDATE statements can be observed:
1u. The derived table with the same specification as of an updatable view cannot be updated
MariaDB [test]> update v1 set a=10;
|
Query OK, 5 rows affected (0.027 sec)
|
Rows matched: 5 Changed: 5 Warnings: 0
|
|
|
MariaDB [test]> select * from t1;
|
+------+
|
| a |
|
+------+
|
| 10 |
|
| 10 |
|
| 10 |
|
| 10 |
|
| 10 |
|
+------+
|
5 rows in set (0.001 sec)
|
|
|
MariaDB [test]> update (select a from t1) dt set dt.a=20;
|
ERROR 1288 (HY000): The target table dt of the UPDATE is not updatable
|
|
2u. Only one underlying table of a view specified as a join of some tables can be updated
MariaDB [test]> update t1,t2 set t1.a=10, t2.b=20 where t1.a=t2.b;
|
Query OK, 6 rows affected (0.019 sec)
|
Rows matched: 6 Changed: 6 Warnings: 0
|
|
|
MariaDB [test]> select * from t1;
|
+------+
|
| a |
|
+------+
|
| 3 |
|
| 10 |
|
| 10 |
|
| 8 |
|
| 10 |
|
+------+
|
5 rows in set (0.002 sec)
|
|
|
MariaDB [test]> select * from t2;
|
+------+
|
| b |
|
+------+
|
| 4 |
|
| 20 |
|
| 20 |
|
| 5 |
|
| 20 |
|
+------+
|
5 rows in set (0.002 sec)
|
|
|
MariaDB [test]> update v2 set v2.a=30, v2.b=40;
|
ERROR 1393 (HY000): Can not modify more than one base table through a join view 'test.v2'
|
Similar limitations can be observed for DELETE statements:
1d. Delete from the derived table with the same specification as of a updatable view is not allowed:
ariaDB [test]> delete from v1 where a < 5;
|
Query OK, 3 rows affected (0.017 sec)
|
|
|
MariaDB [test]> select * from t1;
|
+------+
|
| a |
|
+------+
|
| 7 |
|
| 8 |
|
+------+
|
2 rows in set (0.001 sec)
|
|
|
MariaDB [test]> delete from (select * from t1) dt where dt.a > 7;
|
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(select * from t1) dt where dt.a > 7' at line 1
|
2d. Rows of one underlying table of a view specified as a join of some tables can be deleted
MariaDB [test]> delete from t1,t2 using t1,t2 where t1.a=t2.b;
|
Query OK, 6 rows affected (0.027 sec)
|
|
|
MariaDB [test]> select * from t1;
|
+------+
|
| a |
|
+------+
|
| 3 |
|
| 8 |
|
+------+
|
2 rows in set (0.001 sec)
|
|
|
MariaDB [test]> select * from t2;
|
+------+
|
| b |
|
+------+
|
| 4 |
|
| 5 |
|
+------+
|
2 rows in set (0.001 sec)
|
|
|
MariaDB [test]> delete from v2 where t1.a=t2.b;
|
ERROR 1395 (HY000): Can not delete from join view 'test.v2'
|
Attachments
Issue Links
- relates to
-
-
- Stalled
-
-
MDEV-23552 Merge mergeable derived tables used at the top level of UPDATE/DELETE statements
-
- Stalled
-