Reply to: MySQL Question

[Sysman]

I know there's commercial Data Masking tools you can get to anonymize customer data (e.g. for dev/test databases) - Informatica have one - but I can't instantly find any free ones that seem worth looking at. Good project for someone to code an open source one.

Data anonymisation for testing purposes was a project a former client had a lot of difficulty with. And yes, this was a bank.

I had a play around with a few freebies several months ago, but their data was horribly Yank. Hands up anyone who has ever met a Zacchary.

[Freamon]

No real problem to solve just setting up a duplicate system for a new dev to work on but wanted to strip out all the real customer data.

Thanks to the excellent pointers I got the job done with minimum of fuss and nothing broken.

I know there's commercial Data Masking tools you can get to anonymize customer data (e.g. for dev/test databases) - Informatica have one - but I can't instantly find any free ones that seem worth looking at. Good project for someone to code an open source one.

[Cliphead]

I suppose it's worth asking why you want to do this?

There's probably a better way to solve the underlying problem that you're trying to solve.

No real problem to solve just setting up a duplicate system for a new dev to work on but wanted to strip out all the real customer data.

Thanks to the excellent pointers I got the job done with minimum of fuss and nothing broken.

[Sysman]

I've sometimes thought it would be useful to create a storage engine that returns result sets using a randomly-chosen way of ordering: each time you run a query without an ORDER BY clause it just picks one of order-of-insertion, reverse-order-of-insertion, every-other-record-by-order-of-insertion-followed-by-the-rest-in-reverse-order-of-insertion-permuted-according-to-the-fibonacci-series... and of course randomly-ordered, along with lots of others. It would be a useful training tool, reminding people that if they want things ordered a particular way, they need to say so. I've also thought of doing the same thing with the "for-each" construct in XSLT - too many people think it operates like a loop, but of course there's no such thing as a loop (which implies a temporal sequence of events) in declarative languages like SQL or XSLT. It all happens outside time

Now you're taking me back to a database I learnt many years ago, when hierarchical rather than relational databases were the norm. It constructed a (hopefully unique*) hash of the primary key and used that to determine the location of the data. This was done for performance and avoided ploughing through index levels on data lookups. At this point my memory gets dodgy, but I do recall sorts on a mainframe being blazingly fast.

* logic was there to cope with non-unique hashes, but of course these would incur a performance penalty with extra I/O.

[NickFitz]

I have several tables containing over 20k rows. Is there a quick way to delete all but the first 20 rows in each of the tables?

I agree with:

I suppose it's worth asking why you want to do this?

There's probably a better way to solve the underlying problem that you're trying to solve.

if only because tables in relational databases are inherently unordered (they're sets), and therefore the concept of "the first 20 rows" doesn't apply: it's like referring to "the first twenty places in Berkshire", in that you need to define what criteria are used to determine where comes before where once all the "places in Berkshire" have been identified.

When you do "SELECT * FROM doobry" the database might well return the records in order of insertion, but that's merely an artefact of the implementation, and must not be relied on. It's entirely possible that the implementation (in MySQL's case, the implementation of the specific storage engine used for that table) could choose to rearrange the way it's stored the contents of the table (maybe to optimise disk access once the table grows beyond a certain size, or some such reason), and then the same query could return the results in a different order. This is completely legitimate behaviour, and a classic example of why one should program to the abstraction's defined behaviour rather than relying on the implementation's circumstantial behaviour leaking to a higher level.

I've sometimes thought it would be useful to create a storage engine that returns result sets using a randomly-chosen way of ordering: each time you run a query without an ORDER BY clause it just picks one of order-of-insertion, reverse-order-of-insertion, every-other-record-by-order-of-insertion-followed-by-the-rest-in-reverse-order-of-insertion-permuted-according-to-the-fibonacci-series... and of course randomly-ordered, along with lots of others. It would be a useful training tool, reminding people that if they want things ordered a particular way, they need to say so. I've also thought of doing the same thing with the "for-each" construct in XSLT - too many people think it operates like a loop, but of course there's no such thing as a loop (which implies a temporal sequence of events) in declarative languages like SQL or XSLT. It all happens outside time

[Freamon]

I suppose it's worth asking why you want to do this?

There's probably a better way to solve the underlying problem that you're trying to solve.

[TheFaQQer]

How many tables? Do the tables have an ID column? As other posters have said you can simply delete where ID > 20 so

DELETE from TABLE where ID > 20;

That only works if ID is sequential with no gaps though.

[wobbegong]

Load the MySQL ODBC driver. Within MS-Access, link to the tables, open them and highlight the rows you don't want, hit 'Del'.

[yorkshireman]

I have several tables containing over 20k rows. Is there a quick way to delete all but the first 20 rows in each of the tables?

Use phpmyadmin or Mysql Workbench and select/delete with your mouse.

[2BIT]

I'm clueless when it comes to sql

dunno mysql but in Oracle you could use the rownum, mysql prob has something similar,

or perhaps SELECT * FROM TABLE WHERE NOT IN (SELECT TOP 20 * FROM TABLE)

[administrator]

How many tables? Do the tables have an ID column? As other posters have said you can simply delete where ID > 20 so

DELETE from TABLE where ID > 20;

[lilelvis2000]

Good stuff.

[eek]

I have several tables containing over 20k rows. Is there a quick way to delete all but the first 20 rows in each of the tables?

well this will give you the first 20 rows

SELECT tableid FROM {table} ORDER BY {row} asc LIMIT 20

so something like will work
delete from table where tableid not in (
SELECT tableid FROM {table} ORDER BY {row} asc LIMIT 20)

[Cliphead]

Can you not use the order by limit 20 in the delete statement ?

I'm clueless when it comes to sql

[FiveTimes]

Can you not use the order by limit 20 in the delete statement ?