SQLite Forum

Update with group_concat
Login

Update with group_concat

(1) By Gideon on 2020-08-03 16:49:42 [source]

I have the following group_concat SQLite query :

SELECT GROUP_CONCAT(Plaasno) FROM wingrd13 group by Aliasnaam

This works perfectly,but if I use it as a sub-query with update, it update all the rows based on the group_concat of the first Aliasnaam.

The update query is as follows :

Update wingrd13 set Plaasnommers=(SELECT GROUP_CONCAT(Plaasno) FROM wingrd13 group by Aliasnaam)

What am I missing?

Regards

(2) By David Raymond (dvdraymond) on 2020-08-03 17:23:18 in reply to 1 [link] [source]

You have an update which runs a sub query which returns multiple rows, what are you expecting it to do? You have nothing in there which specifies which of the (potentially) multiple returned rows it should use in the update, so it uses the first one it gets back.

In other DMBS's like Postgres, a subquery which return multiple rows will raise an error. SQLite apparently allows it and just uses the first returned row.

If you're trying to update Plassnommers based off of the Aliasnaam field, it would be something like this I believe

update wingrd13
set Plaasnommers = (
    select group_concat(Plaasno)
    from wingrd13 as foo
    where foo.Aliasnaam = wingrd13.Alasnaam
);

Note that if the field you were updating were used anywhere in the sub-select to the same table, then it would probably result in "undefined" problems as it would be updating it and changing the results while you were going through it.

(3) By Keith Medcalf (kmedcalf) on 2020-08-03 17:27:38 in reply to 1 [link] [source]

A correlation. For example:

update wingrd13
   set Plaasnommers = (
                        select group_concat(Plaasno)
                          from wingrd13 as S
                         where S.Aliasnaam == wingrd13.Aliasnaam
                      )

assuming, of course, that your "correlation" is the Aliasnaam column. Adjust the correlated subquery so that the it reflects the correlation you want.

(4.1) By Keith Medcalf (kmedcalf) on 2020-08-03 17:50:24 edited from 4.0 in reply to 3 [link] [source]

Note that in the NEXT version of SQLite3 you will be able to do something like this:

update wingrd13
   set Plaasnommers = group_concat(S.Plaasno)
  from wingrd13 as S
 where S.Aliasnaam == wingrd13.Aliasnaam
;

to achieve the same result. This form will automatically create the required covering index if it does not exist whereas the correlated subquery form probably will not.

You would also be able to do something like this:

update wingrd13
  set Plaasnommers = gcPlaasno
 from (
         select Aliasnaam, 
                group_concat(Plaasno) as gcPlaasno
           from wingrd13
       group by Aliasnaam
      ) as S
where S.Aliasnaam == wingrd13.Aliasnaam;

which would likely be more efficient as long as you have an index on Aliasnaam since it will materialize the subquery once and then do the update.