Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171

    Unanswered: Multiple column updates

    Hi,

    I have a table T1 with columns
    (t1_key
    val1
    val2
    val3)

    Anothe table T2 with columns
    (t2_key
    val1
    val2
    val3)


    I want to update all rows in table t1 with values from t2 (for same key)

    Using SQL, one option I have is:
    update t1
    set val1 = (select val1 from t2 where t2_key = t1_key),
    val2 = (select val2 from t2 where t2_key = t1_key),
    val3 = (select val3 from t2 where t2_key = t1_key);

    This query means querying t2 three times, once for each column.

    Is this optimised query or is there any other way to do this in SQL?

    Regards,
    Oracle can do wonders !

  2. #2
    Join Date
    Dec 2003
    Posts
    18

    Re: Multiple column updates

    Hi,
    Use this:-
    update t1 set (val1,val2,val3)=(select t2.val1,t2.val2,t2.val3 from t2 where t2_key=t1_key);





    Originally posted by cmasharma
    Hi,

    I have a table T1 with columns
    (t1_key
    val1
    val2
    val3)

    Anothe table T2 with columns
    (t2_key
    val1
    val2
    val3)


    I want to update all rows in table t1 with values from t2 (for same key)

    Using SQL, one option I have is:
    update t1
    set val1 = (select val1 from t2 where t2_key = t1_key),
    val2 = (select val2 from t2 where t2_key = t1_key),
    val3 = (select val3 from t2 where t2_key = t1_key);

    This query means querying t2 three times, once for each column.

    Is this optimised query or is there any other way to do this in SQL?

    Regards,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •