locked
same table update and insert RRS feed

  • Question

  •  

    Hi,

     

    Can any one help me how to get this query....

     

    This is my sample data..

     

    LID Rate RID

    1    10    111

    2    20    123

    3    40    111

    4    30    312

    5    60    123

     

    Result set should be like this on the same table....

     

    The result table will be like this

     

    LID    Rate    RID

    1       10       111

    2       20       123

    3       40       111

    4       30       312

    5       60       123

    111    50       322

    123    80       211

    312    30       322

     

    The above scenario is, i have to group the RID's and sum of the rates and place in the same table as above what i shown.

    Daily I will get some new records and some changed records. i have to update if there is any change records and insert if there are any new records in the same table.

     

    Suppose next day with the same LID 111 i got the record and the rate has chnaged to 60, then i have to update at the 111 row and if i got the new record with the new LID some 413,i have to insert that record into the same table.

     

    I will get those records some other table. The other table contains the LID and rates and corresponding RID include date also. I will insert records into the above table day by day.

     

    Can i get query for this.

     

    Regards

    sqldev

    Thursday, October 2, 2008 2:31 AM

Answers

  • Something similar to this may help.  Do the updates first then the inserts.

     

    I assumed the LID was the Primary Key, and that you would be updating both the rate and rid

     

    Code Snippet

    -- Main Table -- Asuming LID is the PK

    CREATE TABLE #T

    (

    LID INT

    ,RATE INT

    ,RID INT

    )

    INSERT INTO #T VALUES(1, 10, 111)

    INSERT INTO #T VALUES(2, 20, 123)

    INSERT INTO #T VALUES(3, 40, 111)

    INSERT INTO #T VALUES(4, 30, 312)

    INSERT INTO #T VALUES(5, 60, 123)

    GO

    -- Te table with the updates and inserts

    CREATE TABLE #U

    (

    LID INT

    ,RATE INT

    ,RID INT

    )

    INSERT INTO #U VALUES(1, 110, 111)

    INSERT INTO #U VALUES(2, 120, 123)

    INSERT INTO #U VALUES(3, 140, 111)

    INSERT INTO #U VALUES(4, 130, 312)

    INSERT INTO #U VALUES(5, 160, 123)

    INSERT INTO #U VALUES(111, 50, 322)

    INSERT INTO #U VALUES(123, 80, 211)

    INSERT INTO #U VALUES(312, 30, 322)

    GO

    -- First Update

    UPDATE t

    SET t.Rate = u.Rate

    ,t.RID = u.RID

    FROM #T t

    INNER JOIN #U u

    ON t.LID = u.LID

    Go

    INSERT INTO #T

    SELECT LID, RATE, RID

    FROM #U

    WHERE NOT EXISTS

    (

    SELECT *

    FROM #T

    WHERE #T.LID = #U.LID

    )

    GO

     

    GO

    SELECT * FROM #T

    Go

     

    DROP TABLE #T

    DROP TABLE #U

     

     

    Thursday, October 2, 2008 2:48 AM

All replies

  • Something similar to this may help.  Do the updates first then the inserts.

     

    I assumed the LID was the Primary Key, and that you would be updating both the rate and rid

     

    Code Snippet

    -- Main Table -- Asuming LID is the PK

    CREATE TABLE #T

    (

    LID INT

    ,RATE INT

    ,RID INT

    )

    INSERT INTO #T VALUES(1, 10, 111)

    INSERT INTO #T VALUES(2, 20, 123)

    INSERT INTO #T VALUES(3, 40, 111)

    INSERT INTO #T VALUES(4, 30, 312)

    INSERT INTO #T VALUES(5, 60, 123)

    GO

    -- Te table with the updates and inserts

    CREATE TABLE #U

    (

    LID INT

    ,RATE INT

    ,RID INT

    )

    INSERT INTO #U VALUES(1, 110, 111)

    INSERT INTO #U VALUES(2, 120, 123)

    INSERT INTO #U VALUES(3, 140, 111)

    INSERT INTO #U VALUES(4, 130, 312)

    INSERT INTO #U VALUES(5, 160, 123)

    INSERT INTO #U VALUES(111, 50, 322)

    INSERT INTO #U VALUES(123, 80, 211)

    INSERT INTO #U VALUES(312, 30, 322)

    GO

    -- First Update

    UPDATE t

    SET t.Rate = u.Rate

    ,t.RID = u.RID

    FROM #T t

    INNER JOIN #U u

    ON t.LID = u.LID

    Go

    INSERT INTO #T

    SELECT LID, RATE, RID

    FROM #U

    WHERE NOT EXISTS

    (

    SELECT *

    FROM #T

    WHERE #T.LID = #U.LID

    )

    GO

     

    GO

    SELECT * FROM #T

    Go

     

    DROP TABLE #T

    DROP TABLE #U

     

     

    Thursday, October 2, 2008 2:48 AM
  • Thanks alot, this one is useful for me

     

    Thursday, October 9, 2008 6:36 PM