CODEWITHSUNDEEP
sqloracle-databaseinsert
Matin
Matin

Reputation: 117

Insert record in a table based on a column in another table (in Oracle SQL developer)

I want to insert new records in table Prices(Date, ID1, Price) based on records in table Headers (Date, ID1, ID2). If there exists a record in table Headers where its ID2 equals to ID1 of another record in this table, then a new record must be inserted in table Prices where its Date and ID1 is taken from table Headers, and its Price equals to the Price of the record with ID1 already in this table. For example:

Input:

Table Headers:

Date          ID1         ID2
-------------------------------
2013.08.10     100        200
2013.08.10     300        100
2013.08.10     400        100
2013.08.11     200        500
2013.08.11     500        200
2013.08.11     600        200


Table Prices:

Date          ID1      Price
---------------------------
2013.08.10     100      500
2013.08.11     200      1200

The output must include new records inserted in Prices for ID1=300 and ID1=400 with Price=500 (since the records with ID1= 300 and ID1=400 have ID2=100 which corresponds to the record with ID1=100). Similarly for the records with ID1=500 and ID1=600 new records must be inserted in table Prices based on the price of ID2=200:

Date          ID1        Price
--------------------------------
2013.08.10     100        500
2013.08.10     300        500
2013.08.10     400        500
2013.08.11     200        1200
2013.08.11     500        1200
2013.08.11     600        1200

Upvotes: 1

Views: 4675

Answers (2)

geomagas
geomagas

Reputation: 3280

The following query should do the trick:

insert into Prices ("Date",ID1,Price)
select h."Date",h.ID1,p.Price
from Headers h join Prices p on (p.ID1=h.ID2)
where h.ID1 not in (select ID1 from Prices)

Upvotes: 1

Laurence
Laurence

Reputation: 10976

There's not enough examples to know if this is entirely correct, but it works for the single provided case:

Insert Into Prices (
    "Date",
    id1,
    Price
)
Select
    h2."Date",
    h2.id1,
    p.Price
From
    Headers h1
        Inner Join
    Headers h2
        on h1.id1 = h2.id2
        Inner Join
    Prices p
        on h2.id2 = p.id1;

Example Fiddle

Upvotes: 1

Related Questions