Using UPDATE with ROW_NUMBER()

• paul.keohan

  SSC Enthusiast

  Points: 118

  More actions

  November 15, 2016 at 11:45 am

  #315737

  I'm fairly new to SQL and have become completely stuck in trying to do this:

  I want to update a column in Table 1 where there are a number of duplicates. However, I only want to update one of the duplicate rows, and I need to update this from a value in another table.

  Table 1:

  name age city country

  paul 30 dallas

  paul 32 dallas

  jim 40 chicago

  pete 50 london

  phil 45 munich

  jim 28 chicago

  Table 2:

  name city country

  paul dallas USA

  jim chicago USA

  pete london UK

  phil london UK

  E.g. I want to compare the fact that I have an entry for paul, dallas in both tables. I look up the country column in Table 2 and I want to apply this to the country column in Table 1. However, I want to apply it to the first occurrence only so that I end up with this:

  Table 1:

  name age city country

  paul 30 dallas USA

  paul 32 dallas

  jim 40 chicago USA

  pete 50 london UK

  phil 45 munich

  jim 28 chicago

  I accept that this example probably doesn't make a lot of sense but any help on how to achieve what I'm attempting would be very much appreciated. In 'real life' my country column is a value that ends up getting double or tripled when I only want one value in there.

  I think I should be using row_number() somehow but not sure how to apply the correct syntax in an UPDATE.

  Thanks - and please let me know if I can clarify.

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  November 15, 2016 at 12:09 pm

  #1912634

  Use a CTE or a derived table to update it using ROW_NUMBER.

  WITH CTE AS(

  SELECT t1.*,

  t2.country AS t2_country,

  ROW_NUMBER() OVER (PARTITION BY t1.name, t1.city ORDER BY t1.age) rn

  FROM Table1 t1

  JOIN Table2 t2 ON t1.name = t2.name AND t1.city = t2.city

  )

  UPDATE CTE

  SET country = t2_country

  WHERE rn = 1;

  Read more about this option by searching for "Updateable views"

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  November 15, 2016 at 12:13 pm

  #1912635

  paul.keohan (11/15/2016)

  ---

  I'm fairly new to SQL and have become completely stuck in trying to do this:

  I want to update a column in Table 1 where there are a number of duplicates. However, I only want to update one of the duplicate rows, and I need to update this from a value in another table.

  Table 1:

  name age city country

  paul 30 dallas

  paul 32 dallas

  jim 40 chicago

  pete 50 london

  phil 45 munich

  jim 28 chicago

  Table 2:

  name city country

  paul dallas USA

  jim chicago USA

  pete london UK

  phil london UK

  E.g. I want to compare the fact that I have an entry for paul, dallas in both tables. I look up the country column in Table 2 and I want to apply this to the country column in Table 1. However, I want to apply it to the first occurrence only so that I end up with this:

  Table 1:

  name age city country

  paul 30 dallas USA

  paul 32 dallas

  jim 40 chicago USA

  pete 50 london UK

  phil 45 munich

  jim 28 chicago

  I accept that this example probably doesn't make a lot of sense but any help on how to achieve what I'm attempting would be very much appreciated. In 'real life' my country column is a value that ends up getting double or tripled when I only want one value in there.

  I think I should be using row_number() somehow but not sure how to apply the correct syntax in an UPDATE.

  Thanks - and please let me know if I can clarify.

  First, based on sample data and expected results your data is missing any means of identifying which row is first. Remember, a table does not ensure order.

  The following code uses age in ascending order to determine which row of data to update:

  create table #Table1(name varchar(10),age int, city varchar(12), country varchar(3));

  create table #table2(name varchar(10), city varchar(12), country varchar(3));

  insert into #Table1(name,age,city)

  values ('paul', 30, 'dallas')

  ,('paul', 32, 'dallas')

  ,('jim', 40, 'chicago')

  ,('pete', 50, 'london')

  ,('phil', 45, 'munich')

  ,('jim', 28, 'chicago');

  insert into #table2

  values ('paul', 'dallas', 'USA')

  ,('jim', 'chicago', 'USA')

  ,('pete', 'london', 'UK')

  ,('phil', 'london', 'UK');

  with base as (

  select

  name,

  age,

  city,

  country,

  rn = ROW_NUMBER() over (partition by name order by age)

  from

  #Table1

  )

  update b set

  country = t2.country

  from

  base b

  inner join #table2 t2

  on b.name = t2.name and b.city = t2.city

  where

  b.rn = 1;

  select * from #Table1;

  drop table #Table1;

  drop table #table2;

• paul.keohan

  SSC Enthusiast

  Points: 118

  More actions

  November 16, 2016 at 10:14 am

  #1912864

  Thanks for the excellent suggestions. Luis, the CTE option appears to work.