表userinfo 字段userid regprize两个表的字段USDERID是相同的,求个SQL语句,查询表USDERINFO,如果regprize=26,就将表gameex里的point值减20,如果表gameex里的point值小于26,则修改表gameex里的point字段值为6end if
UPDATE A SET point = CASE WHEN B.regprize = 26 THEN A.point - 20 WHEN B.regprize < 26 THEN 6 ELSE A.point END FROM gameex AS A JOIN userinfo AS B ON A.USDERID = B.USDERID;
UPDATE A SET point = CASE WHEN B.regprize = 26 THEN A.point - 20 WHEN B.regprize < 26 THEN 6 END FROM gameex AS A JOIN userinfo AS B ON A.USDERID = B.USDERID WHERE B.regprize <= 26;
谢谢liangCK ,只要在运行这段语句就可以了吗? UPDATE A SET point = CASE WHEN B.regprize = 26 THEN A.point - 20 WHEN B.regprize < 26 THEN 6 END FROM gameex AS A JOIN userinfo AS B ON A.USDERID = B.USDERID WHERE B.regprize <= 26;
update a set a.point=case when b.regprize=26 then a.point-20 when b.regprize<26 then 6 else a.point end from gameex a, userinfo b where a.userid=b.userid
树哥,SQL里运行上面的语句就可以了吗?
可能大家有点误解 WHEN B.regprize = 26 THEN A.point - 20 如果regprize=26,就将表gameex里的point值减20, WHEN B.regprize < 26 THEN 6 是point小于26如果表gameex里的point值小于26,则修改表gameex里的point字段值为6
point = CASE
WHEN B.regprize = 26 THEN A.point - 20
WHEN B.regprize < 26 THEN 6
ELSE A.point
END
FROM gameex AS A
JOIN userinfo AS B
ON A.USDERID = B.USDERID;
point = CASE
WHEN B.regprize = 26 THEN A.point - 20
WHEN B.regprize < 26 THEN 6
END
FROM gameex AS A
JOIN userinfo AS B
ON A.USDERID = B.USDERID
WHERE B.regprize <= 26;
UPDATE A SET
point = CASE
WHEN B.regprize = 26 THEN A.point - 20
WHEN B.regprize < 26 THEN 6
END
FROM gameex AS A
JOIN userinfo AS B
ON A.USDERID = B.USDERID
WHERE B.regprize <= 26;
a
set
a.point=case
when b.regprize=26 then a.point-20
when b.regprize<26 then 6
else a.point
end
from
gameex a,
userinfo b
where
a.userid=b.userid
如果regprize=26,就将表gameex里的point值减20, WHEN B.regprize < 26 THEN 6 是point小于26如果表gameex里的point值小于26,则修改表gameex里的point字段值为6