phpmyadmin Question posted in
The official documentation can be found here.

I can't run this query, but when i simulate it in phpmyadmin, it returns the rows

I’m going to delete some rows in one of my table called sy_user. I want to delete some of the rows that is not matched in the other table, which is the tb_master_pegawai.

How do i solve this query to do the row delete using the subquery as the WHERE condition?

I used subquery as the WHERE condition in the DELETE query.
When i run it in phpmyadmin it says

You can’t specify target table ‘sy_user’ for update in FROM clause

But when i simulate it, it has return rows.

DELETE FROM sy_user 
       WHERE user_id NOT IN 
                (SELECT tbpeg.ID 
                 FROM tb_master_pegawai AS tbpeg 
                 JOIN sy_user AS tbuser ON tbpeg.ID=tbuser.user_id)

I expect the result is i have deleted the rows that are not matched with the same data in the other table.

Tags:

2

Answers


  1. 0

    You could simplify your request using only a LEFT JOIN:

    DELETE sy_user FROM sy_user
LEFT JOIN tb_master_pegawai ON sy_user.user_id = tb_master_pegawai.ID
WHERE tb_master_pegawai.ID IS NULL
    Login or Signup to reply.
  2. 0

    You can’t reference your the table you are deleting from in a subquery of DELETE/UPDATE queries. Instead, use a LEFT JOIN and check where the joined table is NULL (meaning it had no rows that matched the ON condition).

    Try to run delete queries within a transaction when testing it, that way you can rollback if the query deleted more rows than intended. 

    DELETE u
FROM sy_user AS u
LEFT JOIN tb_master_pegawai AS tp
  ON tp.ID = u.user_id
WHERE tp.ID IS NULL
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search