SQLSTATE[HY000]: General error The SQL being executed in Yii2

Question

Reference from Specified Twice Table for 'DELETE' in MariaDB

After I implemented the query from that reference into the code, I made an error in yii2

Query :

public function actionDeleteduplicate($date){
        Yii::$app->db->createCommand("DELETE t1
                                    FROM HrAttLogsFormatted t1
                                    INNER JOIN
                                    (
                                        SELECT FingerId, MIN(CreatedDate) AS MinCreatedDate
                                        FROM HrAttLogsFormatted 
                                        WHERE DateIn = '".$date."' AND Late != ''
                                        GROUP BY FingerId
                                        HAVING COUNT(FingerId) > 1
                                    ) t2
                                        ON t1.FingerId = t2.FingerId AND t1.CreatedDate = t2.MinCreatedDate
        ")->queryAll();

        $this->redirect(['index']);
    }

Result :

SQLSTATE[HY000]: General error The SQL being executed was: DELETE t1 FROM HrAttLogsFormatted t1 INNER JOIN ( SELECT FingerId, MIN(CreatedDate) AS MinCreatedDate FROM HrAttLogsFormatted WHERE DateIn = '2019-05-03' AND Late != '' GROUP BY FingerId HAVING COUNT(FingerId) > 1 ) t2 ON t1.FingerId = t2.FingerId AND t1.CreatedDate = t2.MinCreatedDate

Does anyone know what is wrong with the code?

You should probably use `execute()` instead of `queryAll()` for delete queries. — rob006, May 07 '19 at 07:15
@rob006 Thank you, I forgot that there has a `DELETE` function not only `SELECT` where the function is executed by `execute()` — Aldan, May 09 '19 at 01:55

score 2 · Accepted Answer · edited May 07 '19 at 07:18

The issue could be related to the fact you have the same table in delete and in subquery. Try force the db engine to use a temp table using a nested query:

DELETE t1
FROM HrAttLogsFormatted t1
INNER JOIN ( 
  select FingerId, MinCreatedDate
  FROM   (
    SELECT FingerId, MIN(CreatedDate) AS MinCreatedDate
    FROM HrAttLogsFormatted 
    WHERE DateIn = '".$date."' AND Late != ''
    GROUP BY FingerId
    HAVING COUNT(FingerId) > 1
  ) t3 
) t2  ON t1.FingerId = t2.FingerId AND t1.CreatedDate = t2.MinCreatedDate

BTW: You should not use a PHP var in SQL directly, you should take a look at binding params.

SQLSTATE[HY000]: General error The SQL being executed in Yii2

1 Answers1