CODEWITHSUNDEEP
sqldatabaseoracle-database
Attila
Attila

Reputation: 107

Oracle SQL query a table and delete from an other based on the results

So I have the following tables,

   CREATE TABLE Accounts (
        Acc_ID INTEGER PRIMARY KEY,
        Acc_Name VARCHAR(50) NOT NULL
    );

    CREATE TABLE City (
        Zip VARCHAR(20) PRIMARY KEY,
        CityName VARCHAR(30) NOT NULL
    );

    CREATE  TABLE Address (
        Addr_ID INTEGER PRIMARY KEY,
        Street VARCHAR(50) NOT NULL,
        HouseNo VARCHAR(10) NOT NULL,
        Zip VARCHAR(20) NOT NULL REFERENCES City ON DELETE CASCADE
    );


    CREATE TABLE Located (    
        Acc_ID INTEGER NOT NULL REFERENCES Accounts ON DELETE CASCADE,
        Addr_ID INTEGER NOT NULL REFERENCES Address ON DELETE CASCADE,
        PRIMARY KEY(Acc_ID, Addr_ID)

I would like to use a similar as query below to be able to delete the row based on the result from the select query.

delete from Address where add_id=(select addr_id from Located where ACC_ID=1);

Is that possible ? if yes how ?

Upvotes: 0

Views: 42

Answers (1)

Erich Kitzmueller
Erich Kitzmueller

Reputation: 36977

You nearly got it, your proposal even works in some cases (when the subquery finds one row). Here is the more general case, you just need IN instead of =:

delete from Address 
  where add_id IN (select addr_id from Located where ACC_ID=1);

Upvotes: 1

Related Questions