CODEWITHSUNDEEP
mysqlselectwhere-clausesql-deletesubquery
Henzard Kruger
Henzard Kruger

Reputation: 62

MySql select statement works but not delete

SET @UserID_In = 1;
Select * FROM EarnedTransaction AS ETMain WHERE ETMain.UserID = @UserID_In
  AND ETMain.ID = ( SELECT MAX(ID) FROM EarnedTransaction AS ETSub WHERE ETSub.UserID = 
    ETMain.UserID AND ETSub.TransactionType = 'Buying' AND ETSub.CompleteDate IS NULL )
  AND ETMain.ID = ( SELECT MAX(ID) FROM EarnedTransaction AS ETSub WHERE ETSub.UserID =             
    ETMain.UserID );

Returns 34 15 Buying 1500 1428101231 1 2014-09-29 10:09:55

but:

SET @UserID_In = 1;
Delete FROM EarnedTransaction AS ETMain WHERE ETMain.UserID = @UserID_In
   AND ETMain.ID = ( SELECT MAX(ID) FROM EarnedTransaction AS ETSub WHERE ETSub.UserID = 
       ETMain.UserID AND ETSub.TransactionType = 'Buying' AND ETSub.CompleteDate IS NULL )
   AND ETMain.ID = ( SELECT MAX(ID) FROM EarnedTransaction AS ETSub WHERE ETSub.UserID =                   
       ETMain.UserID );

Returns : [Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near AS ETMain WHERE ETMain.UserID = @UserID_In AND ETMain.ID = ( SELECT MAX(ID) at line 1

Upvotes: 0

Views: 627

Answers (2)

Joop Eggen
Joop Eggen

Reputation: 109547

I got the following simplifications. At some point you probably meant something different.

Original, formatted:

SET @UserID_In = 1;
DELETE FROM EarnedTransaction AS ETMain WHERE ETMain.UserID = @UserID_In
AND ETMain.ID = (
     SELECT MAX(ID)
     FROM EarnedTransaction AS ETSub
     WHERE ETSub.UserID = ETMain.UserID
     AND ETSub.TransactionType = 'Buying'
     AND ETSub.CompleteDate IS NULL )
AND ETMain.ID = (
     SELECT MAX(ID)
     FROM EarnedTransaction AS ETSub
     WHERE ETSub.UserID = ETMain.UserID );

The first subquery AND the second subquery reduce to the first. Maybe something like an OR-ELSE was meant?

SET @UserID_In = 1;
DELETE FROM EarnedTransaction AS ETMain WHERE ETMain.UserID = @UserID_In
AND ETMain.ID = (
     SELECT MAX(ID)
     FROM EarnedTransaction AS ETSub
     WHERE ETSub.UserID = @UserID_In
     AND ETSub.TransactionType = 'Buying'
     AND ETSub.CompleteDate IS NULL );

Now the main alias is not needed (if using the @UserID_In)

SET @UserID_In = 1;
DELETE FROM EarnedTransaction WHERE UserID = @UserID_In
AND ID = (
     SELECT MAX(ID)
     FROM EarnedTransaction AS ETSub
     WHERE ETSub.UserID = @UserID_In
     AND ETSub.TransactionType = 'Buying'
     AND ETSub.CompleteDate IS NULL );

Then the subset alias, and removal of double condition on UserID:

SET @UserID_In = 1;
DELETE FROM EarnedTransaction WHERE ID = (
     SELECT MAX(ID)
     FROM EarnedTransaction
     WHERE UserID = @UserID_In
     AND TransactionType = 'Buying'
     AND CompleteDate IS NULL );

Here the subquery is easily checked.

Upvotes: 1

Edi G.
Edi G.

Reputation: 2422

Try this delete-query (without alias for the "delete-table"):

SET @UserID_In = 1;
DELETE FROM EarnedTransaction 
      WHERE UserID = @UserID_In
        AND ID = (SELECT MAX(ID) 
                    FROM EarnedTransaction AS ETSub 
                   WHERE ETSub.UserID = UserID 
                     AND ETSub.TransactionType = 'Buying' 
                     AND ETSub.CompleteDate IS NULL)
        AND ID = (SELECT MAX(ID) 
                    FROM EarnedTransaction AS ETSub 
                   WHERE ETSub.UserID = UserID );

Upvotes: 2

Related Questions