表结构如下:
插入之前:
想要实现 name字段如果有值不更新,如果不存在则更新为新值d。
CREATE TABLE `test` ( `id` int(10) NOT NULL auto_increment, `name` varchar(222) default '', PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |
插入之前:
id name
------ ------
1 a
2 b
3 c
4
5
UPDATE test t1 INNER JOIN
( SELECT
CASE WHEN NAME IS NULL OR NAME = '' THEN 'd'
ELSE NAME END AS newname
FROM test WHERE id =4)
( SELECT
CASE WHEN NAME IS NULL OR NAME = '' THEN 'd'
ELSE NAME END AS newname
FROM test WHERE id =4)
AS t
ON t1.id = t1.id
SET NAME = t.newname
WHERE id = 4
ON t1.id = t1.id
SET NAME = t.newname
WHERE id = 4
在mysql中要使用update set = (select ) 之类的语句必须要使用inner join 等值连接来实现
执行sql语句之后
id name
------ ------
1 a
2 b
3 c
4 d
5
再次执行上面的语句,id=4的name值依然是d,不会更新。