CODEWITHSUNDEEP
phpmysqlsql-delete
Shafizadeh
Shafizadeh

Reputation: 10380

How to check existing row before deleting row in another table?

I have two tables:

// table1                              // table2
+----+------+---------+                +----+------+
| id | col1 | user_id |                | id | col2 |
+----+------+---------+                +----+------+
| 1  | a    | 100001  |                | 1  | a    |
| 2  | b    | 100002  |                | 2  | b    |
| 3  | c    | 100003  |                | 3  | c    |
+----+------+---------+                | 4  | a    |
                                       | 5  | a    |
                                       | 6  | c    |
                                       +----+------+

Also I have a variable named $user_id. Now I want to delete all row in the table2 where col2='a', But I need to check table1.user_id = $user_id (in this case $user_id = '100001' ) in the table1 before deleting.

How can I write a correct syntax query with this concept:

IF table1.user_id = $user_id where table1.col1 = 'a' then
   delete from table2 where col2 = 'a'

I can do that using PHP and MySQL using two separated query. But I want to do that with one query, is it possible?

Upvotes: 2

Views: 141

Answers (1)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44874

You can use JOIN to do the delete operation something as 

delete t2 from table2 t2
join table1 t1 on t2.col2 = t1.col1
where 
t2.col2 = 'a'
and t1.user_id = '100001'

Upvotes: 2

Related Questions