Avatar of Johnny Newbee
Johnny NewbeeFlag for United States of America asked on

Mysql delete records

Hi,

I have about twenty tables in Mysql 5.0 db for a application. For demo purpose, I need to delete the most records except three rows left, which should have linked records among the tables. What should be the correct procedure to generate proper linkage and a complete delete sql that only leave three rows left?

Thank you.
PHPMySQL ServerSQL

Avatar of undefined
Last Comment
Johnny Newbee

8/22/2022 - Mon
- -

delete from table1 where id in (select id from ... your criteria ... limit 3, 100000)
ASKER
Johnny Newbee

Alex,

Thanks for the quick response. But please read my question again clearly. I need a sql to delete most records in all tables once. And the 3 records left should be linked among the tables.

Mark Wills

The danger there is not so much removing records, but making sure the records remaining still have referential integrity across the database so the remaining demo data does make sense and does display...

So, you will need to select a few customers, products, or people, (or whatever the dtabase represents), and retain sufficient data to best represent the capabilities that you need for your demo database (possibly including some history).

You would then remove any data elements that did not contribute or belong to the chosen "master" / "header" records.

for headers / masters it would be something like:

delete from hdr_table where key not in ('KeepThis','AndThis','AndthisOneAswell')

then for details / transaction data :

delete from dtl_table where not exists (select key from hdr_table)

does that make sense ?
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Mark Wills

sorry, that delete should be : delete from dtl_table where not exists (select * from hdr_table where dtl_table.key = hdr_table.key)
ASKER
Johnny Newbee

Hi Mark,

I've seen you are on the right track. Here are my follow up to your response:

1) How do I make sure referential integrity is there? If not, is some kind of tool I could use with Mysql to make that happen automatically or semi-auto?

2) delete from dtl_table where not exists (select * from hdr_table where dtl_table.key = hdr_table.key) it looks like I am still deleting one table at one time. No way I could do it once for all tables?


Thank you.



Mark Wills

I am not aware of any off the top of my head. You would need to publish a comprehension data dictionary and data model for it to work properly. and by the time you have done that...

If you have referential integrity with foreign keys etc throughout your system, then cascading deletes are possible - depending on how you set up foreign keys.

otherwise, it is a search of the schema, get the keys, and start deleting...

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Johnny Newbee

Thanks for the referential guide line. But the sql has to delete one table by one time?
Mark Wills

Yes.
ASKER CERTIFIED SOLUTION
Mark Wills

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Johnny Newbee

Too bad to hear that.

FYI, here is the db tool I just found new on the street:

http://dev.mysql.com/workbench/

Hope you could find it useful too.
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
Johnny Newbee

It could be a complete solution if I don't have to manually delete records from 20+ tables; or if there is a such sql in the future :)