Link to home
Start Free TrialLog in
Avatar of DaveChoiceTech
DaveChoiceTech

asked on

SQL UPDATE QUERY PROBLEM

This is my problem

Table A has columns ID1,ID2 and NAME
Table B has columns ID1 and CATEGORY
Table C has columns ID1, ID2

B.CATEGORY can have the value X,Y and Z
A.ID2 has not had any values assigned

Step A: Get C.ID1, C.ID2 where C.ID1=B.ID1 and B.CATEGORY = 'X'
Step B: Update A.ID2 with C.ID2 where C.ID1 = A.ID1 for the records selected in step A

I am using DB2
Avatar of Member_2_2484401
Member_2_2484401
Flag of United States of America image
This should get you close. I split it into two steps for clarity.

create table StepA as (
   select c.ID1, c.ID2
     from TableC c
     join TableB b
       on b.id1 = c.id1
      and b.category = 'X'
) with data

Open in new window


Step B: Update A.ID2 with C.ID2 where C.ID1 = A.ID1 for the records selected in step A

update TableA a
   set a.id2 = (
       select id2
         from StepA s
        where s.id1 = a.id1)
 where exists (select 1
                 from StepA sa
                where sa.id1 = a.id1
                  and sa.id2 = a.id2)

Open in new window


HTH,
DaveSlash
Avatar of DaveChoiceTech
DaveChoiceTech

ASKER

I need to do this without creating an intermediate table.
ASKER CERTIFIED SOLUTION
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of DaveChoiceTech
DaveChoiceTech

ASKER

Perfect!