table :
ID field1 field21 zhang 902 song 783 zhang 904 zhang 695 wang 86
--------------------------------------------------
请教各位 要将ID=1 列的field2值更新为ID=4 列的field2的值 并且删除所有 与ID=1 列field2字段相等的所有列?结果因该是ID field1 field21 zhang 692 song 784 zhang 695 wang 86
ID field1 field21 zhang 902 song 783 zhang 904 zhang 695 wang 86
--------------------------------------------------
请教各位 要将ID=1 列的field2值更新为ID=4 列的field2的值 并且删除所有 与ID=1 列field2字段相等的所有列?结果因该是ID field1 field21 zhang 692 song 784 zhang 695 wang 86
一个DELETE语句
--------------------------------
再帮忙写个把ID=1列的field2的值更新为ID=4列的field2的值结果:
ID field1 field21 zhang 692 song 784 zhang 695 wang 86
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-03-17 10:38:25
-- Verstion:
-- Microsoft SQL Server 2000 - 8.00.2055 (Intel X86)
-- Dec 16 2008 19:46:53
-- Copyright (c) 1988-2003 Microsoft Corporation
-- Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[field1] varchar(5),[field2] int)
insert [tb]
select 1,'zhang',90 union all
select 2,'song',78 union all
select 3,'zhang',90 union all
select 4,'zhang',69 union all
select 5,'wang',86
--------------开始查询--------------------------
delete t from tb t where exists(select 1 from tb where id<t.id and [field1]=t.[field1] and [field2]=t.[field2])select * from tb
----------------结果----------------------------
/* ID field1 field2
----------- ------ -----------
1 zhang 90
2 song 78
4 zhang 69
5 wang 86(所影响的行数为 4 行)
*/
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[field1] varchar(5),[field2] int)
insert [tb]
select 1,'zhang',90 union all
select 2,'song',78 union all
select 3,'zhang',90 union all
select 4,'zhang',69 union all
select 5,'wang',86
--------------开始查询----------------------------删除删除所有 与ID=1 列field2字段相等的所有列
DELETE FROM tb
WHERE field2 = (SELECT field2 FROM tb WHERE ID = 1)
AND ID <> 1
--将ID=1 列的field2值更新为ID=4 列的field2的值
UPDATE tb
SET field2 = (SELECT field2 FROM tb WHERE ID = 4)
WHERE ID = 1;SELECT * FROM tb;ID field1 field2
----------- ------ -----------
1 zhang 69
2 song 78
4 zhang 69
5 wang 86(4 行受影响)