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
Solved

run update after insert

Posted on 2007-03-29
7
346 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
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 500 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
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 shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

840 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