SQLite Forum

Update SQLITE with Count and condtions
Login

Update SQLITE with Count and condtions

(1) By Gideon on 2021-03-18 06:29:45 [source]

I want to update column Blokkeklaarblokkenognieklaar for every Blokkeklaaraliasnaam where BlokkeklaarVB = "NEE".

I have the following query to select what I want to achieve and it works.

select count(blokkeklaarVB) from BlokkeKlaar where BlokkeklaarVB = "NEE" group by BlokkeklaarAliasnaam

However when when I paste the above in an update query it updates the column with only one value for all Blokkeklaaraliasnaam. What am I missing in my update query?

update Blokkeklaar set BlokkeklaarBlokkeNogNieKlaar =(select count(blokkeklaarVB) from BlokkeKlaar where BlokkeklaarVB = "NEE" group by BlokkeklaarAliasnaam)

Regards

(2) By Gunter Hick (gunter_hick) on 2021-03-18 11:11:13 in reply to 1 [link] [source]

It is not at all clear what you are attempting to do.

NOTE: please use the proper quotes. "NEE" is the contents of a field called NEE, not a string literal 'NEE' (although SQLite is nice enough to pretend you wrote 'NEE' if there is no field NEE). It is bad habit to get into and leads to very obscure bugs in your code.

If you want to limit updates to certain records, you need to

UPDATE .. SET ... WHERE BlokkeklaarVB = 'NEE';

If you want to set a field to an aggregate value correlated to another field, you need to use a correlated subquery

UPDATE Blokkeklaar BK SET ... = (select count() from Blokkeklaar where BlokkeklaarAliasnaam = BK.BlokkeklaarAliasnaam) ...

What is the intent behind updating detail records with a group aggregate?