SQL编写常见错误记录

最新推荐文章于 2023-04-09 00:09:38 发布

转载最新推荐文章于 2023-04-09 00:09:38 发布 · 124 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://my.oschina.net/u/3160411/blog/1590371

文章标签：

#数据库 #python

2019独角兽企业重金招聘Python工程师标准>>>

mysql错误：1093 You can't specify target table for update in FROM clause

错误代码

UPDATE tb_applicant 
SET data_3 = 0
WHERE
	id IN (
		SELECT
			id
		FROM
			 tb_applicant 
		WHERE
			`status` = 1
		AND check_status = 1
		AND id NOT IN (
			SELECT
				applicant_id
			FROM
				tb_test_paper 
		)
	)

原因：MySQL对子查询支持比较薄弱，可以建立缓存表解决，参考博客

更正后的代码

UPDATE tb_applicant 
SET data_3 = 0
WHERE
	id IN (
		SELECT
			temp.id
		FROM
			(select id from tb_applicant 
		WHERE
			`status` = 1
		AND check_status = 1
		AND id NOT IN (
			SELECT
				applicant_id
			FROM
				tb_test_paper 
		)) temp
	)

转载于:https://my.oschina.net/u/3160411/blog/1590371