Professional Web Applications Themes

Need help with a Tricky Update statement - Microsoft SQL / MS SQL Server

UPDATE XTargetTable SET total1 = COALESCE(total1,0) + (SELECT SUM(valuetobeadd) FROM XSourceTable WHERE totaltype=1 AND customerid = XTargetTable.customerid), total2 = COALESCE(total2,0) + (SELECT SUM(valuetobeadd) FROM XSourceTable WHERE totaltype=2 AND customerid = XTargetTable.customerid), total3 = COALESCE(total3,0) + (SELECT SUM(valuetobeadd) FROM XSourceTable WHERE totaltype=3 AND customerid = XTargetTable.customerid) -- David Portas ------------ Please reply only to the newsgroup --...

  1. #1

    Default Re: Need help with a Tricky Update statement

    UPDATE XTargetTable
    SET total1 = COALESCE(total1,0) +
    (SELECT SUM(valuetobeadd)
    FROM XSourceTable
    WHERE totaltype=1
    AND customerid = XTargetTable.customerid),
    total2 = COALESCE(total2,0) +
    (SELECT SUM(valuetobeadd)
    FROM XSourceTable
    WHERE totaltype=2
    AND customerid = XTargetTable.customerid),
    total3 = COALESCE(total3,0) +
    (SELECT SUM(valuetobeadd)
    FROM XSourceTable
    WHERE totaltype=3
    AND customerid = XTargetTable.customerid)

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


    David Guest

  2. #2

    Default Need help with a Tricky Update statement

    Hi All,

    Iím trying to do an Update statement, but I figured that it is little bit
    tricky so, I need your help.

    I have two tables: XsourceTable and XTargetTable (The creation script is
    attached.)
    I need to update periodically the XTargetTable with new values I found in
    XsourceTable.

    Please note that the data structure is different, therefore I find it
    difficult to build the right Update statement

    Actually what I need is to update (Sum) the fields: Total2, Total2, Total3
    with the appropriate ValueToBeAdd from XsourceTable according to the
    TotalType field.

    Please find the best query I tried, but with no success since the final
    query result was always the data of the third TotalType (Somehow the other
    two Totals were override).
    Attached also the set of data I used.

    /************************************************** *************************
    ***************************/
    UPDATE TR
    SET TR.Total1 = ISNULL(TR.Total1,0) + CASE WHEN SR.TotalType=1 THEN
    SR.ValueToBeAdd ELSE 0 END,
    TR.Total2 = ISNULL(TR.Total2,0) + CASE WHEN SR.TotalType=2 THEN
    SR.ValueToBeAdd ELSE 0 END,
    TR.Total3 = ISNULL(TR.Total3,0) + CASE WHEN SR.TotalType=3 THEN
    SR.ValueToBeAdd ELSE 0 END
    FROM XTargetTable AS TR INNER JOIN XSourceTable AS SR ON TR.CustomerID =
    SR.CustomerID
    /************************************************** *************************
    ***************************/

    CREATE TABLE [dbo].[XSourceTable] (
    [CustomerID] [int] NOT NULL ,
    [TotalType] [int] NOT NULL ,
    [ValueToBeAdd] [int] NULL
    ) ON [PRIMARY]
    GO

    CREATE TABLE [dbo].[XTargetTable] (
    [CustomerID] [int] NOT NULL ,
    [Total1] [int] NULL ,
    [Total2] [int] NULL ,
    [Total3] [int] NULL
    ) ON [PRIMARY]
    GO


    ALTER TABLE [dbo].[XSourceTable] WITH NOCHECK ADD
    CONSTRAINT [PK_TomerTable] PRIMARY KEY CLUSTERED
    (
    [CustomerID],
    [TotalType]
    ) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[XTargetTable] WITH NOCHECK ADD
    CONSTRAINT [PK_AvnerTable] PRIMARY KEY CLUSTERED
    (
    [CustomerID]
    ) ON [PRIMARY]
    GO
    /************************************************** *************************
    ***************************/

    XSourceTable Data:
    1,1,2
    1,2,3
    1,3,4
    2,1,6
    2,2,7
    2,3,8

    XTargetTable Data:
    1, NULL, NULL, NULL
    2, NULL, NULL, NULL

    /************************************************** *************************
    ***************************/


    Thanks in advanced!!,
    Tomer



    Tomer Guest

  3. #3

    Default Re: Need help with a Tricky Update statement

    Tomer
    Run it
    SELECT XSourceTable.CustomerID,SUM(CASE WHEN TotalType=1 THEN ValueToBeAdd
    END)Total1,
    SUM(CASE WHEN TotalType=2 THEN
    ValueToBeAdd END)Total2,
    SUM(CASE WHEN TotalType=3 THEN
    ValueToBeAdd END)Total3
    FROM XSourceTable JOIN XTargetTable
    ON XSourceTable.CustomerID=XTargetTable.CustomerID
    GROUP BY XSourceTable.CustomerID




    "Tomer" <com> wrote in message
    news:phx.gbl... 
    /************************************************** ************************* 

    /************************************************** ************************* 
    /************************************************** ************************* 
    /************************************************** ************************* 


    Uri Guest

  4. #4

    Default Re: Need help with a Tricky Update statement

    I probably need to clarify myself more:

    The Target table is some kind of a billing table I already have in my
    database.

    The Source table is actually a result set of a stored procedure, collecting
    data from another database periodically!.

    So, I need to refresh my totals (all of them) with this subtotal data
    retrieved from the other database...

    The Source table always produce new data that wasn't collected before.



    Does this simplify the things somehow?



    Thanks again

    Tomer



    "David Portas" <org> wrote in message
    news:phx.gbl... 


    Tomer Guest

  5. #5

    Default Re: Need help with a Tricky Update statement

    Guys, I really appreciate your help, but a Select statement cannot help me,
    since I have to update an existing table regularly, based on result of
    another procedure.

    It is me to blame, since I confused you with two tables instead of telling
    you the entire story

    Is it still possible?

    Thanks!,
    Tomer

    "Uri Dimant" <co.il> wrote in message
    news:phx.gbl... 
    ValueToBeAdd [/ref]
    bit [/ref]
    in [/ref]
    Total3 [/ref]
    other 
    >[/ref]
    /************************************************** ************************* [/ref]
    TR.CustomerID 
    >[/ref]
    /************************************************** ************************* 
    >[/ref]
    /************************************************** ************************* 
    >[/ref]
    /************************************************** ************************* 
    >
    >[/ref]


    Tomer Guest

  6. #6

    Default Re: Need help with a Tricky Update statement

    Thanks David!


    "David Portas" <org> wrote in message
    news:phx.gbl... 


    Tomer Guest

Similar Threads

  1. More than one update in a statement?
    By Jon in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: August 20th, 05:50 PM
  2. Tricky Update
    By Steve in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: August 15th, 10:21 PM
  3. update statement
    By John Bell in forum Microsoft SQL / MS SQL Server
    Replies: 6
    Last Post: August 9th, 08:25 PM
  4. Help with Tricky UPDATE sql statements.
    By Lam in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 22nd, 07:05 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