-2

For a project i need to to delete all data from all tables in a database,

I tried

DELETE FROM table1,table2,table3,...;

But it doesnt work. Any advice ? Thank

julien1h
  • 85
  • 7
  • Any reason why you can't just use separate delete statements for each table? – Tim Biegeleisen Dec 08 '22 at 14:39
  • That's not how DELETE statements work. Also, you should be looking at TRUNCATE, not DELETE, if you just want to clear out tables. – Andy Lester Dec 08 '22 at 14:41
  • @TimBiegeleisen it's the way im doing it right now but im curious how to optimize that – julien1h Dec 08 '22 at 14:50
  • @AndyLester you're right, i'd rather using TRUNCATE. didnt think about it, im a beginner edit : i checked, there is not TRUNCATE statement in sqlite – julien1h Dec 08 '22 at 14:52

2 Answers2

1

I would like to refer You to this related post

How do I use cascade delete with SQL Server?

as You will find there several possible solutions.

When using SQL it means that Your data is relations, which means most of the records are somehow related in the different tables and this relation is expressed with foreign keys. However when attempting to delete data which is id is related with data in another table a cascade deletion should be implemented, the other way around it is add additional boolean column named isDeleted(as example ofcourse) and just alter specific rows to true in this specific column and then filter by preferences. Hopefully I have managed somehow to provide with alternative and/or possible solution to Your problem.

Leaving also this link which gives some examples on cascade deletion and guide on how to implement it. ->

https://www.sqlshack.com/delete-cascade-and-update-cascade-in-sql-server-foreign-key/

P.S. also if You want just to DELETE all the data You can either use TRUNCATE TABLE or DROP DATABASE query. With the latest option You will have to recreate the database once more.

92Infinitus92
  • 138
  • 5
  • He is asking for Sqlite in specific and from what I can tell, he wants to delete all data from all tables. The quickest way would be to drop the database, but in sqlite there is no way of doing that except deleting the file. Check my answer. – jpcapdevila Dec 08 '22 at 14:47
1

Because you want to delete all databases from all tables, you are essentially deleting the database.

The sqlite way of doing this is to just delete the database file.

In contrast to postgres or mongodb or most databases, sqlite doesn't have a server so no need to call DROP DATABASE.

As soon as the file is gone and you open the database from your application you will be in a blank state again.

jpcapdevila
  • 207
  • 3
  • 8
  • even if i want to keep my tables ? i mean, if i call a ```DROP DATABASE``` all my tables will be delete right ? (even if there is no way to call a ```DROP DATABASE``` in sqlite but this is the idea) – julien1h Dec 08 '22 at 14:48
  • Yes, you would loose your tables. For me it is usually not a problem as I run my migrations that would essentially recreate the tables when my code starts. That could be your case if you are using something like RoR or similar, but if you are interacting with sqlite directly I can see that being a hassle. – jpcapdevila Dec 08 '22 at 14:52
  • it's a problem for me because im using this database in a cpp code, and i wanted a "CLEAR" function for that database. Do you think it's better to delete the file and recreate the database in the "CLEAR" function ? (with the creation of all tables of course) – julien1h Dec 08 '22 at 15:28