CODEWITHSUNDEEP
sqloracle-database
user906153
user906153

Reputation: 1218

Deleting rows from two tables

I'm trying to delete rows from two different tables at the same time. The tables look something like this:

TABLE - Value
-----------
ID
Field
<some other stuff...>

TABLE - Srch
-----------
Value_ID
Srch_ID
<some other stuff...>

I'm trying to delete all the rows from VALUE where field is equal to 'SUCCESS' and also delete the rows from SRCH that are linked to VALUE through value_id. In SRCH there can be multiple rows with the same value_id.

What would this SQL statement look like?

Upvotes: 2

Views: 3669

Answers (3)

Mualig
Mualig

Reputation: 1444

I recommend using foreign keys between your 2 tables and ON DELETE CASCADE. So you just have to delete the row in Value and all the corresponding rows in Srch will be deleted.

You can add the following constraint to your database:

ALTER TABLE Srch ADD CONSTRAINT
FK_Value_ID FOREIGN KEY(Value_ID)
REFERENCES Value (ID) 
ON DELETE CASCADE;

Just to be clear (quote from xQbert comment): "the cascade will ALWAYS happen" when an ID from Value is deleted.

Upvotes: 5

David Manheim
David Manheim

Reputation: 2626

While changing the database to do this automatically is ideal, if this is always the behavior you want, and you have permissions on the database to do so.

Otherwise, especially if this is a one time or occasional thing, it can be done in a set of 2 simple queries, run together:

Delete from Srch where value_id in (Select ID from value where field='SUCCESS')
Delete from value where field='SUCCESS'

If this is a live system, you want to run these as part of a single transaction, so that you don't have values updated in between the two statements, and leave orphans, or something similar. If run with the correct isolation level, this will be your easiest, simplest way of doing this.

Upvotes: 3

Brandon Kreisel
Brandon Kreisel

Reputation: 1636

Or a more complex solution Using PL/SQL but no foreign key:

DEFINE 
v_success_count number;
v_val_id number;
BEGIN

SELECT INTO v_success_count count(*) FROM


WHILE (select count(*) FROM value WHERE field = 'SUCCESS') > 0
LOOP
    SELECT value_id INTO val_id FROM value WHERE field = 'SUCCESS' AND rownum = 1;

    DELETE FROM srch WHERE value_id = v_val_id;

    DELETE FROM value WHERE field = 'SUCCESS' AND rownum = 1;
END LOOP
COMMIT;
END;

Upvotes: 0

Related Questions