在工作中,有一个数据库,截取了很小的一段如下:
ProNr Userfield OperationTxt Std Act
3405479 894 Manual side B 7.8 3
3405479 726 Manual side A 2.8 3
3405481 894 Manual side B 7.8 2.75
3405481 726 Manual side A 2.8 2.75
3405483 726 Manual side A 2.8 3
3405483 894 Manual side B 7.8 3
3405485 726 Manual side A 1.7 1
3405485 894 Manual side B 4.1 1
3405591 894 Manual side B 3.9 1.25
3405591 726 Manual side A 1.7 1.25
3405593 726 Manual side A 1.8 1.25
3405593 894 Manual side B 4.4 1.25
3405861 894 Manual side B 11.3 3.5
3405861 726 Manual side A 3.9 3.5
3405863 894 Manual side B 11.3 2.5
3405863 726 Manual side A 3.9 2.5每个工单有两行,工序为726和894,726和894的"Std"应该是一样的,此处894的数字错误,要把每个工单的894的std值改为726的值,假设表名为TB,我写了下述语句,但结果不对,请教高手应该怎么写:UPDATE TB
SET std =(SELECT Std FROM TB AS A WHERE (Userfield = 726))
WHERE (Userfield = 894) AND (A.ProNr = ProNr)
sql语句
ProNr Userfield OperationTxt Std Act
3405479 894 Manual side B 7.8 3
3405479 726 Manual side A 2.8 3
3405481 894 Manual side B 7.8 2.75
3405481 726 Manual side A 2.8 2.75
3405483 726 Manual side A 2.8 3
3405483 894 Manual side B 7.8 3
3405485 726 Manual side A 1.7 1
3405485 894 Manual side B 4.1 1
3405591 894 Manual side B 3.9 1.25
3405591 726 Manual side A 1.7 1.25
3405593 726 Manual side A 1.8 1.25
3405593 894 Manual side B 4.4 1.25
3405861 894 Manual side B 11.3 3.5
3405861 726 Manual side A 3.9 3.5
3405863 894 Manual side B 11.3 2.5
3405863 726 Manual side A 3.9 2.5每个工单有两行,工序为726和894,726和894的"Std"应该是一样的,此处894的数字错误,要把每个工单的894的std值改为726的值,假设表名为TB,我写了下述语句,但结果不对,请教高手应该怎么写:UPDATE TB
SET std =(SELECT Std FROM TB AS A WHERE (Userfield = 726))
WHERE (Userfield = 894) AND (A.ProNr = ProNr)
sql语句
STD = T726.STD
FROM TB T894,TB T726
WHERE T894.ProNr = T726.ProNr
AND T894.Userfield = 894
AND T726.Userfield = 726
from TB as a inner join TB as b on a.ProNr = b.ProNr
WHERE a.Userfield = 894 and b.Userfield = 726
SET b.std = a.std
FROM ( SELECT *
FROM TB
WHERE Userfield = 726
) a
INNER JOIN ( SELECT *
FROM TB
WHERE Userfield = 894
) b ON a.pronr = b.pronr
drop table [tb]
Go
Create table [tb]([ProNr] int,[Userfield] smallint,[Operation] nvarchar(6),[Txt] nvarchar(6),[Std] numeric(3,1),[Act] numeric(3,2))
Insert into [tb]
Select 3405479,894,N'Manual',N'side B',7.8,3
Union all Select 3405479,726,N'Manual',N'side A',2.8,3
Union all Select 3405481,894,N'Manual',N'side B',7.8,2.75
Union all Select 3405481,726,N'Manual',N'side A',2.8,2.75
Union all Select 3405483,726,N'Manual',N'side A',2.8,3
Union all Select 3405483,894,N'Manual',N'side B',7.8,3
Union all Select 3405485,726,N'Manual',N'side A',1.7,1
Union all Select 3405485,894,N'Manual',N'side B',4.1,1
Union all Select 3405591,894,N'Manual',N'side B',3.9,1.25
Union all Select 3405591,726,N'Manual',N'side A',1.7,1.25
Union all Select 3405593,726,N'Manual',N'side A',1.8,1.25
Union all Select 3405593,894,N'Manual',N'side B',4.4,1.25
Union all Select 3405861,894,N'Manual',N'side B',11.3,3.5
Union all Select 3405861,726,N'Manual',N'side A',3.9,3.5
Union all Select 3405863,894,N'Manual',N'side B',11.3,2.5
Union all Select 3405863,726,N'Manual',N'side A',3.9,2.5UPDATE a
SET Std=b.Std
FROM tb AS a,tb AS b
WHERE a.Userfield=894
AND b.Userfield=726
AND a.ProNr=b.ProNr/*
ProNr Userfield Operation Txt Std Act
----------- --------- --------- ------- ------ -----
3405479 894 Manual side B 2.8 3.00
3405479 726 Manual side A 2.8 3.00
3405481 894 Manual side B 2.8 2.75
3405481 726 Manual side A 2.8 2.75
3405483 726 Manual side A 2.8 3.00
3405483 894 Manual side B 2.8 3.00
3405485 726 Manual side A 1.7 1.00
3405485 894 Manual side B 1.7 1.00
3405591 894 Manual side B 1.7 1.25
3405591 726 Manual side A 1.7 1.25
3405593 726 Manual side A 1.8 1.25
3405593 894 Manual side B 1.8 1.25
3405861 894 Manual side B 3.9 3.50
3405861 726 Manual side A 3.9 3.50
3405863 894 Manual side B 3.9 2.50
3405863 726 Manual side A 3.9 2.50
*/
Update b
而不是
Update TB?????
update a set std=
(select std from tb b where userfield=(select max(userfield) from tb group by a.pronr) and b.pronr=a.pronr) from tb a