如何删除 mysql 表中包含另一个查询结果中的字段的行？

发布于 2024-09-03 07:04:04 字数 631 浏览 13 评论 0原文

该语句如下所示：

DELETE FROM videoswatched vw2 
 WHERE vw2.userID IN ( SELECT vw.userID 
                         FROM videoswatched vw
                         JOIN users u ON vw.userID=u.userID
                        WHERE u.companyID = 1000
                     GROUP BY userID )

这对我来说看起来不错，并且 SELECT 语句可以独立工作（生成具有单列“userID”的行。

基本上，我想删除“videoswatched”表中的条目，其中 userID 在在加入 users 表后，发现 videowatched 条目的 companyID=1000。

我怎样才能做到这一点而不在我的 sql 语法中出现错误？它说错误就在

vw2 WHERE vw2.userID IN (
    SELECT vw.userID FROM videoswatched vw
    JOIN users u

第 1 行。

原文

Here's what the statement looks like:

DELETE FROM videoswatched vw2 
 WHERE vw2.userID IN ( SELECT vw.userID 
                         FROM videoswatched vw
                         JOIN users u ON vw.userID=u.userID
                        WHERE u.companyID = 1000
                     GROUP BY userID )

That looks decent to me, and the SELECT statement works on its own (producing rows with a single column 'userID'.

Basically, I want to delete entries in the 'videoswatched' table where the userID in the videoswatched entry, after joining to the users table, is found to have companyID=1000.

How can I do this without getting the error in my sql syntax? It says the error is near:

vw2 WHERE vw2.userID IN (
    SELECT vw.userID FROM videoswatched vw
    JOIN users u

and on line 1.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

吃→可爱长大的 2024-09-10 07:04:04

评论中已经回答了：您需要删除表别名，在 MySQL 的 DELETE 语句语法（也没有必要）。

编辑：顺便说一句，试试这个（从记忆中编码，可能是错误的）：

DELETE vw.*
FROM videoswatched vw
INNER JOIN users u ON vw.userID = u.userID
WHERE u.companyID = 1000;

It has already been answered in the comments: You need to remove the table alias, it is not allowed in MySQL's DELETE statement syntax (and there is no need for it, either).

Edit: BTW, Try this (coding from memory, might be wrong):

DELETE vw.*
FROM videoswatched vw
INNER JOIN users u ON vw.userID = u.userID
WHERE u.companyID = 1000;

回复收藏 0 原文

忆沫 2024-09-10 07:04:04

MySQL 手册说

注意
如果您为表声明别名，
引用时必须使用别名
到表：
从测试 AS t1、test2 中删除 t1，其中
...

所以，你可以尝试

DELETE vm2 FROM videoswatched vw2 WHERE vw2.userID IN (
SELECT vw.userID FROM videoswatched vw
JOIN users u
ON vw.userID=u.userID
WHERE u.companyID=1000
GROUP BY userID
)

但是正如评论中提到的，你可以简单地取消别名

DELETE FROM videoswatched WHERE userID IN (
SELECT vw.userID FROM videoswatched vw
JOIN users u
ON vw.userID=u.userID
WHERE u.companyID=1000
GROUP BY userID
)

The MySQL manual says

Note
If you declare an alias for a table,
you must use the alias when referring
to the table:
DELETE t1 FROM test AS t1, test2 WHERE
...

So, you could try

DELETE vm2 FROM videoswatched vw2 WHERE vw2.userID IN (
SELECT vw.userID FROM videoswatched vw
JOIN users u
ON vw.userID=u.userID
WHERE u.companyID=1000
GROUP BY userID
)

But as mentioned in the comments you can simply do away with the alias

DELETE FROM videoswatched WHERE userID IN (
SELECT vw.userID FROM videoswatched vw
JOIN users u
ON vw.userID=u.userID
WHERE u.companyID=1000
GROUP BY userID
)

回复收藏 0 原文

~没有更多了~