Why didn't you just do this, then? UPDATE TABLE1 SET TABLE1.FIELD = ( SELECT TABLE2.FIELD FROM TABLE2 WHERE TABLE1.KEY = TABLE2.KEY AND TABLE1.FIELD = 'Y' )
(You don't need the outer table in your inner query.) ----- Original Message ----- From: "Frank Mamone" <[EMAIL PROTECTED]> To: "CF-Talk" <cf-talk@houseoffusion.com> Sent: Thursday, January 20, 2005 6:28 PM Subject: Re: Oracle help please >I was trying to run an update with a self-join. The table has no > primary key attributes. It's a an interface table for data migration. > Here is one variation: > > UPDATE TABLE1 > SET TABLE1.FIELD = > ( > SELECT > TABLE2.FIELD > FROM TABLE1, TABLE2 > WHERE TABLE1.KEY = TABLE2.KEY > AND TABLE1.FIELD = 'Y' > ) > > I was just wondering if my interpretation of the error was correct. > Note that non-join updates > work without error. > > - Frank > > On Thu, 20 Jan 2005 22:16:45 -0000, Ciliotta, Mario > <[EMAIL PROTECTED]> wrote: >> Frank, >> >> What is the exact SQL that you are now trying to run? >> >> Mario >> >> -----Original Message----- >> From: Jochem van Dieten [mailto:[EMAIL PROTECTED] >> Sent: Thursday, January 20, 2005 4:42 PM >> To: CF-Talk >> Subject: Re: Oracle help please >> >> Frank Mamone wrote: >> > >> > 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? >> >> I am not familiar with that error. >> >> 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:191340 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=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
