CREATE TABLE book(n VARCHAR(100),c VARCHAR(100)); INSERT into book(n,c) Select 'a','123456' UNION Select 'b','123467' UNION Select 'a','' UNION Select 'c','234435' ; UPDATE book b,(select n, max(c) as c from book group by n) a set b.c =a.c where a.n=b.n and CHAR_LENGTH(ifnull(b.c,''))=0;
update book set cardnum=ISNULL(cardnum, (select MAX(cardnum) from book b where book.name=b.name))
把cardnum 为空的值,更新为同NAME的非空cardnum 值 e.g.--查询 SELECT a.*,b.cardnum AS cardnum2 FROM book AS a CROSS APPLY(SELECT TOP 1 cardnum FROM book WHERE name=a.name AND cardnum>'') AS b WHERE a.cardnum IS NULL OR a.cardnum =''--更新 UPDATE a SET cardnum=b.cardnum FROM book AS a CROSS APPLY(SELECT TOP 1 cardnum FROM book WHERE name=a.name AND cardnum>'') AS b WHERE a.cardnum IS NULL OR a.cardnum =''
CREATE TABLE book(n VARCHAR(100),c VARCHAR(100));
INSERT into book(n,c)
Select 'a','123456' UNION
Select 'b','123467' UNION
Select 'a','' UNION
Select 'c','234435'
;
UPDATE book b,(select n, max(c) as c from book group by n) a
set b.c =a.c
where a.n=b.n and CHAR_LENGTH(ifnull(b.c,''))=0;
为什么是select max(cardnum)?
我就是想实现为cardnum 为空的字段赋值,值为与它name有相同记录的cardnum的值。还请高手详解!多谢。。
e.g.--查询
SELECT a.*,b.cardnum AS cardnum2
FROM book AS a
CROSS APPLY(SELECT TOP 1 cardnum FROM book WHERE name=a.name AND cardnum>'') AS b
WHERE a.cardnum IS NULL OR a.cardnum =''--更新
UPDATE a
SET cardnum=b.cardnum
FROM book AS a
CROSS APPLY(SELECT TOP 1 cardnum FROM book WHERE name=a.name AND cardnum>'') AS b
WHERE a.cardnum IS NULL OR a.cardnum =''
为什么是select max(cardnum)?
我就是想实现为cardnum 为空的字段赋值,值为与它name有相同记录的cardnum的值。还请高手详解!多谢。。是为了防止有多条记录。
例如,表中还有一条“a 234567”,那更新name为a的那条cardnum为空的记录,就需要在两条cardnum不为空的记录中选择出一条。
如果表中记录多,也可以在后面增加一个where,筛选出cardnum为空的记录才update。