求一条修改语句
一直表a (fid,fName,fdbID)
数据如下
fid,fName,fdbID
1 A 1
2 B 1
9 A 2
我想修改成如下效果
fid,fName,fdbID
1 A 1
2 B 1
1 A 2
-------------------------------------------------
就是把 fdbID=2 的记录和fDbID=1 的记录做比较 如果fName相同 把fid 修改成 (fdbID=1)的fid 请问怎么写?
[解决办法]
update a set fid = (select min(fid) from a where fName = t.fName) from a t
[解决办法]
create table a(fid int,fName varchar(10),fdbID int)insert into a values( 1 ,'A', 1)insert into a values( 2 ,'B', 1)insert into a values( 9 ,'A', 2)goupdate a set fid = (select min(fid) from a where fName = t.fName) from a tselect * From adrop table a /*fid fName fdbID ----------- ---------- ----------- 1 A 12 B 11 A 2(所影响的行数为 3 行)*/
[解决办法]
update t2set fid=t1.fidfrom a as t1 inner join a as t2 on t1.fname=t2.fname where t1.fdbID=1 and t2.fdbID=2
[解决办法]