CODEWITHSUNDEEP
mysqlsql-update
Walter Schrabmair
Walter Schrabmair

Reputation: 1341

how can I update this column with an group by option

I have a table like this

 title                     ACCNR      ID
 Hello1                    1          1
 Hello1                    1          2
 Hello1                               3
 Hello1                               4
 Hello2                    3          5
 Hello2                    3          6
 Hello2                               7
 Hello2                               8

Now I would like to fill the missing ACCNR for the same Titles.

Edit: result should be:

 title                     ACCNR      ID
 Hello1                    1          1
 Hello1                    1          2
 Hello1                    **1**          3
 Hello1                    **1**          4
 Hello2                    3          5
 Hello2                    3          6
 Hello2                    **3**          7
 Hello2                    **3**          8

I tried this:

update refs join refs as p set refs.accnr = 
IF  (   
    SELECT  GROUP_CONCAT(DISTINCT refs.accnr order by accnr)) 
    from refs 
    where refs.id = p.id and  refs.accnr <> '' GROUP BY refs.title 
IS NULL ' ',c1) where p.id=refs.id;

The reason is, that I get this error

[Err] 1048 - Column 'custom_1' cannot be null

by this first try:

   update refs join refs as p set refs.custom_1 = 
    (   
    SELECT  GROUP_CONCAT(DISTINCT refs.accnr order by accnr) 
    from refs       
    where refs.id = p.id and  refs.accnr <> '' GROUP BY refs.title 
    ) where p.id=refs.id;

What make I wrong? I would thankfull for any advices.

EDIT: this approach uses a second table:

      drop table if EXISTS accnr;
      create table accnr (SELECT  GROUP_CONCAT(DISTINCT refs.accnr order 
      by accnr) as accnr, concat(',',GROUP_CONCAT(refs.id order by 
      refs.id),',') as idlist from refs         
      where refs.accnr <> '' GROUP BY refs.titel,);

      update refs join accnr set custom_1 = accnr.accnr where refs.id like concat('%,',refs.id,',%');

Upvotes: 0

Views: 40

Answers (1)

fonz
fonz

Reputation: 167

If I understand it correctly, you just need to update the column accnr with the same value as the first row.

You can update you table like this:

Update your_table b Join 
(Select title, accnr from your_table where accnr is not null group by title) a 
on b.title = a.title set b.accnr =a.accnr;

Upvotes: 1

Related Questions