Professional Web Applications Themes

UPDATE from Two Tables Into One Table - Microsoft SQL / MS SQL Server

I have this problem, which for now is NOT something I have created, yet. However the problem remains. We have to UPDATE TABLE A - TABLES - TABLE A Columns xCode, yCode, EndValue TABLE B Columns yCode, EndValue TABLE C Columns yCode, EndValue Condtions for UPDATE IF TABLE A.xCode starts with a 1 then UPDATE TABLE A.EndValue = TABLE C.EndValue WHERE TABLE A.yCode = TABLE C.yCode Otherwise UPDATE TABLE A.EndValue = TABLE B.EndValue WHERE TABLE A.yCode = TABLE B.yCode So my question is how would I perform this type of update ? PLEASE NOTE ( before someone says - "Put ...

  1. #1

    Default UPDATE from Two Tables Into One Table

    I have this problem, which for now is NOT something I have
    created, yet. However the problem remains.

    We have to UPDATE TABLE A -

    TABLES -

    TABLE A Columns xCode, yCode, EndValue

    TABLE B Columns yCode, EndValue

    TABLE C Columns yCode, EndValue

    Condtions for UPDATE

    IF TABLE A.xCode starts with a 1
    then UPDATE TABLE A.EndValue = TABLE C.EndValue
    WHERE TABLE A.yCode = TABLE C.yCode

    Otherwise UPDATE TABLE A.EndValue = TABLE B.EndValue
    WHERE TABLE A.yCode = TABLE B.yCode

    So my question is how would I perform this type of update ?

    PLEASE NOTE ( before someone says - "Put everything in one
    table and get rid of TABLE B and TABLE C " )

    I am dealing with a legacy system which is still in
    operation and there are NO plans for any changes. And thus,
    putting everything into ONE table IS NOT an option and should
    NOT be considered. ( There is more to real life than just the
    data. There is a political aspect to many things in IT ) Since
    this a real life problem dealing with multiple DB from different
    clients this is not something that can be change.

    I know I could create some sort of loop to do this. I would like
    some assistance in coming up with something more "elegent".

    Would anyone have any thoughts ?


    Jon Spartan
    Jon Guest

  2. #2

    Default Re: UPDATE from Two Tables Into One Table

    Assuming that xcode is CHAR/VARCHAR:

    UPDATE A
    SET endvalue =
    CASE LEFT(xcode,1) WHEN '1'
    THEN
    (SELECT endvalue
    FROM C
    WHERE ycode = A.ycode)
    ELSE
    (SELECT endvalue
    FROM B
    WHERE ycode = A.ycode)
    END

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

  3. #3

    Default Re: UPDATE from Two Tables Into One Table

    UPDATE A
    SET endvalue =
    CASE LEFT(xcode,1) WHEN '1'
    THEN
    COALESCE(
    (SELECT endvalue
    FROM C
    WHERE ycode = A.ycode),
    (SELECT endvalue
    FROM C
    WHERE ycode = '000'))
    ELSE
    COALESCE(
    (SELECT endvalue
    FROM B
    WHERE ycode = A.ycode),
    (SELECT endvalue
    FROM B
    WHERE ycode = 'DEFAULT'))
    END

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

  4. #4

    Default Re: UPDATE from Two Tables Into One Table

    AGAIN MY THANKS,

    It works and is very cool. It is easy to make someone very happy eh ?

    A question....

    SHould other conditions come up may I conclude it is possible just to
    add "sections" such as

    (SELECT endvalue
    FROM TABLE B
    WHERE ycode = ycode AND somethin g= 'DEFAULT' )

    and so one with each SELECT within the COALESCE section.

    Yes ?

    Jon

    On Tue, 22 Jul 2003 14:58:01 -0400, Jon Spartan <net>
    wrote:
     

    Jon Guest

  5. #5

    Default Re: UPDATE from Two Tables Into One Table

    You can add additional scalar queries inside the COALESCE functions but the
    value that gets assigned is the first non-NULL query result determined by
    the order within the COALESCE function.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

Similar Threads

  1. Replies: 0
    Last Post: October 10th, 07:37 PM
  2. How many dataBase tables can a dataSet table update via dataGrid?
    By Gary Frank in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: August 27th, 01:15 PM
  3. trying to update a table after making a join select query on two tables
    By rob merritt in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: March 1st, 10:33 PM
  4. Replies: 0
    Last Post: August 20th, 02:46 PM
  5. UPDATE MULTIPLE ROWS IN ONE TABLE OBTAINING SUMS FROM ANOTHER TABLE
    By Jon Spartan in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 9th, 04:48 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139