How do you DELETE rows in a mysql table that have a field IN the results of another query?

Question

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. thanks!

Answer 1

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;

Answer 2

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
)