Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

run update after insert

Posted on 2007-03-29
7
Medium Priority
?
353 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

636 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