操作update时报错不能更新from出来的数据

发表于 | 分类于 | 阅读次数：

You can’t specify target table ‘xsdb’ for update in FROM clause参考

错误写法总结：不能先select出同一表中的某些值，再update这个表(在同一语句中)，即不能依据某字段值做判断再来更新某字段的值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- 根据条件批量更新逾期时间
UPDATE XSDB set JSRQ = 1609430399999 where GUID in (
  SELECT
    -- FROM_UNIXTIME(xsd.JSRQ/1000, '%Y-%m-%d %H:%i:%S') AS JSRQQ,
    `xsd`.GUID
    FROM
    (
      SELECT dqdd.CJJXSID, dqdd.SHJXSID FROM DDB dqdd WHERE dqdd.GUID = 906000029082
    ) AS ydd,
    `DDB` AS `dd`
    INNER JOIN `XSDB` AS `xsd` ON `dd`.`GUID` = `xsd`.`DDID`
    WHERE
      `dd`.`CJJXSID` = ydd.CJJXSID
      AND `dd`.`SHJXSID` = ydd.SHJXSID
      AND `dd`.`DDZT` = '1'
      AND `dd`.`DDLX` IN (1, 2, 9)
      AND `dd`.`WSZJE` >= 0
      AND `dd`.`ZTBS` = 'E'
      AND `xsd`.`WSZJE` >= 0
      AND `xsd`.`JSRQ` < UNIX_TIMESTAMP(NOW())*1000
      AND `xsd`.`ZTBS` = 'E'
      AND `xsd`.`CWHXZT` = '0'
      AND `xsd`.`ZJE` > '0'
)

正确写法总结：将SELECT出的结果再通过中间表SELECT一遍，这样就规避了错误；需要注意的是，这个问题只出现于MySQL，MSSQL和Oracle不会出现此问题。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- 根据条件批量更新逾期时间
UPDATE XSDB set JSRQ = 1609430399999 where GUID in (
	select lsXSDB.GUID from (
		SELECT
			FROM_UNIXTIME(xsd.JSRQ/1000, '%Y-%m-%d %H:%i:%S') AS JSRQQ,
			`xsd`.*
			FROM
			(
				SELECT dqdd.CJJXSID, dqdd.SHJXSID FROM DDB dqdd WHERE dqdd.GUID = 906000029082
			) AS ydd,
			`DDB` AS `dd`
			INNER JOIN `XSDB` AS `xsd` ON `dd`.`GUID` = `xsd`.`DDID`
			WHERE
				`dd`.`CJJXSID` = ydd.CJJXSID
				AND `dd`.`SHJXSID` = ydd.SHJXSID
				AND `dd`.`DDZT` = '1'
				AND `dd`.`DDLX` IN (1, 2, 9)
				AND `dd`.`WSZJE` >= 0
				AND `dd`.`ZTBS` = 'E'
				AND `xsd`.`WSZJE` >= 0
				AND `xsd`.`JSRQ` < UNIX_TIMESTAMP(NOW())*1000
				AND `xsd`.`ZTBS` = 'E'
				AND `xsd`.`CWHXZT` = '0'
				AND `xsd`.`ZJE` > '0'
	) lsXSDB
)
0%