Definately not what I want. This is an ETL job. So the system exports the data from several tables and denormalizes it. Then I need to do the updates and re-import.
So as you can imagine there will be a lot of empty rows. Here is what I have: 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' I hope this clarifies the situation. - F On Tue, 18 Jan 2005 19:18:01 +0100, Jochem van Dieten <[EMAIL PROTECTED]> wrote: > Frank Mamone wrote: > > TABLE2 is actually the same table so I need to use an alias. Without a > > from I cannot give it an alias name. > > If TABLE1 and TABLE2 are the same table, your example: > UPDATE TABLE1 > SET TABLE1.FIELD = TABLE2.FIELD > FROM TABLE1, TABLE2 > WHERE TABLE1.KEY = TABLE2.KEY > AND TABLE1.FIELD = 'Y' > > simplifies to a no-op: > UPDATE TABLE1 > SET FIELD = FIELD > WHERE FIELD = 'Y' > > which is probably not what you want. > > How about giving us the real schema? > > Jochem > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:190973 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
