Updating multiple values from 1 table to another

  • How can I update multiple values in 1 table based on the values from another table, do i need to use cursor ? or any loop ?

    every time there may be different values in temptable or sometime there may be no values at all. in this particular example there are three columns for which I need to update the values in temptable2

    from temptable1 i.e Name, Phone and Address

    I know when updating we need to give field names and field values however in this case this will be coming from another table at runtime.

    Can I use any loop to execute

    Update dbo.temptable set ColumnValue = val1

    where ColumnName = val2

    For you to see what I have please use following code

    drop table #temptable

    Create Table #temptable

    ( id int not null primary key,

    val1 varchar(50),

    val2 varchar(50))

    insert into #temptable(id,val1,val2)

    values

    (1,'Name','John'),

    (2,'Phone','1111111'),

    (3,'Address','Newton Australia')

    select * from #temptable

    drop table #temptable2

    Create Table #temptable2

    ( columnname varchar(50),

    columnvalue varchar(50))

    insert into #temptable2(columnname)

    values

    ('Address'),

    ('idnumber'),

    ('Phone'),

    ('OfficeNumber'),

    ('Surname')

    select * from #temptable2

    Umar Memon

  • Pls explain more clearly.

  • Let us know the expected o/p

  • Currently all fields in Temptable 2 are Null.

    Output of Temptable 2 would be after an update statement would be as follows

    Address = Newton Australia

    Phone = 111111

    idnumber = NULL

    OfficeNumber = NULL

    Surname = NULL

    Currently all fields in Temptable 2 are Null.

    these values for phone and address are going to be taken from Temp Table 1 as only these 2 column names are present in Table 1. rest of fields in temp table 2 will be null as it is.

    I hope now you understand what I mean, let me know if any further explanation is required

  • I think this is ur expecting result

    UPDATE #temptable2

    SET columnvalue =CAse When columnname ='Address' THEN (SELECT VAl2 from #temptable WHERE val1 ='Address')

    When columnname ='idnumber' THEN (SELECT VAl2 from #temptable WHERE val1 ='idnumber')

    When columnname ='Phone' THEN (SELECT VAl2 from #temptable WHERE val1 ='Phone')

    When columnname ='OfficeNumber' THEN (SELECT VAl2 from #temptable WHERE val1 ='OfficeNumber')

    When columnname ='Surname' THEN (SELECT VAl2 from #temptable WHERE val1 ='Surname')

    ELSE NULL END

    AddressNewton Australia

    idnumberNULL

    Phone1111111

    OfficeNumberNULL

    SurnameNULL

  • Are you implying that you need a Trigger?

    here is something that I worked up. Hope it helps:

    --Creating Tables

    Create Table Ex

    (id int not null primary key Identity(1,1), -----=====Identity Column is required for the trigger to work

    val1 varchar(50),

    val2 varchar(50) )

    Create Table Ex2

    (columnname varchar(50),

    columnvalue varchar(50) )

    --Inserting Sample Data into tables

    Insert Into Ex

    Select 'Name', 'John'

    Union ALL

    Select 'Phone', '1111111'

    Union ALL

    Select 'Address', 'Newton Australia'

    Insert Into Ex2 (columnname)

    values

    ('Address'),

    ('idnumber'),

    ('Phone'),

    ('OfficeNumber'),

    ('Surname')

    --Create Trigger

    Alter Trigger Tr

    On Ex

    For Insert

    As

    Begin

    Declare @colname varchar(50), @value varchar(50), @sql varchar(max)

    Select @colname = Val1, @value = val2 From Ex

    Where Id IN (Select Id From Inserted)

    If(@Colname IN ('Address','Phone'))

    Begin

    Select @sql = 'Update Ex2 Set ColumnValue = '+@value+' Where ColumnName = '''+@colname+''';'

    Execute (@sql)

    End

    Else

    Begin

    Return

    End

    End

    --Test Execution Of trigger

    Insert Into Ex

    Select 'Phone', '2222222'

    Select * From Ex

    Select * From Ex2

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • vinu512 (5/9/2012)


    Are you implying that you need a Trigger?

    here is something that I worked up. Hope it helps:

    --Creating Tables

    Create Table Ex

    (id int not null primary key Identity(1,1), -----=====Identity Column is required for the trigger to work

    val1 varchar(50),

    val2 varchar(50) )

    Create Table Ex2

    (columnname varchar(50),

    columnvalue varchar(50) )

    --Inserting Sample Data into tables

    Insert Into Ex

    Select 'Name', 'John'

    Union ALL

    Select 'Phone', '1111111'

    Union ALL

    Select 'Address', 'Newton Australia'

    Insert Into Ex2 (columnname)

    values

    ('Address'),

    ('idnumber'),

    ('Phone'),

    ('OfficeNumber'),

    ('Surname')

    --Create Trigger

    Alter Trigger Tr

    On Ex

    For Insert

    As

    Begin

    Declare @colname varchar(50), @value varchar(50), @sql varchar(max)

    Select @colname = Val1, @value = val2 From Ex

    Where Id IN (Select Id From Inserted)

    If(@Colname IN ('Address','Phone'))

    Begin

    Select @sql = 'Update Ex2 Set ColumnValue = '+@value+' Where ColumnName = '''+@colname+''';'

    Execute (@sql)

    End

    Else

    Begin

    Return

    End

    End

    --Test Execution Of trigger

    Insert Into Ex

    Select 'Phone', '2222222'

    Select * From Ex

    Select * From Ex2

    That trigger will not work when there are multiple rows in a single insert. Consider what would happen if you ran the following:

    Insert Into Ex

    Select 'Phone', '2222222' union all

    select 'Phone1', '321321'

    And why the dynamic sql? I don't think that is needed in this case.

    I did not rewrite this trigger due to a lack of information from the OP. If they come back and say that a trigger is what they need then I will happy to help.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sean Lange (5/9/2012)


    vinu512 (5/9/2012)


    Are you implying that you need a Trigger?

    here is something that I worked up. Hope it helps:

    --Creating Tables

    Create Table Ex

    (id int not null primary key Identity(1,1), -----=====Identity Column is required for the trigger to work

    val1 varchar(50),

    val2 varchar(50) )

    Create Table Ex2

    (columnname varchar(50),

    columnvalue varchar(50) )

    --Inserting Sample Data into tables

    Insert Into Ex

    Select 'Name', 'John'

    Union ALL

    Select 'Phone', '1111111'

    Union ALL

    Select 'Address', 'Newton Australia'

    Insert Into Ex2 (columnname)

    values

    ('Address'),

    ('idnumber'),

    ('Phone'),

    ('OfficeNumber'),

    ('Surname')

    --Create Trigger

    Alter Trigger Tr

    On Ex

    For Insert

    As

    Begin

    Declare @colname varchar(50), @value varchar(50), @sql varchar(max)

    Select @colname = Val1, @value = val2 From Ex

    Where Id IN (Select Id From Inserted)

    If(@Colname IN ('Address','Phone'))

    Begin

    Select @sql = 'Update Ex2 Set ColumnValue = '+@value+' Where ColumnName = '''+@colname+''';'

    Execute (@sql)

    End

    Else

    Begin

    Return

    End

    End

    --Test Execution Of trigger

    Insert Into Ex

    Select 'Phone', '2222222'

    Select * From Ex

    Select * From Ex2

    That trigger will not work when there are multiple rows in a single insert. Consider what would happen if you ran the following:

    Insert Into Ex

    Select 'Phone', '2222222' union all

    select 'Phone1', '321321'

    And why the dynamic sql? I don't think that is needed in this case.

    I did not rewrite this trigger due to a lack of information from the OP. If they come back and say that a trigger is what they need then I will happy to help.

    Nice catch Sean.

    The above trigger is just a general idea which the OP can use to get to where he wants.

    When I read the OP's post, the first thought that came to my mind is that the OP is trying to insert values from a front end application, maybe using Drop Down boxes. I could be wrong, its just a Hunch I had. I could be right as well as the OP wasn't very descriptive about his requirement.

    I started working with the Hunch and came up with the Trigger. If the OP inserts from the application, the following might not happen:

    Insert Into Ex

    Select 'Phone', '2222222' union all

    select 'Phone1', '321321'

    Because, only one row will be inserted at a time.

    And why the dynamic sql? I don't think that is needed in this case.

    Anyways, its just a Hunch. I could be wrong.

    Going by the OP's first post, he was unsure. This was just my try at getting him up and running with some possibilities.

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • vinu512 (5/9/2012)


    Sean Lange (5/9/2012)


    vinu512 (5/9/2012)


    Are

    Nice catch Sean.

    The above trigger is just a general idea which the OP can use to get to where he wants.

    I was just pointing out that the general idea needed to be tweaked to handle multiple rows.

    When I read the OP's post, the first thought that came to my mind is that the OP is trying to insert values from a front end application, maybe using Drop Down boxes. I could be wrong, its just a Hunch I had. I could be right as well as the OP wasn't very descriptive about his requirement.

    I started working with the Hunch and came up with the Trigger. If the OP inserts from the application, the following might not happen:

    Insert Into Ex

    Select 'Phone', '2222222' union all

    select 'Phone1', '321321'

    You have to assume that anything in a trigger will have multiple rows. Just because it is designed to be used with an application there will be a point where some sort of CRUD operation will involve multiple rows. Maybe some data was deleted accidentally or whatever. That situation ALWAYS happens at some point and any triggers MUST be able to handle it.

    There was a story in another thread a few months ago by one of the regulars about a company that went belly up because their triggers couldn't handle multiple rows.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sean Lange (5/10/2012)

    There was a story in another thread a few months ago by one of the regulars about a company that went belly up because their triggers couldn't handle multiple rows.

    Sean - I would really like to read that article if you could find it.

    We have an application that we support here and it ran up against just this issue. I hit upon the fact that ALL triggers should be able to support multiple rows (I call them bulk triggers) and not just single rows as a common sense thing without even the need to seek advice. Ultimately I went back through that app and rewrote every single bloody trigger in the thing to handle bulk ops.

    The article would be useful to me to impress this point upon others not as enlightened.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Is this better Swan?

    --Creating Tables

    Create Table Ex

    (id int not null primary key Identity(1,1), -----=====Identity Column is required for the trigger to work

    val1 varchar(50),

    val2 varchar(50) )

    Create Table Ex2

    (columnname varchar(50),

    columnvalue varchar(50) )

    --Inserting Sample Data into tables

    Insert Into Ex

    Select 'Name', 'John'

    Union ALL

    Select 'Phone', '1111111'

    Union ALL

    Select 'Address', 'Newton Australia'

    Insert Into Ex2 (columnname)

    values

    ('Address'),

    ('idnumber'),

    ('Phone'),

    ('OfficeNumber'),

    ('Surname')

    --Create Trigger

    Alter Trigger Tr

    On Ex

    For Insert

    As

    Begin

    Declare @cnt int

    Declare @colname varchar(50), @value varchar(50), @sql varchar(max), @sql1 varchar(max)

    Declare @tbl Table(id int, val1 varchar(50), val2 varchar(50) )

    Insert Into @tbl

    Select id, val1, val2 From Inserted

    Select @cnt = Count(*) From Ex as a JOIN Inserted as i ON a.id = i.id

    Where i.val1 IN ('Address', 'Phone')

    While(@cnt > 0)

    Begin

    Select @colName = i.val1, @value = i.val2 From Ex as a JOIN @tbl as i ON a.id = i.id

    Where i.id IN (Select Top 1 id From @tbl) AND i.val1 IN ('Address', 'Phone')

    Select @sql = 'Update Ex2 Set ColumnValue = '''+@value+''' Where ColumnName = '''+@colname+''';'

    Execute (@sql)

    Delete From @tbl Where Id IN (Select Top 1 id From @tbl)

    Select @cnt = @cnt - 1

    End

    End

    --Test Execution Of trigger

    Insert Into Ex

    Select 'Name', 'Allen'

    Union ALL

    Select 'Phone', '2222222'

    Union ALL

    Select 'Address', 'Australia'

    Select * From Ex

    Select * From Ex2

    This works for multiple row inserts.

    It could be better....but this is just my "raw" attempt at it.

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • Vinus - I think there's a simpler solution with no need for dynamic SQL in the trigger.

    --drop table temptable

    Create Table temptable (id int not null primary key, val1 varchar(50), val2 varchar(50))

    GO

    --drop table temptable2

    Create Table temptable2 (columnname varchar(50), columnvalue varchar(50))

    GO

    CREATE TRIGGER OnInsertTempTable ON Temptable AFTER INSERT

    AS

    BEGIN

    SET NOCOUNT ON;

    UPDATE t

    SET columnvalue = val2

    FROM temptable2 t

    INNER JOIN INSERTED ON columnname = val1

    END

    GO

    insert into temptable2(columnname)

    SELECT 'Address'

    UNION ALL SELECT 'idnumber'

    UNION ALL SELECT 'Phone'

    UNION ALL SELECT 'OfficeNumber'

    UNION ALL SELECT 'Surname'

    select * from temptable2

    insert into temptable(id,val1,val2)

    SELECT 1,'Name','John'

    UNION ALL SELECT 2,'Phone','1111111'

    UNION ALL SELECT 3,'Address','Newton Australia'

    select * from temptable

    SELECT * FROM temptable2

    DROP TRIGGER OnInsertTempTable

    drop table temptable

    drop table temptable2


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • dwain.c (5/11/2012)


    Vinus - I think there's a simpler solution with no need for dynamic SQL in the trigger.

    --drop table temptable

    Create Table temptable (id int not null primary key, val1 varchar(50), val2 varchar(50))

    GO

    --drop table temptable2

    Create Table temptable2 (columnname varchar(50), columnvalue varchar(50))

    GO

    CREATE TRIGGER OnInsertTempTable ON Temptable AFTER INSERT

    AS

    BEGIN

    SET NOCOUNT ON;

    UPDATE t

    SET columnvalue = val2

    FROM temptable2 t

    INNER JOIN INSERTED ON columnname = val1

    END

    GO

    insert into temptable2(columnname)

    SELECT 'Address'

    UNION ALL SELECT 'idnumber'

    UNION ALL SELECT 'Phone'

    UNION ALL SELECT 'OfficeNumber'

    UNION ALL SELECT 'Surname'

    select * from temptable2

    insert into temptable(id,val1,val2)

    SELECT 1,'Name','John'

    UNION ALL SELECT 2,'Phone','1111111'

    UNION ALL SELECT 3,'Address','Newton Australia'

    select * from temptable

    SELECT * FROM temptable2

    DROP TRIGGER OnInsertTempTable

    drop table temptable

    drop table temptable2

    You've nailed it Dwain. All this time I was thinking that I have done this before but wasn't able to remember how I did it. I tried everything except your logic.

    Nice One!! 🙂

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • vinu512 (5/11/2012)

    You've nailed it Dwain. All this time I was thinking that I have done this before but wasn't able to remember how I did it. I tried everything except your logic.

    Nice One!! 🙂

    Thanks Vinus! I'll take that as a compliment. 😀

    Note that I have a sneaking suspicion that this can also be done with a MERGE statement and no trigger. Unfortunately I am not in a position to try that at the moment.

    Maybe later when I'm in my SQL 2008 playground.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • dwain.c (5/11/2012)


    vinu512 (5/11/2012)

    You've nailed it Dwain. All this time I was thinking that I have done this before but wasn't able to remember how I did it. I tried everything except your logic.

    Nice One!! 🙂

    Thanks Vinus! I'll take that as a compliment. 😀

    Note that I have a sneaking suspicion that this can also be done with a MERGE statement and no trigger. Unfortunately I am not in a position to try that at the moment.

    Maybe later when I'm in my SQL 2008 playground.

    I didn't really look back through very closely but I think you are right about using MERGE Dwain. Of course if you stick with the trigger methodology the one you wrote looks nice and clean.

    I can help with the MERGE if the OP is interested. Otherwise something like what Dwain posted should work nicely.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply