多次用到的sql 每次找文档都找好久 sql如下
UPDATE `user` AS A INNER JOIN (SELECT id FROM `user` WHERE `sid`=2) AS B ON = SET ='123'
上面sql的使用场景：希望将多表联查的数据补充到一张表中时使用，或者修改的数据在别的表中时使用。
自己的使用场景：补全表中的pid条件参考表的code关联
UPDATE `common_district` AS A
INNER JOIN (SELECT id,`code` FROM common_district
WHERE id>600000 AND id<=900000
) AS B
ON = SET =
WHERE >600000 AND <=900000;
使用中遇到的问题70w的数据执行时间过长可分段操作。
原文地址：↓
MySQL一条语句实现同时查询和修改
/**********************************************/
其它知识点记录：
删除A表数据时where条件需要查询A表，此时需要包装查询条件的A表为另一个视图方可执行删除语句，
DELETE FROM `common_district`
WHERE `id` NOT IN (
SELECT
t.`id`
FROM (
SELECT `id` FROM `common_district` WHERE 1=1
AND city_code = "6103"
OR `code` = '61'
OR `code` = '6103'
) t
)
复制表结构以及复制表结构和表数据
复制表结构
CREATE TABLE common_district1 LIKE common_district;
复制表结构和表数据
CREATE TABLE common_district2 SELECT * FROM `common_district` WHERE 1=1
AND city_code = "6103"
OR `code` = '61'
OR `code` = '6103'
插入数据时查询其它表中的数据
INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;