一直用MS-SQL，现在有个MYSQL的语法问题。。。。

[jmx123456789]

很少用到MYSQL 这里有个语法问题需要大虾们帮帮忙：

UPDATE ecs_users SET `status` = 1
WHERE user_id IN(
	SELECT DISTINCT t1.user_id FROM ecs_users t1
	LEFT JOIN ecs_user_account t2 ON t1.user_id = t2.user_id
	WHERE (t2.add_time IS NULL AND t1.reg_time + 180 > NOW()) OR
				(t2.add_time IS NOT NULL AND t2.add_time + 180 > NOW())
)

[Err] 1093 - You can't specify target table 'ecs_users' for update in FROM clause

        /* 该语句测试通过，可能返回NULL值 */
	SELECT DISTINCT t1.user_id FROM ecs_users t1
	LEFT JOIN ecs_user_account t2 ON t1.user_id = t2.user_id
	WHERE (t2.add_time IS NULL AND t1.reg_time + 180 > NOW()) OR
				(t2.add_time IS NOT NULL AND t2.add_time + 180 > NOW())

PS:MYSQL板块人气不如咱这边 所以在这边也来一贴~~~~

[中国风]

这样试试

UPDATE ecs_users t1
    LEFT JOIN ecs_user_account t2 ON t1.user_id = t2.USER_ID
SET status = 1
    WHERE (t2.add_time IS NULL AND t1.reg_time + 180 > NOW()) OR
                (t2.add_time IS NOT NULL AND t2.add_time + 180 > NOW())

[Ginnnnnnnn]

mysql 的语法要改成这样

UPDATE ecs_users a inner join (SELECT DISTINCT t1.user_id FROM ecs_users t1
    LEFT JOIN ecs_user_account t2 ON t1.user_id = t2.user_id
    WHERE (t2.add_time IS NULL AND t1.reg_time + 180 > NOW()) OR
                (t2.add_time IS NOT NULL AND t2.add_time + 180 > NOW())) b on a.user_id  = b.user_id
SET a.`status` = 1

[中国风]

UPDATE ecs_users t1 LEFT JOIN ecs_user_account t2 ON t1.user_id = t2.USER_ID SET t1.status = 1 WHERE (t2.add_time IS NULL AND t1.reg_time + 180 > NOW()) OR (t2.add_time IS NOT NULL AND t2.add_time + 180 > NOW()) 更新列加上表别名

[jmx123456789]

引用 1 楼 roy_88 的回复:

引用 2 楼 KanzakiOrange 的回复:

谢过~~~