在pw_members表里
uid username
42 lbj
43 xxk
44 fghj
.....在pw_posts表里
author authorid
lbj 0
xxk 0
fghj 0
kang 0
.....请问如何用sql语句 把uid的值更新到authorid里呢?假如author 的值在username里不存在,就把author的值更新为56
使pw_posts结果为
author authorid
lbj 42
xxk 43
fghj 44
kang 56
uid username
42 lbj
43 xxk
44 fghj
.....在pw_posts表里
author authorid
lbj 0
xxk 0
fghj 0
kang 0
.....请问如何用sql语句 把uid的值更新到authorid里呢?假如author 的值在username里不存在,就把author的值更新为56
使pw_posts结果为
author authorid
lbj 42
xxk 43
fghj 44
kang 56
from pw_posts b left join pw_members a
on b.author=a.username
SET authorid = ISNULL(
(
SELECT uid
FROM pw_members
WHERE author = pw_posts.username
), 56
)
set authorid=uid
from pw_members
where author=username
CREATE TABLE pw_members(uid INT,username VARCHAR(10))
INSERT INTO pw_members
SELECT 42 ,'lbj' UNION ALL
SELECT 43, 'xxk' UNION ALL
SELECT 44, 'fghj'
IF object_id('pw_posts')IS NOT NULL DROP TABLE pw_posts
CREATE TABLE pw_posts(author VARCHAR(10),authorid INT)
INSERT INTO pw_posts
SELECT 'lbj', 0 UNION ALL
SELECT 'xxk' ,0 UNION ALL
SELECT 'fghj' ,0 UNION ALL
SELECT 'kang' ,0SELECT * FROM pw_membersSELECT * FROM pw_posts UPDATE a
SET a.authorid=isnull(b.uid,56)
FROM pw_posts a
LEFT JOIN pw_members b
ON b.username=a.author