Update one from two of duplicates in Firebird – mo

Question

How do I update one from two or more duplicate rows? I want to keep one and update the others with a new value.

Simple example tables:

from

one|two|three
----------
1|milk|water
1|milk|water

to

one|two|three
----------
1|milk|water
1|milk|sugar

Answer 1

http://www.ibexpert.net/ibe/index.php?n=Doc.TheMysteryOfRDBDBKEY

Select *, RDB$DB_KEY from MyExampleTable;

then

Update MyExampleTable 
  Set Column=Value
  Where RDB$DB_KEY=xxxxx;

---

Another approach would be using Stored Procedure (or Execute Block) and use SQL Cursor variables. But that would require careful loop management, so you would skip one row and change 2nd, third and so on.

https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-psql-coding.html#fblangref25-psql-tbl-declare-cursor

Also see examples for UPDATE ... WHERE CURRENT OF ... at https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-dml-update.html#fblangref25-dml-tbl-update

---

But probably most proper way would be to add unique Primary Key column to that table and then use that unique numeric ID

Answer 2

Don't know which version of Firebird you are using (analytic functions are supported as of version 3.0) and if the following syntax is valid (I wasn't able to verify that at the moment), you could do this:

update table
set three='sugar'
where row_number() over (partition by one, two)=1

Otherwise, another, more convoluted way to do it would be: (untested)

select one, two, three
from (
      select t1.one
            ,t1.two
            ,coalesce(t2.three, t1.three) as three
            ,row_number() over (partition by t1.one, t1.two) as row_num
      from table t1
      left join (
                 select one, two, 'sugar' as three, 1 as row_num
                 from (
                       select distinct one, two, three
                       from table
                       group by one, two, three
                       having count(*) > 1
                       )
                 ) t2
        on t1.one=t2.one
        and t1.two=t2.two
        and t1.row_num=t2.row_num
  )