Re: SQL Update duplicate records

On 23 Jul 2013 22:20, Birgitta Hauser wrote:

Assumed you want to update the first record of a list of records
with a duplicate pallet number and duplicate date. (you may even add
a WHERE condition to only filter a specific date:
Here is another solution:

Update yourtable x
set MyDate = '2013-07-18'
where rrn(x) in (Select RRNa
from (Select Palet, myDate, min(rrn(a)) RRNA
from yourtable a
/* optionally */ where myDate = '2013-07-19'
group by Palet, Mydate
having count(*) > 1
) b
)
;

If the WHERE-clause is added, thus debasing the more generic nature of the statement when the WHERE-clause is omitted, then it is probably best placed [also] in the WHERE-clause of the UPDATE to enable taking advantage of an INDEX for Mydate thus possibly avoiding the processing of every row due to full-table-scan implementation as the side effect of the selection on the RRN(x).

The subquery could be modified to limit its selection, correlated to the date chosen for the UPDATE [i.e. "where b.myDate=x.myDate" or if also adding the TABLE or LATERAL for the derived table that defines the aggregate query to enable scoping the correlation into that query, then "where a.myDate=x.myDate"], rather than duplicating the date constant in the predicate [i.e. rather than either "where a.myDate='2013-07-19'" or "where b.date='2013-07-19'"]; e.g.:

Update yourtable x
set MyDate = '2013-07-18'
where x.myDate = '2013-07-19'
and rrn(x) in (Select RRNa
from TABLE /* or use instead: LATERAL */
(Select Palet, myDate, min(rrn(a)) RRNA
from yourtable a
where a.myDate = x.myDate
group by Palet, Mydate
having count(*) > 1
) b
)