Question

[SQL]UPDATE 语句中使用SELECT的结果

UPDATE ADMIN_USER
SET HASMSG = exp1 IN
(SELECT COUNT(*) AS exp1
FROM ADMIN_MSG
WHERE RECEIVE = 1 AND UNREAD = 1)
WHERE USERID = 1

上边是我胡写的
有2个表admin_user(hasmsg记录未读数),admin_msg(unread=1表示没读),将count(*) where unread=1的count(*) 更新到admin_user的hasmsg中

谢谢

Answer 1

UPDATE ADMIN_USER SET HASMSG =(SELECT COUNT(*)FROM ADMIN_MSG WHERE RECEIVE = 1 AND UNREAD = 1) WHERE USERID = 1 这就行了

Answer 2

update a,b set a.hasmsg = count(b.*) from admin_user a,admin_msg b where b.unread = 1