-1

I am trying to combine 2 sql query 1: SELECT and 2: DELETE. Any inputs on how i can implement that?

The output from SELECT is given into WHERE clause in DELETE. Please refer code snippet.

select id from table1 where id_hash IN( select id_hash from table1 where name='stack' group by id_hash having count(*)>1

The output(id) of above SELECT is passed to DELETE

delete from table1 where id in (<id from SELECT>)

Thanks in advance.

nits
  • 73
  • 2
  • 8
  • @KenWhite That will get the "can't specify target table for update" query. – Barmar Jul 12 '19 at 23:55
  • 1
    Why do you need to combine the queries? Can't you just copy the `WHERE` clause? `DELETE FROM table1 WHERE name = 'stack'` – Barmar Jul 12 '19 at 23:56
  • 1
    @Barmar: I hate MySQL. :-) – Ken White Jul 13 '19 at 00:13
  • @Barmar Thanks for the comments. I edited my code where in reality its a complex subquery. The reason to combine is an early effort to automate(Reduce steps of execution) I am a newbie to this. – nits Jul 13 '19 at 00:19

1 Answers1

0

Here's a link to someone who has asked a similar question:

How to write a SQL DELETE statement with a SELECT statement in the WHERE clause?

Here is an example from that thread. Credit to user @AlexW

DELETE FROM tableA
WHERE ROWID IN 
  ( SELECT q.ROWID
    FROM tableA q
    INNER JOIN tableB u on (u.qlabel = q.entityrole AND u.fieldnum = q.fieldnum) 
    WHERE (LENGTH(q.memotext) NOT IN (8,9,10) OR q.memotext NOT LIKE '%/%/%')
    AND (u.FldFormat = 'Date'));
Oliver Musovski
  • 46
  • 6
  • 1
    If you've found an answer already here, you should be flagging the original question as a duplicate instead of posting an answer from it. – Ken White Jul 13 '19 at 00:12