环境
MacBook Pro
备份表并重命名
DROP TABLE IF EXISTS new_table;
CREATE TABLE new_table LIKE old_table;
INSERT INTO new_table SELECT * FROM old_table;
-- 假设第三条SQL检测不通过,就把具体的字段写出来
=-=-=-=-=-=-=-=-=-=2020年8月10日-=-=-=-=-=-=-=start-=-=-=
create table tableName1 as select * from tableName2
-- 如果不想同步数据
create table tableName1 as select * from tableName2 where 1 = 2
=-=-=-=-=-=-=-=-=-=2020年8月10日-=-=-=-=-=-=-=end-=-=-=
update select
更新时使用临时表,但是该临时表的数据又来自于本表,
这个时候进行更新操作会报错,因为MySQL不允许对本地进行先查询后更新。
变通的方法就是把查询出来的结果再用select包一下,变成一个临时别名表就可以了。
=-=-=-=-=-=-=-=-=-=2020年10月15日-=-=-=-=-=-=-=start-=-=-=
今天被下面的SQL搞了很久,总是被you can't specify target table for update in from clause
。
刚开始我是如下写法:
UPDATE xrshop.product AS aaa
INNER JOIN (
SELECT
we.sn,
we.productId
FROM
warehouse.examination we
WHERE
we.productId IN (
SELECT
p.id
FROM
xrshop.product AS p
WHERE
CODE IS NULL
AND p.majorCategoryId = 17
... 省略一些条件
)
) AS b
SET aaa.`code` = b.sn
WHERE b.productId = aaa.id
这样总是报错:后来我改为:
UPDATE xrshop.product AS aaa
INNER JOIN (select sn, productId from (
SELECT
we.sn,
we.productId
FROM
warehouse.examination we
WHERE
we.productId IN (
SELECT
p.id
FROM
xrshop.product AS p
WHERE
CODE IS NULL
AND p.majorCategoryId = 17
AND p.minorCategoryId IN (
17005,
17009,
17012,
17013,
17010,
17011,
17015
)
)
) AS b ) bbb
SET aaa.`code` = bbb.sn
WHERE bbb.productId = aaa.id
也就是对INNER JOIN
后面的查询结果集,再用select
包一下,然后就可以了;
delete select
如果删除表A的数据,是先从表A中查询出来的,则同理一样,
也是要先用select
包一下:
DELETE from xrshop.product WHERE id in (select a.id from (SELECT
p.id
FROM
xrshop.product AS p
WHERE
CODE IS NULL
AND p.majorCategoryId = 17
AND p.minorCategoryId IN (
17005,
17009,
17012,
17013,
17010,
17011,
17015
)) as a)
not in 优化
因为 not in不走索引,所以不在不得已情况下,就不要使用not in
下面使用 join 来替代not in 做查询
select ID from A where ID not in (select ID from B)
替换为
select A.ID from A left join B on A.ID=B.ID and B.ID is null
或者:
select A.ID from A left join B on A.ID=B.ID where B.ID is null