I am surprised something like the example I emailed earlier would not
work for this. Though maybe I am skimming the problem too much. I
update our LDAP table off of itself via the merge statement. My guess
on 9i
MERGE INTO EIM_CONTACT A
USING ( SELECT B.PC_STATUS, B.CON_PERSON_UID
FROM EIM_CONTACT A, EIM_CONTACT B
WHERE A.ROW_ID = B.CON_PERSONUID
AND A.CON_PR_POSTN = 'Y' ) B
ON (A.ROW_ID = B.CON_PERSONUID)
WHEN MATCHED THEN UPDATE SET A.CON_CUST_STAT_CD = B.PC_STATUS
-- This should never run due to join above
WHEN NOT MATCHED THEN INSERT (CON_CUST_STAT_CD) VALUES (null)
There are a lot of different examples for updating a table off
"another" table in this link:
http://asktom.oracle.com/pls/ask/f?p=4950:8:14984877134114249405::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:273215737113
On Tue, 18 Jan 2005 14:07:18 -0500, Frank Mamone <[EMAIL PROTECTED]> wrote:
> 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:190979
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
