Error my my SQL query while DELETE the Similar record

Question

I have tried this query but i am having this error code 1093 this query

delete  
from flow 
where flow.FlowID  in (SELECT flow.FlowID 
                       From flow 
                       WHERE ExpirationDate IS NOT NULL  AND `Username`="XYZ@gmail.com" 
                       GROUP BY Username, FlowName, EffectiveDate 
                       Having COUNT(*) >1 );

1093 error : Can't specify target table in clause.

Also tell us what you're trying to do. Make it easy to assist: [mcve]. — jarlh, Oct 24 '19 at 07:28
I can't figure out why you are using `GROUP BY` in the subquery (and the syntax is almost certainly wrong). Can you add sample data to your question which helps to explain things? — Tim Biegeleisen, Oct 24 '19 at 07:32
When using the same table twice, use aliases and specify columns with those aliases — Serg, Oct 24 '19 at 07:35
@Shadow That duplicate alone still won't fix the OP, most likely. — Tim Biegeleisen, Oct 24 '19 at 07:38

score 0 · Answer 1 · answered Oct 24 '19 at 07:34

Here is my suggestion at a corrected version:

DELETE
FROM flow
WHERE FlowID IN (SELECT FlowID FROM (SELECT FlowID FROM flow
                                     WHERE ExpirationDate IS NOT NULL AND
                                           Username = 'XYZ@gmail.com') t);

The deletion logic here is to delete any record whose FlowID is also associated with a record having a non NULL expiration date and user XYZ@gmail.com. The trick I used to get around the 1093 MySQL error is to wrap the subquery in another subquery. This works by forcing MySQL to materialize that subquery before the update, so it no longer conflicts.

Error my my SQL query while DELETE the Similar record

1 Answers1