查询作为SELECT而不是更新?
问题描述:
我试图在MySQL中使用联系人表和accounts_contacts连接表执行重复的清理查询。我有查询工作作为一个SELECT查询,但当我尝试使其更新,我得到一个非特定的错误:查询作为SELECT而不是更新?
#1064 - 您的SQL语法中有错误;检查对应于你的MySQL服务器版本使用附近的正确语法手册“FROM sugarDB.contacts INNER JOIN(SELECT dupIDs.id FROM(SELECT克拉”在行3
下面是该查询:
UPDATE ctUpdate
SET ctUpdate.deleted = 1
FROM sugarDB.contacts AS ctUpdate
INNER JOIN (
SELECT dupIDs.id
FROM (
SELECT ctIDs.id
FROM sugarDB.contacts AS ctIDs
INNER JOIN (
SELECT ctSource.first_name,
ctSource.last_name
FROM sugarDB.contacts AS ctSource
GROUP BY ctSource.first_name,
ctSource.last_name
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC
)
AS ctSource
ON ctIDs.first_name = ctSource.first_name
AND ctIDs.last_name = ctSource.last_name
)
AS dupIDs
LEFT JOIN sugarDB.accounts_contacts AS a2cIDs
ON dupIDs.id = a2cIDs.contact_id
WHERE a2cIDs.id IS NULL
)
AS dupIDs
ON ctUpdate .id = dupIDs.id
;
我已经倒在这几天了,我找不到任何错误的帮助是极大的赞赏
答
There is no FROM
clause in UPDATE
statements in MySql.
相反,你的加入应该是你UPDATE
条款的一部分。!
UPDATE sugarDB.contacts AS ctUpdate
INNER JOIN (
SELECT dupIDs.id
FROM (
SELECT ctIDs.id
FROM sugarDB.contacts AS ctIDs
INNER JOIN (
SELECT ctSource.first_name,
ctSource.last_name
FROM sugarDB.contacts AS ctSource
GROUP BY ctSource.first_name,
ctSource.last_name
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC
)
AS ctSource
ON ctIDs.first_name = ctSource.first_name
AND ctIDs.last_name = ctSource.last_name
)
AS dupIDs
LEFT JOIN sugarDB.accounts_contacts AS a2cIDs
ON dupIDs.id = a2cIDs.contact_id
WHERE a2cIDs.id IS NULL
)
AS dupIDs
ON ctUpdate .id = dupIDs.id
SET ctUpdate.deleted = 1
;
+0
谢谢!现在看起来很明显! – BayssMekanique 2012-08-02 16:38:16
您可以“选择”,但不能“更新”。这真的很简单:) – paulsm4 2012-08-02 16:31:36