SQL Update用法为
update table set col=value where conditions
SQL join之后更新表
update t1 set t1.col=value
from t1 inner join t2 on t1.col=t2.col
where conditons
SQL 还有一种更新方法,进行复杂开窗函数排序后,也可直接更新到表
测试例子如下:
更新表中“姓名”列和“是否满分”列相同的,且“是否满分列”为y,
保留一个满分为y,其他满分更新为n
语句如下:
CREATE TABLE #a1(姓名 VARCHAR(20),年级班级 VARCHAR(20),是否满分 VARCHAR(10))
INSERT INTO #a1
SELECT '小王','0101','y' UNION ALL
SELECT '小李','0101','n' UNION ALL
SELECT '小张','0101','n' UNION ALL
SELECT '小王','0201','y' UNION ALL
SELECT '小李','0201','n' UNION ALL
SELECT '小张','0201','y'
select * from #a1;
with t1 as(
SELECT row_number() OVER (partition by 姓名,是否满分 ORDER BY [姓名]) n,* FROM #a1
where 是否满分='y'
)
UPDATE t1 SET t1.[是否满分] = 'n' WHERE t1.n>1;
select * from #a1;