1.基于联结的delete
2.UPDATE
注意1:
注意2:基于联结的update
3.通过表表达式修改数据
3-1:如何使用
--必须使用:窗口函数
练习:
1.#8.1-2 从sales.customers表插入所有具有订单的客户到dbo.customers'
-- 注意有订单要求 sales.customers的custid存在于Sales.Orders的custid
INSERT INTO dbo.Customers(custid,companyname,country,region,city)
SELECT custid,companyname,country,region,city
FROM Sales.Customers AS sc
WHERE EXISTS
(SELECT * FROM Sales.Orders AS so
WHERE so.custid = sc.custid);
2.
#8.3 删除dbo.orders的名称为巴西的订单行,
-- 而且dbo.orders没有名字 需要从 dbo.Customers查询名字
DELETE FROM dbo.Orders
WHERE EXISTS
(SELECT * FROM dbo.Customers AS dc
WHERE Orders.custid = do.custid
AND dc.country = 'Brazil');
-- 做法2:
DELETE FROM O
FROM dbo.Orders AS O
JOIN dbo.Customers AS C
ON O.custid = C.custid
WHERE country = 'Brazil'
-- 注意delete from这里是一体的,需要再加一个from表名来源
-- 因此delete from 执行语句在where之后,做法1里面不能直接在delete from 中对表起别名,
-- 因为这样在where子句中看不到
3.
UPDATE dbo.Orders
SET shipcountry = county
shipregion = region,
shipcity = city
FROM dbo.Orders db
JOIN dbo.Customers dc
ON db.custid = dc.custid
WHERE dc.country = 'United Kingdom'
-- 小结:之前有from可以用exist没有from不建议使用(因为还要连接)
这一章的3个题都要好好看