Re: SQL Query Help

Venkateshwaran.I_at_geind.ge.com (Venkat) wrote in message news:<b9f3c8e9.0209242040.5df76a08_at_posting.google.com>...
> Hi Friends,
>
> Could you please help me how this can be done in SQL.
>
> I have 2 tables.
>
> dept
> ----
> SQL>select * from dept;
> deptno dname loc1 loc2 loc3
> ------- ----- ---- ---- ----
> 10 X LA1 LA2 LA3
> 20 Y LB1 LB2 LB3
> 30 Z LC1 LC2 LC3
> 40 A LD1 LD2 LD3
>
> emp
> ---
> SQL>select * from emp;
>
> empno locations position
> ----- --------- --------
> 10 AA 1
> 20 AB 1
> 10 AC 3
> 10 AD 2
> 20 AE 3
> 30 AF 2
> 40 AG 1
> 30 AH 1
>
> Now I want to update dept tables based on the following condition,
>
> For each matching deptno from emp to dept, take the locations value from
> emp and update the dept table's column. The column may be loc1, loc2 or loc3
> based on position in emp table.
>
> After updating dept should look like this,
>
> SQL>select * from dept;
> deptno dname loc1 loc2 loc3
> ------- ----- ---- ---- ----
> 10 X AA AD AC
> 20 Y AB LB2 AE
> 30 Z AH AF LC3
> 40 A AG LD2 LD3
>
> Thanks,
> Venkat

Try this

update dept
set loc1 = (select locations from emp

             where emp.deptno = dept.deptno
              and position  = 1)
set loc2 = (select locations from emp
             where emp.deptno = dept.deptno
              and position  = 2)
set loc3 = (select locations from emp
             where emp.deptno = dept.deptno
              and position  = 3)