晴风

准备数据

-- 建表
CREATE TABLE test1_0616(idd varchar(10),val varchar(20));
CREATE TABLE test2_0616(idd varchar(10),val varchar(20));
-- 准备数据
INSERT INTO test1_0616 VALUES('01','1111');
INSERT INTO test1_0616 VALUES('02','1111');
INSERT INTO test1_0616 VALUES('02','1111');
INSERT INTO test1_0616 VALUES('03','1111');
INSERT INTO test1_0616 VALUES('06','1111');

INSERT INTO test2_0616 VALUES('01','01');
INSERT INTO test2_0616 VALUES('02','02');
INSERT INTO test2_0616 VALUES('02','022');
INSERT INTO test2_0616 VALUES('03','03');
INSERT INTO test2_0616 VALUES('04','04');
-- 测试
TRUNCATE TABLE test1_0616;
SELECT * FROM test1_0616;

根据一张表的数据更新另一张表

UPDATE test1_0616 t1 SET val=(SELECT val FROM test2_0616 t2 WHERE t1.idd=t2.idd)

结果

可以看到

1.idd=02在表2中存在两条,会取第一条更新表1的值

2.idd=06在表2中并不存在,但是表1中idd=06的值也被更新了,被赋了null值

更新

若不想上述结果idd=06的值被更新,则需要改一下sql语句

UPDATE test1_0616 t1 SET val=(SELECT val FROM test2_0616 t2 WHERE t1.idd=t2.idd) WHERE EXISTS (SELECT 1 FROM test2_0616 t2 WHERE t1.idd=t2.idd)

结果

可以看到,idd=06并没有被更新