I worked out the syntax quirks and now it looks like this: UPDATE eim_contact SET con_cust_stat_cd = ( SELECT b.pc_status FROM eim_contact a, eim_contact b WHERE a.row_id = b.con_person_uid AND b.con_pr_postn = 'Y' )
Now I get : Single row sub-query returns more than one row. I'm trying to upate multiple rows in the same table. - Frank On Tue, 18 Jan 2005 08:44:34 -0500, Frank Mamone <[EMAIL PROTECTED]> wrote: > Greg, > > I tried this: > > UPDATE EIM_CONTACT > SET A.CON_CUST_STAT_CD = > ( > SELECT B.PC_STATUS > FROM EIM_CONTACT A , EIM_CONTACT B > WHERE A.ROW_ID = B.CON_PERSON_UID > AND B.CON_PR_POSTN = 'Y' > ) > > I get Table or View does not exist. > > - Frank > > > On Mon, 17 Jan 2005 16:17:59 -0600, Greg Morphis <[EMAIL PROTECTED]> wrote: > > Frank, > > > > use something like this.... > > > > UPDATE TEST1 > > SET CHR = > > ( > > SELECT CHR FROM TEST2 > > WHERE TEST1.num = TEST2.num > > ) > > > > adjust your query to... > > UPDATE TABLE1 > > SET TABLE1.FIELD = > > ( > > SELECT > > TABLE2.FIELD > > FROM TABLE1, TABLE2 > > WHERE TABLE1.KEY = TABLE2.KEY > > AND TABLE1.FIELD = 'Y' > > ) > > > > > > On Mon, 17 Jan 2005 16:42:23 -0500, Frank Mamone <[EMAIL PROTECTED]> wrote: > > > I am having trouble doing an update with a join in Oracle. I am > > > getting this error: > > > > > > SQL command not properly ended. > > > > > > Here is the generic syntax I am using: > > > > > > UPDATE TABLE1 > > > SET TABLE1.FIELD = TABLE2.FIELD > > > FROM TABLE1, TABLE2 > > > WHERE TABLE1.KEY = TABLE2.KEY > > > AND TABLE1.FIELD = 'Y' > > > > > > Thanks for your help. > > > > > > Frank > > > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware: a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:190908 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
