Update of table using joined tables !?

[OLAPer]

Hi everyone

I have a query that is boggling me !

I have three tables, e.g. S_CONTACT, EIM_CONTACT and S_EMPLOYEE.

==============
S_CONTACT
- Created_by
- Row_id
===============

EIM_CONTACT
- Created_by
- T_Contact__RID

===============

S_EMPLOYEE
- Row_id
- Login

===============

The relationships between these tables are:

S_CONTACT - EIM_CONTACT
Row_id - T_Contact__rid

EIM_CONTACT - S_EMPLOYEE
Created_by - Login

The Created_by in the EIM_CONTACT table is the LOGIN in the S_EMPLOYEE table. What I want to do is (update) make the Created_by in the S_CONTACT table equal the corresponding ROW_ID in the S_EMPLOYEE table.

The following SQL gives me an indication of what I need, but it is the SELECT, I need to make this an UPDATE !

select 
     A.row_id as S_Employee_ROW_ID, 
     A.login as S_Employee_LOGIN, 
     B.row_id as S_Contact_ROW_ID, 
     B.created_by as S_Contact_CREATED_BY, 
     c.created_by as EIM_Contact_Created_by 
from 
     siebel.s_employee A, 
     siebel.s_contact B, 
     siebel.eim_contact C 
where 
     B.row_id = C.T_contact__rid and 
     A.login = upper(C.created_by);

I imported the tables into Access 2000, and using the query builder got the following:

UPDATE SIEBEL_S_CONTACT INNER JOIN (SIEBEL_S_EMPLOYEE INNER JOIN SIEBEL_EIM_CONTACT ON SIEBEL_S_EMPLOYEE.LOGIN = SIEBEL_EIM_CONTACT.CREATED_BY) ON SIEBEL_S_CONTACT.ROW_ID = SIEBEL_EIM_CONTACT.T_CONTACT__RID SET SIEBEL_S_CONTACT.CREATED_BY = [SIEBEL_EIM_CONTACT].[created_by];

Now I'm confused, I can't run this in DB2. Does it shed any light on the situation? Would anyone please please please be able to help me out of "another fine mess" (!!!!) I have gotten myself into!

Regards and peace !

OLAPer

 

[dbmsguy]

no, I don't think you can do this in DB2...

the stmt

update x a set col1 = (select col1 from y b where a.col1=b.col1)

will only work if if the result of the subselect is one value.

I don't think that it is possible to update different rows of col1 to different values.

it would be easier to just write a script to generate multiple update stmts. Paul

http://www.dbmsguy.com

 

[OLAPer]

Paul

I was attempting to use a correlated sub-select, in order for the itteration to occur.

I am in the process of testing the following, but it is taking a lot of time, I have 68,000 contact records that need updating !!

UPDATE siebel.S_contact B
SET created_by = (SELECT
distinct A.row_id
FROM
siebel.eim_contact C,
siebel.s_employee A
WHERE
B.row_id = C.t_contact__rid
AND
UPPER(C.created_by) = A.login
)
WHERE EXISTS (SELECT
X.t_contact__rid
FROM
siebel.eim_contact X,
siebel.s_contact Y,
siebel.s_employee Z
WHERE
X.t_contact__rid = Y.row_id
AND
X.created_by = UPPER(Z.login));

The second where clause is used to limit the update, so that attempted NULL update is not undertaken.

Not sure if this is going to work, but hey let's give it a shot, as soon as it is done I'll let you guys know.

In the mean time if you see a flaw in the code please let me know !

Regards


OLAPer
('Tis but to suffer the Slings and Arrows of Outrageous Fortune!) - Shakie !

 

[OLAPer]

OK, it really hasn't taken me 4 days to run the script, but there have been some problems.

Firstly the script above works, the problem I had was that there were erroneous duplicate records in the EIM_CONTACT table, hence the return of the correlated sub select was more than one row.......so the update couldn't complete !!!!

I have run the following on a set of tables that have unique t_ row ids.

UPDATE siebel.S_org_ext B
SET created_by = (SELECT distinct A.row_id
FROM siebel.eim_Account C,
siebel.s_employee A
WHERE
B.row_id = C.t_org_ext__rid
AND UPPER(C.created_by) = A.login)
WHERE exists (SELECT distinct Y.row_id
FROM
siebel.eim_Account X,
siebel.s_employee Y
WHERE
B.row_id = X.t_org_ext__rid
AND UPPER(X.created_by) = Y.login);

This worked !!!!

In terms of the problem with the duplicate records the answer is that the migrated data from the client needs to be cleansed.