Update multiple distinct columns

Question

I'm trying to update multiple rows at once, but only distinct rows.

SQL code: http://sqlfiddle.com/#!9/9c6bdd/4

As you can see from the select statement in the fiddle - I want to update rows using where and distinct conditions.

Update should change IDOrder value, for TOP 1 (LIMIT 1) row, where GroupP is the same

Example:

(IDPobocka, IDCinnost, Termin, IDOrder)

Input:

(1, 10,'2019-05-02 11:00:00', NULL),
(1, 10,'2019-05-02 11:00:00', NULL),
(1, 10,'2019-05-02 11:00:00', NULL),
(1, 11,'2019-05-02 11:00:00', NULL),
(1, 11,'2019-05-02 11:00:00', NULL),
(1, 11,'2019-05-02 11:00:00', NULL),
(1, 11,'2019-05-02 11:00:00', NULL),

Output:

(1, 10,'2019-05-02 11:00:00', 1234),
(1, 10,'2019-05-02 11:00:00', NULL),
(1, 10,'2019-05-02 11:00:00', NULL)
(1, 11,'2019-05-02 11:00:00', 1234),
(1, 11,'2019-05-02 11:00:00', NULL),
(1, 11,'2019-05-02 11:00:00', NULL),
(1, 11,'2019-05-02 11:00:00', NULL),

Answer 1

Fix your data model so you can distinguish the rows:

CREATE TABLE IF NOT EXISTS Test (
    TestId int auto_increment primary key,
    IDPobocka int,
    IDCinnost int,
    Termin datetime,
    IDOrder int
);

Then you can simply do:

update t join
       (select min(testid) as min_testid
        from test t
        group by IDPobocka, IDCinnost, Termin
       ) tt
       on t.testid = tt.min_testid
    set IDOrder = 1234;

Answer 2

It looks like you want to delete the first of each batch of identical records.

Whilst the permanent solution is to alter the database structure to allow for uniqueness of rows (1st normal form). This is a way of faking that until you get there.

This approach takes the top example of each idcinnost, pushes that in to a separate table. updates those records, finds the top example in the first table, uses a third table to reference that, remove the original row from the original table, then remove that row from the first temp table, then remove that from the 2nd temp table.

It's clunky but the over all effect works and provides the output you are looking for.

declare @t table
(
    IDPobocka int, 
    IDCinnost int, 
    Termin datetime, 
    IDOrder int
);

declare @r table
(
 id int
);

insert @t (IDPobocka, IDCinnost, Termin, IDOrder)
values
(1, 10,'2019-05-02 11:00:00', NULL),
(1, 10,'2019-05-02 11:00:00', NULL),
(1, 10,'2019-05-02 11:00:00', NULL),
(1, 11,'2019-05-02 11:00:00', NULL),
(1, 11,'2019-05-02 11:00:00', NULL),
(1, 11,'2019-05-02 11:00:00', NULL),
(1, 11,'2019-05-02 11:00:00', NULL);


select * into #temp from 
(select row_number()over(partition by idcinnost order by idpobocka) rowno, idpobocka, idcinnost, termin, idorder
 from @t) tx where rowno=1;

 update #temp set idorder=1234;

 while exists (select 1 from #temp)
    begin

        delete top (1) t 
        output deleted.IDCinnost into @r
        from @t t inner join #temp te on t.IDCinnost=te.IDCinnost;

        insert @t (IDPobocka, IDCinnost, Termin, IDOrder)
            select IDPobocka, IDCinnost, Termin, IDOrder from #temp t1 inner join @r r1 on t1.IDCinnost=r1.id;

        delete t2 from #temp t2 inner join @r r2 on t2.IDCinnost=r2.id;

        delete from @r;

    end

    drop table #temp;

    select * from @t;