Hi Jochem, With everyone's help I finally did get the syntax working but I get the famous 'non-preserved key' message. I finally did it by updating the majority doing a straight update and processing the exceptions manually.
I was just wondering if you can enlighten me on the meaning of the message -- if you are familiar with it. From what I read, both tables being joined must both have primary keys defined. Do they mean defined as physical attributes of the tables? Thanks, --Frank On Tue, 18 Jan 2005 21:12:50 +0100, Jochem van Dieten <[EMAIL PROTECTED]> wrote: > Frank Mamone wrote: > > > > ROW_ID > > CON_PERSON_UID > > PC_STATUS > > CON_CUST_STATUS > > CON_PR_POSTN > > > > I need to put the value of PC_STATUS into CON_CUST_STATUS where > > CON_PERSON_UID matches ROW_ID and marked as CON_PR_POSTN = 'Y' > > > > Remember this is denormalized data. So, essentially I need to do a > > self join update. With MS SQL I would use this > > > > UPDATE EIM_CONTACT > > SET EIM_CONTACT.CON_CUST_STAT_CD = EIM_CONTACT1.PC_STATUS > > FROM EIM_CONTACT , EIM_CONTACT EIM_CONTACT1 > > WHERE EIM_CONTACT.ROW_ID = EIM_CONTACT1.CON_PERSON_UID > > AND CON_PR_POSTN = 'Y' > > If that works you need to file a bug with Microsoft. Imagine the > following dataset: > > ROW_ID CON_PERSON_UID PC_STATUS CON_CUST_STATUS CON_PR_POSTN > x1x ? Y > ad x1x 2 Y > df x1x 3 Y > > The outcome of your update query on this dataset is undefined. > Should it set CON_CUST_STATUS in the first row to 2 or to 3? > > If you make the database choose consistently between the 2 and > the 3 (usually through aggregate functions) you have solved the > "Single row sub-query returns more than one row." error message > and then it will work. > > Jochem > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:191189 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
