契合某一条件的数据，全部在本本表复制一份，改动一个标识_Sql Server

符合某一条件的数据，全部在本本表复制一份，改动一个标识
例如

taba
表结果
id name eat
1 tom ok
2 rose no

将所有no 的复制一份新复制的no改为yes

结果
id name eat
1 tom ok
2 rose yes
3 rose ok

最好是两种方式实现 1 直接用语句 2，用触发器实现，sql2000

谢谢

------解决方案--------------------

SQL code

--> 测试数据：[tb]IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]GO CREATE TABLE [tb]([id] INT,[name] VARCHAR(4),[eat] VARCHAR(2))INSERT [tb]SELECT 1,'tom','ok' UNION ALLSELECT 2,'rose','no'--------------开始查询--------------------------SELECT id=ROW_NUMBER() OVER(ORDER BY id),[name],[eat]FROM(SELECT * FROM [tb]UNION ALL SELECT [id],[name],'ok'FROM [tb] WHERE [eat]='no') AS t----------------结果----------------------------/* -------------------- ---- ----1                    tom  ok2                    rose no3                    rose ok(3 行受影响)*/
------解决方案--------------------
SQL code--sql 2000:先插入后更新begin traninsert into taba select name,'ok' from taba where eat='no'update taba set eat='yes' where eat='no'commit--如果是sql 2008的话可以用带OUTPUT的UPDATE语句update taba set eat='yes' output deleted.name,'ok' into tabawhere eat='no'----------结果-----------/*id          name eat----------- ---- ----1           tom  ok2           rose yes3           rose ok*/