Question

求一个SQL语句修改表内数据的方法.

Answer 1

如下:
--演示环境
CREATE
TABLE
fz_buy(BID
INT
IDENTITY(1,1),KUID
INT,DD
INT,WDID
INT)
INSERT
INTO
fz_buy(kuid,dd,wdid)
SELECT
8,0,NULL
UNION
ALL
SELECT
NULL,1,NULL
UNION
ALL
SELECT
NULL,1,NULL
UNION
ALL
SELECT
16,0,NULL
UNION
ALL
SELECT
NULL,4,NULL
UNION
ALL
SELECT
NULL,4,NULL
--查询
SELECT
*
FROM
fz_buy
--更新
UPDATE
fz_buy
SET
WDID=CASE
WHEN
fz_buy.KUID
IS
NULL
THEN
TB.KUID
ELSE
NULL
END
FROM
(SELECT
BID,KUID
FROM
fz_buy
WHERE
dd=0)TB
WHERE
fz_buy.DD=TB.BID
--查询更新结果
SELECT
*
FROM
fz_buy
/*结果:
BID
KUID
DD
WDID
-----------
-----------
-----------
-----------
1
8
0
NULL
2
NULL
1
8
3
NULL
1
8
4
16
0
NULL
5
NULL
4
16
6
NULL
4
16
(6
行受影响)
*/
--删除演示环境
DROP
TABLE
fz_buy