SAP (Documents/Screen shots/Complete Solution): RE:[peopletools-l] Update Query -- all Field values in Table1 where Field value found in Table2

Reply from Howler_Fish on Jan 17 at 5:13 PM

UPDATE sysadm.MY_TABLE A
SET MY_FIELD = (SELECT B.EMPLID FROM serv.path@databasedblink B WHERE B.username = A.MY_FIELD AND ROWNUM = 1)
WHERE EXISTS (SELECT NULL FROM serv.path@databasedblink B WHERE B.username = A.MY_FIELD AND ROWNUM = 1)

This allows the database to loop through all instances in MY_TABLE, run the test and then retrieve the value when needed.

---------------Original Message---------------
From: Rico Yah
Sent: Tuesday, January 17, 2012 4:45 PM
Subject: Update Query -- all Field values in Table1 where Field value found in Table2

Is there a way to write this query without having to hardcode the names? I would need to run a query to find which names, count them, create an update statement for each, etc. I would have many more than 3, this is just an example ....

Is there logic that would allow me to do this dynamically? Thanks ...

BEGIN
FOR i IN REVERSE 1..3 LOOP

UPDATE sysadm.PS_MY_TABLE
SET MY_FIELD = (SELECT a.emplid
FROM serv.path@databasedblink a
WHERE a.username = 'name1' and rownum = 1)
WHERE MY_FIELD ='name1';

UPDATE sysadm.PS_MY_TABLE
SET MY_FIELD = (SELECT a.emplid
FROM serv.path@databasedblink a
WHERE a.username = 'name2' and rownum = 1)
WHERE LSC_SAP_LOG_ADMN ='name2';

UPDATE sysadm.PS_MY_TABLE
SET MY_FIELD = (SELECT a.emplid
FROM serv.path@databasedblink a
WHERE a.username = 'name3' and rownum = 1)
WHERE MY_FIELD ='name3';

END LOOP;
END;

Reply to this email to post your response.

__.____._

Manage Settings | Unsubscribe | Create FAQ | Send Feedback