UPDATE T1, T2,
[INNER JOIN | LEFT JOIN] T1 ON T1.C1 = T2. C1
SET T1.C2 = T2.C2,
T2.C3 = expr
WHERE condition
•
Update table set colume=... , colum=...
UPDATE words
JOIN all_words ON words.word = all_words.word
SET words.meaning = all_words.meaning;
example
accent n.口音,腔调;重音
accept vt.vi.接受;同意
||
\/
accept vt.vi.接受;同意 | vt.vi.接受;同意
select *,count(meaning) count,group_concat(meaning,' | ') mean from e4 group by word having count(meaning)>1;
UPDATE e4 join (select *,count(meaning) count,group_concat(meaning,' | ') mean from e4 group by word having count(meaning)>1) b using(word)
SET e4.meaning = b.mean
WHERE
e4.word = e4.word;
UPDATE
T_MAP_ICON
SET
FD_ASSOCIATE_ID =
(CASE
WHEN FD_ASSOCIATE_ID = 111 THEN 222
WHEN FD_ASSOCIATE_ID = 222 THEN 111
END)
WHERE
FD_ASSOCIATE_ID = 111
OR FD_ASSOCIATE_ID = 222;
UPDATE T_MAP_ICON SET FD_ASSOCIATE_ID =''
WHERE FD_ROWID IN
(
SELECT FD_ROWID FROM T_MAP_ICON
WHERE FD_ASSOCIATE_ID = '163' AND FD_ROWID NOT IN
(
SELECT i.FD_ROWID FROM T_MAP_ICON AS i
JOIN T_BASEINFO AS b
ON b.FD_FACTORYCODE = '163' AND b.FD_ROWID = i.FD_DEVICE_ID
)
)
db2
join 左右的类型要一样,格式尽量严格一致
MERGE INTO T_MAP_ICON i
USING (SELECT * FROM T_BASEINFO WHERE FD_ROWID = '149') b
ON i.FD_DEVICE_ID = b.FD_ROWID
WHEN MATCHED AND 1=1
THEN UPDATE SET i.FD_ASSOCIATE_ID = '149';
MERGE INTO
T_MAP_ICON i
USING (
SELECT * FROM T_BASEINFO
WHERE FD_BRANCHCODE IN ('232','229','230')
) b
ON i.FD_DEVICE_ID = b.FD_ROWID
WHEN MATCHED AND b.FD_EQUNAME = '1#配料秤' THEN UPDATE SET i.FD_CAMERA_INDEX_CODE = 'bb3c2495af4d4105b38fac6cde9fbe47'
WHEN MATCHED AND b.FD_EQUNAME = '2#配料秤' THEN UPDATE SET i.FD_CAMERA_INDEX_CODE = '8f3d846b89104ca1aff33811295baa77'
评论区