CODEWITHSUNDEEP
mysqlsqljoin
user2886519
user2886519

Reputation: 121

Remove two results of two different tables with a common field

Following the first table (first_table):

id 
data
status

Following the second table (second_table):

id
status
final_result

I would like to remove all of the results from the second table where final_result is equal to 3, and in the first table where status is equal to the status of results previously deleted. Following my query:

DELETE FROM second_table WHERE final_result = '3'

I do not know how to continue, can you help me?

Upvotes: 0

Views: 47

Answers (2)

Linger
Linger

Reputation: 15058

DELETE FT, ST
FROM first_table AS FT
JOIN second_table AS ST
ON FT.status = ST.status 
AND ST.final_result = '3'

Upvotes: 1

Jonysuise
Jonysuise

Reputation: 1830

you have to delete first the rows of your first table.

DELETE FROM first_table where status in (select distinct status from second_table where final_result='3')
DELETE FROM second_table WHERE final_result = '3'

Upvotes: 1

Related Questions