1

I am looking to update the first record when a duplicate is found in a table.

CREATE TABLE tblauthor 
    (
     Col1 varchar(20), 
     Col2 varchar(30)   
    );

CREATE TABLE tblbook 
    (
     Col1 varchar(20), 
     Col2 varchar(30),
     Col3 varchar(30)     
    );

INSERT INTO tblAuthor
(Col1,Col2)
VALUES
('1', 'John'),
('2', 'Jane'),
('3', 'Jack'),
('4', 'Joe');

INSERT INTO tblbook
(Col1,Col2,Col3)
VALUES
('1', 'John','Book 1'),
('2', 'John','Book 2'),
('3', 'Jack','Book 1'),
('4', 'Joe','Book 1'),
('5', 'Joe','Book 2'),
('6', 'Jane','Book 1'),
('7', 'Jane','Book 2');

The update result I want to accomplish should update the records as follows. I would like tblbook.col3 = 1st.

select * from tblbook

('1', 'John','1st'),
('3', 'Jack','1st'),
('4', 'Joe','1st'),
('6', 'Jane','1st');

Can't seem to even get this done with distinct.

eripey
  • 355
  • 1
  • 2
  • 13
  • sql fiddle [link]http://sqlfiddle.com/#!3/4b6c8/1 – eripey Aug 18 '14 at 20:54
  • 1
    What means you want to update, why does your desired result only contain unique records? Do you instead want to delete the others? I would also change your sample data above like you did in sql-fiddle since that does currently not work. – Tim Schmelter Aug 18 '14 at 21:00

2 Answers2

3

Use ROW_NUMBER to assign a number to each row grouped by the Author's name (col2) and then update the ones that have a number of 1

update tblbook set col3 = '1st'
where col1 in(
    select
        col1
    from (
        select 
            tblbook.col1,
            tblbook.col2,
            tblbook.col3,
            ROW_NUMBER() OVER (PARTITION BY tblbook.Col2 order by tblbook.col1) as rownum
        from tblbook 
            left outer join tblauthor on tblbook.col2 = tblauthor.col2
    ) [t1]
    where [t1].rownum = 1
)

Fiddle: http://sqlfiddle.com/#!3/4b6c8/20/0

Dave Zych
  • 21,581
  • 7
  • 51
  • 66
0

If you want to update tblbook so the third column is '1st' on duplicates, then you can easily do so with an updatable CTE:

with toupdate as (
      select tbl2.*, row_number() over (partition by col2 order by col1) as seqnum
      from tbl2
     )
update toupdate
    set col3 = '1st'
    where seqnum = 1;

This is the closest that I can come to understanding what you really want.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786