-1

What's the query to delete all data from a database? The reason for this is there are tons of data that will be processed and stored on that database and every month it should be cleaned in order to avoid having tons of data as the previous data will be irrelevant on the next cycle.

PS, is there such a query as:

Dim sqlQuery As String = "DELETE ALL * FROM employee_table"

I've seen this question but I don't think it's the answer to what I'm looking to do.

Community
  • 1
  • 1
user3678904
  • 33
  • 2
  • 9
  • I have closed your previous question as a duplicate. It seems pretty clear what you need to do. Send a MySqlCommand to your database with the `TRUNCATE TABLE tablename`. What is not clear in that? – Steve May 30 '14 at 10:22
  • 2
    No steve, it is not clear to me because I am not as smart as you when it comes to programming. What I got from that is I thought that there are other conditions after the TRUNCATE TABLE tablename part. At least T.J Crowder answered properly, his answer is helpful and not rude. – user3678904 May 30 '14 at 10:34

2 Answers2

1

PS, is there such a query as:

Dim sqlQuery As String = "DELETE ALL * FROM employee_table"

Yes:

Dim sqlQuery As String = "DELETE FROM employee_table"

That records the delete (in replication logs, etc.) like any other data modification. You might also see:

Dim sqlQuery As String = "TRUNCATE TABLE employee_table"

That does not record the delete (in replication logs, etc.).

See the MySQL documentation for the difference between an unqualified DELETE (a DELETE with no WHERE) and a TRUNCATE, but basically: If you use replication, binlogs, etc. and need to have an accurate reflection of the actons that occurred, use DELETE; if you don't need that, you can use TRUNCATE, which operates more quickly (since it doesn't have to do the logging and such).

Query for deleting ALL data from a database...

The above is, of course, just for one table. To do multiple tables, just use multiple DELETE or TRUNCATE statements.

Community
  • 1
  • 1
T.J. Crowder
  • 1,031,962
  • 187
  • 1,923
  • 1,875
0

Yo can enumerate all the db tables and then delete its content.

To enumerate the tables you can do "SHOW TABLES;", and then iterate throug the results and truncate those tables "TRUNCATE TABLE (tablename);"

Beware of relationships, if a row is referenced by a foreign key you will not be allowed to delete it, so you must delete the info hierarchically.

Gusman
  • 14,905
  • 2
  • 34
  • 50