Оператор выбора MySql работает, но не удаляет

Question

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 );

Возвращает 34 15 Buying 1500 1428101231 1 2014-09-29 10:09:55

но:

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 );

Возвращает: [Err] 1064 - You have an error in your SQL syntax; проверьте руководство, которое соответствует вашей версии сервера MySQL для правильного синтаксиса для использования рядом AS ETMain WHERE ETMain.UserID = @UserID_In AND ETMain.ID = ( SELECT MAX(ID) в строке 1

Answer 1

Попробуйте этот запрос на удаление (без псевдонима для "таблицы удаления"):

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 );

Answer 2

Я получил следующие упрощения. В какой-то момент вы, вероятно, имели в виду что-то другое.

Оригинал, отформатированный:

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 );

Первый подзапрос И второй подзапрос сводятся к первому. Может быть, что-то вроде OR-ELSE подразумевалось?

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 );

Теперь основной псевдоним не нужен (если используется @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 );

Затем псевдоним подмножества и удаление двойного условия по идентификатору пользователя:

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 );

Здесь подзапрос легко проверяется.