当前位置:Gxlcms > 数据库问题 > SQL使用之关联更新、批量插入

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

时间:2021-07-01 10:21:17 帮助过:19人阅读

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

  • 关联更新

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());

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

标签:bsp   send   base   now()   int   方式   and   合成   player   

人气教程排行