Delete data from multiple tables

[Alain Yoshida]

I want to delete the data from all the tables in this database which having device_id="A1"
How to do it. 2 issues heres
1) There are about 40 tables in this database and don't know which table contained the field device_id .
2) We have several this kind of deviced_id need to be deleted, e.g. device_id="A1", or device _id="A2", "A3". can sql read this info from a table and process? thanks

[Hein van den Heuvel]

Martin,

This is really DATABASE questing entirely independent of the OS (hpux). As such this question is better asked (for your own sake, and to cut down noise here) in a database specific forum.

What database are you refering to?
If it is oracle, then here are some queries to get you going.

SQL> select table_name from dict where table_name like '%TAB%COL%';
---> USER_TAB_COLS

SQL> describe USER_TAB_COLS
---> TABLE_NAME, COLUMN_NAME

SQL> select TABLE_NAME, COLUMN_NAME from USER_TAB_COLS where COLUMN_NAME like '%DEVICE%ID%'

...


Once you know your tables and column you casn then proceed with the delete commands using either the ...WHERE device_id_col IN (...)
or use a HELPER TABLE with device ids to be listed and then delete correspondig records.

This is really basic SQL stuff, and if you are not ready to deal with this jsut yet, then be sure to work with a peer, GOOGLE a lot, and/or get your hand on a SQL COOKBOOK.

Good Luck!
Hein.

[Sajjad Sahir]

Dear Martin

sql >delete from table1, table2 where device id=A1;


Sajjad

[Eric Antunes · Accepted solution]

Hi Martin,

You need to steps to do this in a practical way:

a) generate the delete script:

select 'delete from '||dtc.table_name||' where DEVICE_ID = ''A1'';'
from dba_tab_columns dtc
where dtc.column_name = 'DEVICE_ID';

b) Validade all the table names, copy the script to a *.sql (Unix) file or to the clipboard (Windows) and execute it in SQL*Plus

Best Regards,

Eric Antunes