Delete a Select Inner Join Query result-CodePudding

SELECT yturl from ytpointadder a
JOIN watched b on b.watchedyt = a.yturl AND b.ip = a.ip
Group by a.ip;

I want to delete the result of the query for exp this query result is :

yturl	ip	id
eY5WRONGXDI	197.XX.XX.XXX	1

Though i want to delete it from ytpointadder. I tried :

DELETE from ytpointadder a
JOIN watched b on b.watchedyt = a.yturl AND b.ip = a.ip
Group by a.ip;

CodePudding user response：

You can't specify same target table for update (delete) in FROM clause. So you have to use another outer query.

Try:

DELETE FROM ytpointadder
WHERE yturl IN  ( SELECT t.yturl FROM (SELECT a.yturl, a.ip from ytpointadder a
                                     JOIN watched b on b.watchedyt = a.yturl AND b.ip = a.ip
                                     Group by a.ip 
                                     ) as t 
                 ) ;

CodePudding user response：

In a partial query, it should return one column You may need to use parentheses

try this

DELETE FROM ytpointadder
WHERE yturl IN
( 
SELECT a.yturl from  ytpointadder a
JOIN watched b on b.watchedyt = a.yturl AND b.ip = a.ip
Group by a.ip)