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

Question

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

Does your provided expected output show (1) the final state of the Prices table or (2) the rows that must be inserted into it? Because, if it is the latter, you will have duplicate ID1s, since 100 and 200 are already in Prices. — geomagas
– geomagas, Commented Oct 27, 2013 at 8:35

Laurence · Accepted Answer · 2013-10-26 23:38:35Z

1

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

answered Oct 26, 2013 at 23:38

Laurence

11k1 gold badge28 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

geomagas · Accepted Answer · 2013-10-27 09:09:27Z

1

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)

answered Oct 27, 2013 at 9:09

geomagas

3,2901 gold badge19 silver badges28 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related