fresid fprice pcost fstyle
1001 6.5 6.5 3
1002 11.5 11.5 3
1002 12.5 12.5 3
1003 8.5 8.5 3
1001 12 0 7
1002 20 0 7
1003 15 0 7
1003 15 0 7
怎样让fstyle=7 的行里pcost值 更新为fstyle=3行里的fprice(最小值)
谢谢!!!
1001 6.5 6.5 3
1002 11.5 11.5 3
1002 12.5 12.5 3
1003 8.5 8.5 3
1001 12 0 7
1002 20 0 7
1003 15 0 7
1003 15 0 7
怎样让fstyle=7 的行里pcost值 更新为fstyle=3行里的fprice(最小值)
谢谢!!!
这样执行不对,没有考虑fresid,
要求fresid相同的才更新值
from tb a where fstyle=7
INSERT dbo.tba
SELECT '1001', 6.5, 6.5, 3 UNION ALL
SELECT '1002', 11.5, 11.5, 3 UNION ALL
SELECT '1002', 12.5, 12.5, 3 UNION ALL
SELECT '1003', 8.5, 8.5, 3 UNION ALL
SELECT '1001', 12, 0, 7 UNION ALL
SELECT '1002', 20, 0, 7 UNION ALL
SELECT '1003', 15, 0, 7 UNION ALL
SELECT '1003', 15, 0, 7UPDATE dbo.tba
SET pcost = ( SELECT MIN(fprice)
FROM dbo.tba b
WHERE dbo.tba.fresid = b.fresid
)
WHERE fstyle = 7 SELECT *
FROM dbo.tbaDROP TABLE dbo.tba
/*(4 行受影响)
fresid fprice pcost fstyle
---------- ---------------------- ---------------------- -----------
1001 6.5 6.5 3
1002 11.5 11.5 3
1002 12.5 12.5 3
1003 8.5 8.5 3
1001 12 6.5 7
1002 20 11.5 7
1003 15 8.5 7
1003 15 8.5 7(8 行受影响)*/
UPDATE dbo.tba
SET pcost = ( SELECT MIN(fprice)
FROM dbo.tba b
WHERE dbo.tba.fresid = b.fresid and dbo.tba.fstyle=3
)
WHERE fstyle = 7 SELECT *
FROM dbo.tba