Hi.

I have two tables TAB1(codexp;dataexp) and TAB2(codexp;nvol)

I want to delete the data from the two tables but the search it is onli on the first table.

EX:

TAB1

codexp      dataexp

1             02/10/2008

2             03/10/2008

3             03/10/2008

4             04/10/2008

TAB2

codexp     nvol

1                2

2                1

3                3

4                2

i want to delete the information from the two tables where the dataexp= 03/10/2008, is that possible?

Thanks

Any DELETE query can delete rows from only one table at a time. You would normally need two separate DELETE queries to delete rows from two separate tables.

If there is a foreign key defined between these two tables, with cascading deletes, then deleting rows from the parent table will delete all rows from the child table that have the corresponding foreign key values.

Instead of a foreign key relationship, there may be a delete trigger defined on the "parent" table that implements cascading deletions.

delete from tab2

where codexp in (select codexp from tab1 where dataexp= '03/10/2008')

delete from tab1 where dataexp= '03/10/2008')

Madhivanan:

delete from tab2

where codexp in (select codexp from tab1 where dataexp= '03/10/2008')

I would prefer the JOIN version, but depending on the requirements a referential constraint could do the job as well.

FrankKalis:

I would prefer the JOIN version, but depending on the requirements a referential constraint could do the job as well.

Thank's for your quick response.

The solution posted is good for two tables but for more it's not efficient, can you tell me how to do it with JOIN?

TAB1(codexp;dataexp;impcod)  TAB2(codexp;nvol) and TAB3(impcod;nimp)

EX:

TAB1

codexp      dataexp            impcod

1             02/10/2008             12

2             03/10/2008             13

3             03/10/2008             14

4             04/10/2008             15

TAB2

codexp     nvol

1                2

2                1

3                3

4                2

TAB3

impcod     nimp

12                5

13                5

14                7

15                5

 If there is any specific requirment then go with what Adriaan suggested define a FK relationship with cascade deletion, but for deleting the records from 3rd table as you mentioned I don't think if it is possible with one delete statement. As in T1 the PK will be either codexp or impcod.

Lets see what our Gurus has to say.....

FrankKalis:

Madhivanan:

delete from tab2

where codexp in (select codexp from tab1 where dataexp= '03/10/2008')

 

I would prefer the JOIN version, but depending on the requirements a referential constraint could do the job as well.

Yes. I should have used Join