当前位置： 代码网 > it编程>编程语言>Javascript > 数据库中update与delete使用表别名的深入研究

数据库中update与delete使用表别名的深入研究

2024年10月23日 • Javascript •我要评论
总结updatesql语句示例oraclesqlitepostgresqlmysql & mariadbupdate users as a set a.age = 111 where a.na

总结

update

sql语句示例oraclesqlitepostgresqlmysql & 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语句示例oraclesqlitepostgresqlmysql & 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;

修改前表内容如下：

nameage
alice25
bob30
charlie35
alice40

1 oracle

执行报错

ora-00971: missing set keyword

之后查询表内容未发生变化，如下：

nameage
alice25
bob30
charlie35
alice40

2 sqlite

执行报错

error: near line 12: near ".": syntax error

之后查询表内容未发生变化，如下：

nameage
alice25
bob30
charlie35
alice40

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';

之后查询表内容未发生变化，如下：

nameage
alice25
bob30
charlie35
alice40

4 mysql & mariadb

执行正常

后表内容已经更新，如下：

nameage
alice111
bob30
charlie35
alice111

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;

修改前表内容如下：

nameage
alice25
bob30
charlie35
alice40

1 oracle

执行报错

ora-00971: missing set keyword

之后查询表内容未发生变化，如下：

nameage
alice25
bob30
charlie35
alice40

2 sqlite

 执行报错

error: near line 12: near ".": syntax error

之后查询表内容未发生变化，如下：

nameage
alice25
bob30
charlie35
alice40

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';

之后查询表内容未发生变化，如下：

nameage
alice25
bob30
charlie35
alice40

4 mysql & mariadb

执行正常

后表内容已经更新，如下：

nameage
alice111
bob30
charlie35
alice111

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;

修改前表内容如下：

nameage
alice25
bob30
charlie35
alice40

1 oracle

 执行报错

ora-00971: missing set keyword

之后查询表内容未发生变化，如下：

nameage
alice25
bob30
charlie35
alice40

2 sqlite

执行正常

后表内容已经更新，如下：

nameage
alice111
bob30
charlie35
alice111

3 pg

执行正常

后表内容已经更新，如下：

nameage
bob30
charlie35
alice111
alice111

4 mysql & mariadb

执行正常

后表内容已经更新，如下：

nameage
alice111
bob30
charlie35
alice111

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;

修改前表内容如下：

nameage
alice25
bob30
charlie35
alice40

1 oracle

 执行报错

ora-00971: missing set keyword

之后查询表内容未发生变化，如下：

nameage
alice25
bob30
charlie35
alice40

2 sqlite

执行正常

后表内容已经更新，如下：

nameage
alice111
bob30
charlie35
alice111

3 pg

执行正常

后表内容已经更新，如下：

nameage
bob30
charlie35
alice111
alice111

4 mysql & mariadb

执行正常

后表内容已经更新，如下：

nameage
alice111
bob30
charlie35
alice111

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;

修改前表内容如下：

nameage
alice25
bob30
charlie35
alice40

1 oracle

 执行报错

ora-03048: sql reserved word 'as' is not syntactically valid following 'delete
users '

之后查询表内容未发生变化，如下：

nameage
alice25
bob30
charlie35
alice40

2 sqlite

 执行报错

error: near line 12: near "users": syntax error

之后查询表内容未发生变化，如下：

nameage
alice25
bob30
charlie35
alice40

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';

 之后查询表内容未发生变化，如下：

nameage
alice25
bob30
charlie35
alice40

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

 之后查询表内容未发生变化，如下：

nameage
alice25
bob30
charlie35
alice40

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;

修改前表内容如下：

nameage
alice25
bob30
charlie35
alice40

1 oracle

 执行报错

ora-03048: sql reserved word 'as' is not syntactically valid following 'delete
users '

之后查询表内容未发生变化，如下：

nameage
alice25
bob30
charlie35
alice40

2 sqlite

 执行报错

error: near line 12: near "users": syntax error

之后查询表内容未发生变化，如下：

nameage
alice25
bob30
charlie35
alice40

3 pg

 执行报错

psql:commands.sql:12: error:  syntax error at or near "users"
line 1: delete users as a from a where name = 'alice';

 之后查询表内容未发生变化，如下：

nameage
alice25
bob30
charlie35
alice40

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

 之后查询表内容未发生变化，如下：

nameage
alice25
bob30
charlie35
alice40

总结 

到此这篇关于数据库中update与delete使用表别名的文章就介绍到这了,更多相关update与delete使用表别名内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！

赞 (0)

相关文章:

版权声明：本文内容由互联网用户贡献，该文观点仅代表作者本人。本站仅提供信息存储服务，不拥有所有权，不承担相关法律责任。 如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 2386932994@qq.com 举报，一经查实将立刻删除。

发表评论

验证码：
最近更新的文章
推荐阅读
大家感兴趣的文章
Copyright © 2017-2025  代码网 保留所有权利. 粤ICP备2024248653号
站长QQ:2386932994 | 联系邮箱：2386932994@qq.com