MySQL Delete with multiple criteria

I have a table that has some duplicate data in it that I need to delete. I am struggling how to do this with multiple criteria. For example:

id	orderid	txtype
121264	6885	Invoice
121265	6885	Invoice
121266	6885	Invoice
121269	6885	Invoice
121274	6885	Invoice
921264	6885	Payment
921265	6885	Payment
921266	6885	Payment
921269	6885	Payment
921274	6885	Payment

Open in new window


In the example above I want to delete  just the duplicate invoice rows based on the orderid and the txtype keeping the lowest id value. Note that there are other columns that may or may not be duplicates however orderid and txtype are the ones that I need to use for the criteria. I only want to delete the duplicates where the txtype is Invoice.

The result should look like this:
id	orderid	txtype
121264	6885	Invoice
921264	6885	Payment
921265	6885	Payment
921266	6885	Payment
921269	6885	Payment
921274	6885	Payment

Open in new window


So far my results delete everything and not just the duplicate invoice rows.
ckelsoeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Terry WoodsIT GuruCommented:
Something like this? Please backup your data before you try it! I haven't tested it myself.
delete from invoice a
join invoice b on b.orderid = a.orderid
and b.txtype = a.txtype
where b.id>a.id

Open in new window

0
ckelsoeAuthor Commented:
I think that will delete all duplicates no matter what the txtype is. I just need it to delete the duplicates where the txtype is = 'Invoice'
0
Terry WoodsIT GuruCommented:
Sorry, my test of that failed. This slightly modified version seemed to work though:

delete a from invoice as a
inner join invoice as b on b.orderid = a.orderid
and b.txtype = a.txtype
where b.id>a.id

You might like to add an index on (txtype, orderid) before you do it, or it could take a long time to run. I'm assuming you'll have one on id already (but you'll want that too if you don't).
0
Terry WoodsIT GuruCommented:
Just saw your most recent comment. It's easy to add an extra filter:

delete a from invoice as a
inner join invoice as b on b.orderid = a.orderid
and b.txtype = a.txtype
where b.id>a.id
and txtype='invoice'

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ckelsoeAuthor Commented:
That did it! I was trying using having in the statement. This did exactly what I needed. Thank You
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.