• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 357
  • Last Modified:

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
0
rutgermons
Asked:
rutgermons
1 Solution
 
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
rutgermonsAuthor Commented:
folks

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

tia

r
0
 
sujith80Commented:
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
 
sujith80Commented:
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now