run update after insert

Folks

I have the following insert

INSERT INTO C(AIRLINE, delayinterval,LOCATION,ACTLABHRS,ACTLABCOST)
(
SELECT AIRLINE, avg_delay,MTBF,0,0 FROM
(
SELECT AIRLINE, AVG(DELAY) avg_delay,MTBF,0,0
FROM
(
SELECT DELAY.AIRLINE, lead(DELAY.DELAY) over(PARTITION BY DELAY.AIRLINE ORDER BY DELAY.AIRLINE, DELAY.DELAY)
- DELAY.DELAY DELAY,AIRLINE.MTBF,0,0
FROM DELAY,AIRLINE WHERE inserted <> 'Y'
AND DELAY.AIRLINE=AIRLINE.AIRLINE
)
GROUP BY AIRLINE,MTBF
)
WHERE avg_delay < MTBF
)

how do i run an update of the field 'INSERT' to 'Y'of the reocrds inserted

EG:

UPDATE DELAY SET INSERT = 'Y'
 WHERE

(
SELECT AIRLINE, avg_delay,MTBF,0,0 FROM
(
SELECT AIRLINE, AVG(DELAY) avg_delay,MTBF,0,0
FROM
(
SELECT DELAY.AIRLINE, lead(DELAY.DELAY) over(PARTITION BY DELAY.AIRLINE ORDER BY DELAY.AIRLINE, DELAY.DELAY)
- DELAY.DELAY DELAY,AIRLINE.MTBF,0,0
FROM DELAY,AIRLINE WHERE inserted <> 'Y'
AND DELAY.AIRLINE=AIRLINE.AIRLINE
)
GROUP BY AIRLINE,MTBF
)
WHERE avg_delay < MTBF
)

all help will do
rutgermonsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kimarti3Commented:
I would put a trigger on the table that you are inserting data into.  When an insert is being done, the trigger is fired to update the Insert column in the Delay table where the primary keys match.
0
konektorCommented:
it you want to "insert or update" command, u can use MERGE statement instead of them
0
Naveen KumarProduction Manager / Application Support ManagerCommented:
update delay d set insert = 'Y'
where exists ( select 1 from c where c.airline = d.airline and c.mtbf and d.mtbf  )
and nvl(insert,'*') <> 'Y';

Try the above first and verify. Then u can do a commit if everything is ok or otherwise do a rollback.

Thanks
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

rutgermonsAuthor Commented:
folks

could you send me an example to fit this in to work pls

tia

r
0
SujithData ArchitectCommented:
Are you sure the column name is INSERT???? Oracle will not allow you to have a column with that name.

UPDATE delay
SET    inserted = 'Y'
WHERE  inserted <> 'Y' and exists (select 1 from AIRLINE where DELAY.AIRLINE=AIRLINE.AIRLINE )
/
0
rutgermonsAuthor Commented:
sujith

can i place this update under my first insert? if so how?

r
0
SujithData ArchitectCommented:
I didnt correctly understand this -
>>can i place this update under my first insert? if so how?

The insert and update are two different commands. You can run the update after the insert command.
Just as simple as

insert into <table>
(select statement) ;

update <table>
set <column> = <value>
where <where condition> ;

Notice that there is a semicolon after the insert command.

Now in your case , before running the update you run the following select statement to verify that these are the correct records that you are going to update.

SELECT * FROM DELAY
WHERE  inserted <> 'Y' and exists (select 1 from AIRLINE where DELAY.AIRLINE=AIRLINE.AIRLINE )
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.