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.

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;