Forum Stats

  • 3,733,246 Users
  • 2,246,737 Discussions
  • 7,856,631 Comments

Discussions

Update w/ join

651999
651999 Member Posts: 19
edited July 2008 in SQL & PL/SQL
Not sure how to do this.

table1 - amount field, business id
table2 - employee id, business id
table3 - amount field, employee id


Need to update a field on a table

I have to update amount filed on table1.
I need to join table1 & table2 by business id to get rows i want to update from table1 & need to join table2 & table3 by employee id to get the amount from table 3.

I need to update the amount from table1 to the amount on table3.

any ideas?

Comments

  • SomeoneElse
    SomeoneElse Member Posts: 14,866 Silver Crown
    update table3 t3
    set t3.amount = (select t1.amount
    from table1 t1
    ,table2 t2
    where t1.business_id = t2.business_id
    and t2.employee_id = t3.employee_id
    )
    where exists (select t1.amount
    from table1 t1
    ,table2 t2
    where t1.business_id = t2.business_id
    and t2.employee_id = t3.employee_id
    )
    ;
  • 647253
    647253 Member Posts: 44
    Same principle as SomeoneElse, but I read it as table 1 required the update of the amount field in Table 3!!:
    update table1 t1
    set amount = (select t3.amount
                  from    table2 t2
                  join    table3 t3 using (employeeid)
                  where   t2.business_id = t1.business_id)
    WHERE EXISTS (select 1
                  from    table2 t2
                  join    table3 t3 using (employeeid)
                  where   t2.business_id = t1.business_id)
    :-)
  • SomeoneElse
    SomeoneElse Member Posts: 14,866 Silver Crown
    I have to update amount filed on table1.
    I need to update the amount from table1 to the amount on table3.
    Yeah, just a tad ambiguous there. ;-)
  • 651999
    651999 Member Posts: 19
    thanks.. sql gurus..
  • 651999
    651999 Member Posts: 19
    WHERE EXISTS (select 1
    from table2 t2
    join table3 t3 using (employeeid)
    where t2.business_id = t1.business_id)


    Dumb question . What does the 1 signify? Is that records it will process?
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,492 Red Diamond
    Hi,
    WHERE EXISTS (select 1
    from table2 t2
    join table3 t3 using (employeeid)
    where t2.business_id =
    t1.business_id)

    Dumb question . What does the 1 signify? Is that
    records it will process?
    Not dumb at all!

    An EXISTS sub-query returns TRUE or FALSE, depending on whether the sub-query would return any rows (if it were run by itself) or not.
    The syntax for any query (including an EXISTS sub-query) requires that you have at least one column in the SELECT clause. The "1" is there just to satisfy that requirement. Any other expression would work, too. You could say:
    WHERE EXISTS (select employeeid ...
    I use:
    WHERE EXISTS (select NULL ...
    just as a reminder that nothing is really being selected.
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    create table table1(
    business_id number primary key,
    amount number );
    create table table2(
    business_id number,
    employeeid number,
    primary key (business_id));
    create table table3(
    employeeid number primary key,
    amount number);
    update (select t1.amount as oldVal,t3.amount as newVal
    from table1 t1,table2 t2,table3 t3
    where t1.business_id = t2.business_id
    and t2.employeeid = t3.employeeid)
    set oldVal = newVal;
    Above Update SQL depends what is PrimayKey or UniqueKey.
This discussion has been closed.