Android SQL Insert, Add, Delete etc. to Multiple Tables Within One Statement?

Question

Is it possible to example use:

String sql = "DELETE field FROM table1, table2 WHERE field='x', field='y'";

To delete multiple fields from multiple tables? I ask because I am using creating a clean up method that will go through and delete all references to an object in all of my database tables when I delete that object and it seems easier to add everything into one sql statement as opposed to building a separate sql for each table and then executing each one individually. If one statement isn't possible is there another solutions? Such as building an sql array perhaps and then executing all with one command?

Don't know which DBMS that is on Android, but `DELETE field FROM` is an invalid (standard) SQL statement. `DELETE` does not work on column level, it works on row level. — , Jul 30 '11 at 23:03
@a_horse_with_no_name: The standard is, AFAIK, SQLite for both Android and iOS. — mu is too short, Jul 30 '11 at 23:44

score 0 · Answer 1 · edited May 23 '17 at 10:34

0

Are you trying to delete the field or the entire row? If row then yes you can do the delete in all the tables, if they have Foreign Keys.

Take a look at this post MySQL delete row from multiple tables

That should give you the clue and good luck...

edited May 23 '17 at 10:34

Community

1
1

answered Apr 18 '12 at 17:36

Vincy

1,078
1
9
16

score 0 · Accepted Answer · answered Jul 30 '11 at 22:57

0

You can't do that. You need to do multiple delete statements, one per table.

answered Jul 30 '11 at 22:57

aromero

25,681
6
57
79

I kinda figured that much, but just thought i'd check. Thanks. – ryandlf Jul 30 '11 at 22:58

score 0 · Answer 3 · answered Jul 30 '11 at 23:01

Many databases allow for several statements to be in one string separated by ; and then executed in one command.

e.g.

String sql = 
         "DELETE FROM table1 WHERE field='x' and field='y'; 
          DELETE FROM table2 WHERE field='x' and field='y';";

 Command.Commandtext = sql;
 Command.Execute();

However I do not have specific knowledge about Android so if I'm wrong let me know.

Android SQL Insert, Add, Delete etc. to Multiple Tables Within One Statement?

3 Answers3

Linked