3

Say that I want to run the following SQLite command to delete all rows in a particular table where the Name column is NULL:

DELETE FROM myTable WHERE Name IS NULL;

Now let's say that I've got 20 tables, and they aren't named in any pattern. I want to delete all rows where the Name column is NULL from every table in the database. Is there a statement I can use to do this in one go? Or will I have to run one statement to get the table names, then take the output and run 20 or so commands using each table name?

Thunderforge
  • 19,637
  • 18
  • 83
  • 130

2 Answers2

6

Here's my first crack at it from the sqlite3 console :

.output tmp

SELECT "DELETE FROM " || sqlite_master.name || 
" WHERE name is NULL;" FROM sqlite_master 
WHERE type = "table" AND sqlite_master.name NOT LIKE 'sqlite_%';

.read tmp

This generates the delete statements for each table, outputs it to a file and then reads and runs that file.

(My comment was about the syntax in another answer, you can only delete from one table per delete statement.)

Shawn Balestracci
  • 7,380
  • 1
  • 34
  • 52
  • Is the name variable in between the concatenations the same as the name variable in the `"where name is null"` statement? – Thunderforge Apr 23 '13 at 06:15
  • " where name is null " represents the name field in your tables. In the other two places it is the name of the table from the sqlite_master table. – Shawn Balestracci Apr 23 '13 at 06:17
  • Very useful sqlite commands, also to RENAME a column in all tables with spaced long names using **triple quotes:** SELECT "ALTER TABLE """|| sqlite_master.name ||""" RENAME COLUMN old_name TO new_name;" FROM sqlite_master WHERE type = "table" AND sqlite_master.name NOT LIKE 'sqlite_%'; – social Jul 07 '22 at 02:06
0

If you have mandatory column 'Name' in every table, then

  DELETE FROM myTable1, myTable2, myTable3 WHERE Name IS NULL;
Lalit Sharma
  • 1,142
  • 1
  • 15
  • 32
  • The tables aren't named in any particular pattern, so I couldn't do `myTable1, myTable2, myTable3` and I might not be able to hardcode the names in anyway. Let's say they have the same column names though for simplicity. – Thunderforge Apr 23 '13 at 04:53
  • This is not valid SQLite, you can only delete from one table at a time. – Shawn Balestracci Apr 23 '13 at 05:54