Neil- FYI: yes this can be done, but not as written. Oracle does not use the syntax "RIGHT JOIN" nor does it use "ON". Oracle join syntax is in the WHERE clause. An inner join would be WHERE FOOA.FieldA = FOOB.FieldA An outer join would be WHERE FOOA.FieldA = FOOB.FieldA(+) The plus sign notation indicates that Oracle should add extra rows to the result where there is no matching record. -Ben
-----Original Message----- From: Neil Clark [mailto:[EMAIL PROTECTED]] Sent: Tuesday, October 16, 2001 5:07 AM To: CF-Talk Subject: RE: Oracle Joined Update Query To the best of my knowledge it can be done...? maybe it cant I always thought it could.? My understanding is that supposing we have a master table called FOOA and an update table called FOOB, and both have a structure as follows.. Column Type ------- ----- FieldA Numeric, Primary Key FieldB String We want records in the FOOB table to impact the Cumulative table as shown in the diagram. If an Update records doesn't match a record in Cumulative (based on FieldA) then it should be appended. If it does match, then any differences in the Update record should update the Cumulative record. The following query achieves this effect: UPDATE FOOA RIGHT JOIN FOOB ON FOOA.FieldA = FOOB.FieldA SET FOOA.FieldA = FOOB.FieldA, FOOA.FieldB = FOOB.FieldB It should be no surprise that this join query accomplishes the update of the matching records. The modestly surprising part is that this UPDATE query appends a record to the FOOB table where no record matches the Updates row. If however we had run this as an ordinary SELECT ... RIGHT JOIN query: SELECT FOOA.FieldA, FOOA.FieldB, FOOB.FieldA, FOOB.FieldB FROM FOOA RIGHT JOIN FOOB ON FOOA.FieldA = FOOB.FieldB ...then the presence of rows in the result set contributed by FOOB, with no matching values in the FOOA columns (ie: NULLS) would be unsurprising. So the only wrinkle in the UPDATE query is that these unmatched Updates rows cause rows to be appended to FOOA. Update Only Some Columns In some scenarios, records in the FOOB table may be only partial -- that is to say they may specify values for only some of the columns, with nulls for others. The intent might be to match an existing record in the FOOA table and then alter only the columns with values in FOOB, and leave the other columns alone. For this, the following variation on the query does the job. Note the IIf for the FieldB column. UPDATE FOOA RIGHT JOIN FOOB ON FOOA.FieldA = FOOB.FieldA SET FOOA.FieldA = [FOOB].[FieldA], FOOA.FieldB = IIf(IsNull(FOOB.FieldB), FOOA.FieldA, FOOB.FieldB) HTH Neil Team Macromedia 'I really must buy a watch, but I just cant find the time.' <!------------------------------ Neil Clark :: Team Macromedia Senior Applications Engineer ColdFusion / Spectra / Sitespring MCB Digital Limited :: Macromedia Alliance Partner [T]: +44 (0) 20 8941 3232 [F]: +44 (0) 20 8941 4333 [M]: +44 (0) 7719 448 227 [E]: [EMAIL PROTECTED] [www] www.mcbdigital.com --------------------------------> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
