SQL Server Performance

Question about updating a table using data from another table

Discussion in 'SQL Server 2005 General Developer Questions' started by rmills, Jan 23, 2008.

  1. rmills New Member

    Let's say I have table A with ID as it's primary key field. Then I have table B which has the data I want to copy over to table A.
    What happens if Table B has more than 1 record for a specific ID but different values in the other columns? Which record will ultimately win? I mean what logic does the engine use to determine which one will be updated last?
  2. ndinakar Member

    I think it updates with the first record that matches. There is no guarantee though.
  3. dineshasanka Moderator

    You will get an error
    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
  4. ndinakar Member

    [quote user="dineshasanka"]
    You will get an error
    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.[/quote]
    It will depend on the way the query was written. If the query was written as UPDATE.. SET = (SELECT ...) then yes. IF the query uses a JOIN then not.
  5. satya Moderator

    Rob
    In order to avoid confusion and discussion, can you post the query used in your example?
  6. rmills New Member

    Wow a lot of responses since I last checked.
    First, I'm not using a subquery so there's no error. I'm actually joining the tables. I'm responsible for loading data from another (progress) database into my sql server 2005 database. The progress side is giving me all the changes in one recordset with an extra column to indicate whether it's an insert, update or delete. The inserts and deletes are easy. It's the updates I'm concerned about. If let's say in between the times I retrieve the data there is more than one update to a single record. How will that be treated in the following statement?
    update dbo.activity
    set action = pa.action, last_change_date = pa.last_change_date
    from dbo.activity a inner join progress_activity pa on a.activity_id = pa.activity_id
    The primary key of my table (activity) is activity_id. The primary key of progress_activity is activity_id, last_change_date

  7. Madhivanan Moderator

    As said, it would update first matched record
    Why dont you test and and confirm?
  8. Adriaan New Member

    Disagree: the update will take into account each matching row, and the end result will be from the last matching row that happens to be found ...
    In the following script, change the order of the insert statements, and check the result:
    create table #t1 (i int, a varchar(1))
    create table #t2 (i int, a varchar(1))
    insert into #t1 values (1, null)
    insert into #t2 values (1, 'a')
    insert into #t2 values (1, 'b')
    insert into #t2 values (1, 'c')
    update #t1 set #t1.a = #t2.a
    from #t1 inner join #t2 on #t1.i = #t2.i
    select * from #t1
    drop table #t1
    drop table #t2
  9. Madhivanan Moderator

    I think this is another behaviour change in SQL Server 2005 [:)]
  10. Adriaan New Member

    Don't you love it when they start behaving consistently? It's so refreshingly unexpected!
  11. ndinakar Member

    [quote user="Adriaan"]
    Disagree: the update will take into account each matching row, and the end result will be from the last matching row that happens to be found ...
    In the following script, change the order of the insert statements, and check the result:
    create table #t1 (i int, a varchar(1))
    create table #t2 (i int, a varchar(1))
    insert into #t1 values (1, null)
    insert into #t2 values (1, 'a')
    insert into #t2 values (1, 'b')
    insert into #t2 values (1, 'c')
    update #t1 set #t1.a = #t2.a
    from #t1 inner join #t2 on #t1.i = #t2.i
    select * from #t1
    drop table #t1
    drop table #t2
    [/quote]
    Adriann
    You mean the first record? I ran your code and I always see 'a' in t1. I changed the order to put 'b' first in the Insert and I see the value of 'b' in the table t1.
  12. Adriaan New Member

    I was testing in SQL 2000, where this script updates to the last value inserted into the temp table.
  13. Madhivanan Moderator

    [quote user="ndinakar"]
    [quote user="Adriaan"]
    Disagree: the update will take into account each matching row, and the end result will be from the last matching row that happens to be found ...
    In the following script, change the order of the insert statements, and check the result:
    create table #t1 (i int, a varchar(1))
    create table #t2 (i int, a varchar(1))
    insert into #t1 values (1, null)
    insert into #t2 values (1, 'a')
    insert into #t2 values (1, 'b')
    insert into #t2 values (1, 'c')
    update #t1 set #t1.a = #t2.a
    from #t1 inner join #t2 on #t1.i = #t2.i
    select * from #t1
    drop table #t1
    drop table #t2
    [/quote]
    Adriann
    You mean the first record? I ran your code and I always see 'a' in t1. I changed the order to put 'b' first in the Insert and I see the value of 'b' in the table t1.
    [/quote]
    As I said, it is behaviour change in SQL Server 2005
  14. rmills New Member

    [quote user="Madhivanan"]
    As said, it would update first matched record
    Why dont you test and and confirm?
    [/quote]
    I have run some tests and it looked like it used a consistent pattern in that it was updated with the first record based on a specific sort order. So then my next question would be are we able to control the sort order? Obviously we can declare a sort order in the update statement. Perhaps I can use a subquery which specifies a sort order. I'll test that and let you know.

Share This Page