mysql关联批量插入_SQL使用之关联更新、批量插入

使用场景

某个字段数据异常，利用另外一张表同步修改该表异常字段的数据;

关联更新

UPDATE tableName1 AS t1

LEFT JOIN tableName12 AS t2 ON t1.xx = t2.xx

SET t1.Col = ''

WHERE

t1.Id = 147852

AND t2.`Name` <> ''

当然，也可以使用Inner Join或者Right Join

在实际操作中，还有可能补入相关业务数据来保持数据完整，有以下两种操作方式

查询生成数据

INSERT INTO email

SELECT UUID() AS Id,PlayerId AS PlayerId,'00000000-0000-0000-0000-000000000000' AS SendPlayerId,'系统' AS SendPlayerName,'奖励' AS SUBJECT, '恭喜获得奖励。' AS Content,0 AS EmailType,b'0' AS IsReceive,'110' AS Reward,NOW() AS SendTime,500 AS ModuleId,b'0' AS IsDelete,b'0' AS IsRead FROM info WHERE Id=11390 ORDER BY Score DESC LIMIT 0,10;

上面的这种仅适用于可构造新数据，内容可以合成sql，格式、数据都比较单一，但对某些数据动态的、并依靠其他表，这种操作稍显无力。

设置列变量，查询另外一张表头的数据，再组装SQL

SET @id:='';

SELECT @id:=Id FROM base_info WHERE type='1';

--  先删除已有的，覆盖

DELETE FROM info WHERE Id=@id;

INSERT INTO info(Id,Count1,Count2,Count3,Count4,Count5,Time6,Num7,Time8)

VALUES (@id,0,0,0,0,9,NOW(),0,NOW());