?
Solved

run update after insert

Posted on 2007-03-29
7
Medium Priority
?
352 Views
Last Modified: 2013-12-19
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
Comment
Question by:rutgermons
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 3

Expert Comment

by:kimarti3
ID: 18817894
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
 
LVL 9

Expert Comment

by:konektor
ID: 18818110
it you want to "insert or update" command, u can use MERGE statement instead of them
0
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 2000 total points
ID: 18818441
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:rutgermons
ID: 18820050
folks

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

tia

r
0
 
LVL 27

Expert Comment

by:sujith80
ID: 18821506
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
 

Author Comment

by:rutgermons
ID: 18821828
sujith

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

r
0
 
LVL 27

Expert Comment

by:sujith80
ID: 18821895
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question