Delete duplicate rows with different statuses

Question

help with deleting duplicate order rows for orders processed multiple times, but only if the duplicate is caused by submit_status 0 or 1. Basically, orders that failed on first attempt but got processed manually. Also, if a duplicate order has the same submit_status, then keep it.

sample data attached. Thank you!

Answer 1

Check this statement:

delete t1
from MyTable t1
inner join MyTable t2 on t1.orderId = t2.orderId and t1.submit_Status <> t2.submit_Status