【MSSQL】SqlServer中delete语句表别名的问题
1、一般情况下删除表数据的sql语句:
delete from products where rowid>2
2、如果想给表起个别名再删除呢,就得像下面这样写了
delete products from products as p where p.rowid>2
也可这样
delete p from products as p where p.rowid>6
3、想一下,什么情况下删除一个表数据的时候要用别名呢?
之所以要用别名,是因为delete的where条件中需要用到子查询写一些条件,举例:
利用自连接删除表中重复的数据:
CREATE TABLE [dbo].[products1] ( [rowid] [int] primary key IDENTITY(1,1) NOT NULL, [name] [nchar](10) NULL, [price] [int] NULL ) INSERT INTO Products VALUES('苹果', 50); INSERT INTO Products VALUES('橘子', 100); INSERT INTO Products VALUES('橘子', 100); INSERT INTO Products VALUES('橘子', 100); INSERT INTO Products VALUES('香蕉', 80); INSERT INTO Products VALUES('香蕉', 80);
delete products from products as p1 where p1.rowid< ( select MAX(p2.rowid) from products p2 where p1.name=p2.name and p1.price=p2.price ) --结果: -- rowid name price -- 1 苹果 50 -- 4 橘子 100 -- 6 香蕉 80
4、其他数据库的情况
ORACLE 可以直接起别名
delete from products p where p.rowid>2
SQLSERVER/MYSQL 可以这样起别名
delete products from products as p where p.rowid>2
delete p from products as p where p.rowid>6
作者:willingtolove
出处:http://www.cnblogs.com/willingtolove/
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。