【数据库差异研究】update与delete使用表别名的研究
目录
♋1.1 测试用例UPDATE users as a SET a.age = 111 WHERE a.name = 'Alice';
♏1.2 测试用例UPDATE users as a SET a.age = 111 WHERE name = 'Alice';
♐1.3 测试用例UPDATE users as a SET age = 111 WHERE a.name = 'Alice';
♑1.4 测试用例UPDATE users as a SET age = 111 WHERE name = 'Alice';
♉2.1 测试用例delete users as a from a WHERE a.name = 'Alice';
♈2.2 测试用例delete users as a from a WHERE name = 'Alice';
⚛️总结
Update
| SQL语句示例 | Oracle | SQLite | PostgreSQL | MYSQL & mariadb |
|---|---|---|---|---|
UPDATE users as a SET a.age = 111 WHERE a.name = 'Alice'; | 报错 | 报错 | 报错 | 正常执行 |
UPDATE users as a SET a.age = 111 WHERE name = 'Alice'; | 报错 | 报错 | 报错 | 正常执行 |
UPDATE users as a SET age = 111 WHERE a.name = 'Alice'; | 报错 | 正常执行 | 正常执行 | 正常执行 |
UPDATE users as a SET age = 111 WHERE name = 'Alice'; | 报错 | 正常执行 | 正常执行 | 正常执行 |
Delete
| SQL语句示例 | Oracle | SQLite | PostgreSQL | MYSQL & mariadb |
|---|---|---|---|---|
delete from users as a WHERE a.name = 'Alice'; | 报错 | 报错 | 报错 | 报错 |
delete from users as a WHERE name = 'Alice'; | 报错 | 报错 | 报错 | 报错 |
☪️1 Update
♋1.1 测试用例UPDATE users as a SET a.age = 111 WHERE a.name = 'Alice';
UPDATE users as a SET a.age = 111 WHERE a.name = 'Alice';
CREATE TABLE users (
name VARCHAR(255) NOT NULL,
age INT
);
INSERT INTO users (name, age) VALUES ('Alice', 25);
INSERT INTO users (name, age) VALUES ('Bob', 30);
INSERT INTO users (name, age) VALUES ('Charlie', 35);
INSERT INTO users (name, age) VALUES ('Alice', 40);
SELECT * FROM users;
UPDATE users as a SET a.age = 111 WHERE a.name = 'Alice';
SELECT * FROM users;修改前表内容如下：
| name | age |
|---|---|
| Alice | 25 |
| Bob | 30 |
| Charlie | 35 |
| Alice | 40 |
1 ORACLE
执行报错
ORA-00971: missing SET keyword之后查询表内容未发生变化，如下：
name age Alice 25 Bob 30 Charlie 35 Alice 40
2 sqlite
执行报错
Error: near line 12: near ".": syntax error之后查询表内容未发生变化，如下：
name age Alice 25 Bob 30 Charlie 35 Alice 40
3 PG
执行报错
psql:commands.sql:12: ERROR: column "a" of relation "users" does not exist LINE 1: UPDATE users as a SET a.age = 111 WHERE a.name = 'Alice';之后查询表内容未发生变化，如下：
name age Alice 25 Bob 30 Charlie 35 Alice 40
4 MYSQL & mariadb
执行正常
后表内容已经更新，如下：
name age Alice 111 Bob 30 Charlie 35 Alice 111
♏1.2 测试用例UPDATE users as a SET a.age = 111 WHERE name = 'Alice';
UPDATE users as a SET a.age = 111 WHERE name = 'Alice';
CREATE TABLE users (
name VARCHAR(255) NOT NULL,
age INT
);
INSERT INTO users (name, age) VALUES ('Alice', 25);
INSERT INTO users (name, age) VALUES ('Bob', 30);
INSERT INTO users (name, age) VALUES ('Charlie', 35);
INSERT INTO users (name, age) VALUES ('Alice', 40);
SELECT * FROM users;
UPDATE users as a SET a.age = 111 WHERE name = 'Alice';
SELECT * FROM users;修改前表内容如下：
| name | age |
|---|---|
| Alice | 25 |
| Bob | 30 |
| Charlie | 35 |
| Alice | 40 |
1 ORACLE
执行报错
ORA-00971: missing SET keyword之后查询表内容未发生变化，如下：
name age Alice 25 Bob 30 Charlie 35 Alice 40
2 sqlite
执行报错
Error: near line 12: near ".": syntax error之后查询表内容未发生变化，如下：
name age Alice 25 Bob 30 Charlie 35 Alice 40
3 PG
执行报错
psql:commands.sql:12: ERROR: column "a" of relation "users" does not exist LINE 1: UPDATE users as a SET a.age = 111 WHERE name = 'Alice';之后查询表内容未发生变化，如下：
name age Alice 25 Bob 30 Charlie 35 Alice 40
4 MYSQL & mariadb
执行正常
后表内容已经更新，如下：
name age Alice 111 Bob 30 Charlie 35 Alice 111
♐1.3 测试用例UPDATE users as a SET age = 111 WHERE a.name = 'Alice';
UPDATE users as a SET age = 111 WHERE a.name = 'Alice';
CREATE TABLE users (
name VARCHAR(255) NOT NULL,
age INT
);
INSERT INTO users (name, age) VALUES ('Alice', 25);
INSERT INTO users (name, age) VALUES ('Bob', 30);
INSERT INTO users (name, age) VALUES ('Charlie', 35);
INSERT INTO users (name, age) VALUES ('Alice', 40);
SELECT * FROM users;
UPDATE users as a SET age = 111 WHERE a.name = 'Alice';
SELECT * FROM users;修改前表内容如下：
| name | age |
|---|---|
| Alice | 25 |
| Bob | 30 |
| Charlie | 35 |
| Alice | 40 |
1 ORACLE
执行报错
ORA-00971: missing SET keyword之后查询表内容未发生变化，如下：
name age Alice 25 Bob 30 Charlie 35 Alice 40
2 sqlite
执行正常
后表内容已经更新，如下：
name age Alice 111 Bob 30 Charlie 35 Alice 111
3 PG
执行正常
后表内容已经更新，如下：
name age Bob 30 Charlie 35 Alice 111 Alice 111
4 MYSQL & mariadb
执行正常
后表内容已经更新，如下：
name age Alice 111 Bob 30 Charlie 35 Alice 111
♑1.4 测试用例UPDATE users as a SET age = 111 WHERE name = 'Alice';
UPDATE users as a SET age = 111 WHERE name = 'Alice';
CREATE TABLE users (
name VARCHAR(255) NOT NULL,
age INT
);
INSERT INTO users (name, age) VALUES ('Alice', 25);
INSERT INTO users (name, age) VALUES ('Bob', 30);
INSERT INTO users (name, age) VALUES ('Charlie', 35);
INSERT INTO users (name, age) VALUES ('Alice', 40);
SELECT * FROM users;
UPDATE users as a SET age = 111 WHERE name = 'Alice';
SELECT * FROM users;修改前表内容如下：
| name | age |
|---|---|
| Alice | 25 |
| Bob | 30 |
| Charlie | 35 |
| Alice | 40 |
1 ORACLE
执行报错
ORA-00971: missing SET keyword之后查询表内容未发生变化，如下：
name age Alice 25 Bob 30 Charlie 35 Alice 40
2 sqlite
执行正常
后表内容已经更新，如下：
name age Alice 111 Bob 30 Charlie 35 Alice 111
3 PG
执行正常
后表内容已经更新，如下：
name age Bob 30 Charlie 35 Alice 111 Alice 111
4 MYSQL & mariadb
执行正常
后表内容已经更新，如下：
name age Alice 111 Bob 30 Charlie 35 Alice 111
☪️2 delete
♉2.1 测试用例delete users as a from a WHERE a.name = 'Alice';
delete users as a from a WHERE a.name = 'Alice';
CREATE TABLE users (
name VARCHAR(255) NOT NULL,
age INT
);
INSERT INTO users (name, age) VALUES ('Alice', 25);
INSERT INTO users (name, age) VALUES ('Bob', 30);
INSERT INTO users (name, age) VALUES ('Charlie', 35);
INSERT INTO users (name, age) VALUES ('Alice', 40);
SELECT * FROM users;
delete users as a from a WHERE a.name = 'Alice';
SELECT * FROM users;修改前表内容如下：
| name | age |
|---|---|
| Alice | 25 |
| Bob | 30 |
| Charlie | 35 |
| Alice | 40 |
1 ORACLE
执行报错
ORA-03048: SQL reserved word 'AS' is not syntactically valid following 'delete users '之后查询表内容未发生变化，如下：
name age Alice 25 Bob 30 Charlie 35 Alice 40
2 sqlite
执行报错
Error: near line 12: near "users": syntax error之后查询表内容未发生变化，如下：
name age Alice 25 Bob 30 Charlie 35 Alice 40
3 PG
执行报错
psql:commands.sql:12: ERROR: syntax error at or near "users" LINE 1: delete users as a from a WHERE a.name = 'Alice';之后查询表内容未发生变化，如下：
name age Alice 25 Bob 30 Charlie 35 Alice 40
4 MYSQL & mariadb
执行报错
ERROR 1064 (42000) at line 12: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as a from a WHERE a.name = 'Alice'' at line 1之后查询表内容未发生变化，如下：
name age Alice 25 Bob 30 Charlie 35 Alice 40
♈2.2 测试用例delete users as a from a WHERE name = 'Alice';
delete users as a from a WHERE name = 'Alice';
CREATE TABLE users (
name VARCHAR(255) NOT NULL,
age INT
);
INSERT INTO users (name, age) VALUES ('Alice', 25);
INSERT INTO users (name, age) VALUES ('Bob', 30);
INSERT INTO users (name, age) VALUES ('Charlie', 35);
INSERT INTO users (name, age) VALUES ('Alice', 40);
SELECT * FROM users;
delete users as a from a WHERE name = 'Alice';
SELECT * FROM users;修改前表内容如下：
| name | age |
|---|---|
| Alice | 25 |
| Bob | 30 |
| Charlie | 35 |
| Alice | 40 |
1 ORACLE
执行报错
ORA-03048: SQL reserved word 'AS' is not syntactically valid following 'delete users '之后查询表内容未发生变化，如下：
name age Alice 25 Bob 30 Charlie 35 Alice 40
2 sqlite
执行报错
Error: near line 12: near "users": syntax error之后查询表内容未发生变化，如下：
name age Alice 25 Bob 30 Charlie 35 Alice 40
3 PG
执行报错
psql:commands.sql:12: ERROR: syntax error at or near "users" LINE 1: delete users as a from a WHERE name = 'Alice';之后查询表内容未发生变化，如下：
name age Alice 25 Bob 30 Charlie 35 Alice 40
4 MYSQL & mariadb
执行报错
ERROR 1064 (42000) at line 12: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as a from a WHERE name = 'Alice'' at line 1之后查询表内容未发生变化，如下：
name age Alice 25 Bob 30 Charlie 35 Alice 40
原文地址：https://blog.csdn.net/m0_57532432/article/details/142689851
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！