The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
SQL Update Statement Using Multiple Tables In Where Clause
Posted: 15 Aug 07 1:54 PM
|
Arrggghhhh - I am trying to be friends with the SLX OELDB Provider and we aren't getting along very well.
I have an update statement that works well in Query Analyzer but fails in SLX Admin. Can someone please throw me a bone and point me in a better direction?
Update Opportunity_Ext
set
export_cofa=b.export_cofa
from Opportunity_Ext a inner join
opportunity o on o.opportunityid = a.opportunityid inner join
Aircraft_ext b on a.AircraftId=b.AircraftId inner join
aircraft ac on a.gac_aircraftid = ac.aircraftid
where o.status = 'Won'
or ac.OH = 'T'
Many thanks! |
|
|
|
Re: SQL Update Statement Using Multiple Tables In Where Clause
Posted: 15 Aug 07 6:24 PM
|
Jim, as you've found, this type of UPDATE statement (I think it's the FROM clause that stuffs things up) does not work through the provider.
But you might see whether you can get a correlated subquery working:
Update Opportunity_Ext
set
export_cofa=(select b.export_cofa
from opportunity o on o.opportunityid = Opportunity_Ext.opportunityid inner join
Aircraft_ext b on Opportunity_Ext.AircraftId=b.AircraftId inner join
aircraft ac on Opportunity_Ext.gac_aircraftid = ac.aircraftid
where o.status = 'Won'
or ac.OH = 'T')
(Untested - you'll prob have to fix the syntax) |
|
|
|
Re: SQL Update Statement Using Multiple Tables In Where Clause
Posted: 17 Aug 07 11:49 AM
|
Jim,
Sometimes the join statement chokes up an Update statement. Try this and see how it goes:
--c
UPDATE Opportunity_Ext
SET
export_cofa=b.export_cofa
FROM Opportunity_Ext a, opportunity o, Aircraft_ext b, aircraft ac
WHERE o.opportunityid = a.opportunityid
AND a.AircraftId=b.AircraftId
AND a.gac_aircraftid = ac.aircraftid
AND (o.status = 'Won'OR ac.OH = 'T' )
|
|
|
|
Re: SQL Update Statement Using Multiple Tables In Where Clause
Posted: 20 Aug 07 1:24 PM
|
Alternatively, this may work
UPDATE Opportunity_Ext
SET
export_cofa=Aircraft_ext.export_cofa
FROM Opportunity_Ext a
WHERE (Exists (Select opportunity.opportunityid from opportunity where a.opportunityid =opportunity.opportunityid and opportunity.status = 'Won') OR
Exists (Select aircraft.aircraftid from aircraft where a.gac_aircraftid = aircraft.aircraftid and aircraft.OH = 'T'))
AND Exists (Select Aircraft_ext.Aircraftid from Aircraft_ext where a.AircraftId=Aircraft_ext.AircraftId)
|
|
|
| |
|
Re: SQL Update Statement Using Multiple Tables In Where Clause
Posted: 07 Sep 07 4:30 PM
|
Thanks everybody for all of your help!
The key to the solution was to get the tableid in the subselect (props to mike spragg on "some other board").
For those that may follow in this path in the future, here was the final answer and hopefully you can make it work for you!
update opportunity_ext
set
export_cofa = (select m.export_cofa from aircraft_milestone m where m.aircraftid = opportunity_ext.aircraftid)
where opportunityid in (select o.opportunityid from opportunity o
inner join opportunity_ext oe on o.opportunityid=oe.opportunityid
inner join aircraft a on oe.aircraftid = a.aircraftid
where o.status = 'Won' or ac.OH = 'T')
|
|
|
|