0

I have a table with columns:

IdNum, NameEmployee, IdInsurance, NameInsurance, num_employees_not_insured

Some employees don't have insurance so IdInsurance and NameInsurance are null.

I want to count how many employees don't have insurance and put that in the last column.

I tried:

UPDATE MajorTable2
SET num_employees_not_insured =
    (SELECT COUNT(*) FROM MajorTable2)
    WHERE 'IdInsurance' IS NULL);

and next I tried "saving the result of counting null rows to a variable" like (but I'm guessing what's in single quotes does not count as a variable):

SELECT COUNT(*) AS 'NULL_values_in_IdInsurance'
FROM `MajorTable2`
WHERE `IdInsurance` IS NULL;

UPDATE MajorTable2
SET num_employees_not_insured = 'NULL_values_in_IdInsurance';

How can I fix my logic and syntax? I know some Python but my SQL is super rusty.

6
  • Why do you want to update every row with the number of people not ensured? From a design point of view this seems very wrong. Either calculate it on the fly, or place this attribute in a another table with aggregated data is my suggestion
    – Lennart
    Aug 6 at 6:10
  • @Lennart it's a HW problem so just testing to see if I can do this Aug 6 at 6:22
  • UPDATE MajorTable2 AS m CROSS JOIN (SELECT COUNT(*) AS cnt FROM MajorTable2) WHERE IdInsurance IS NULL) AS g SET m.num_employees_not_insured = g.cnt; Aug 6 at 8:22
  • Your last attempt (saving into a variable) is correct as well but you need to check how to use variables. Single quoted strings is not the correct way. Aug 6 at 8:23

1 Answer 1

2

Here is one way to overcome the limitations in MySQL regarding updates:

WITH T (n) as (
    SELECT count(*) - count(IdInsurance) from MajorTable2
)
update MajorTable2 set num_employees_not_insured = (
    SELECT n from T
);

A slight modification of ybercubes comment is:

UPDATE MajorTable2 AS m 
CROSS JOIN (
    SELECT COUNT(*) - COUNT(IdInsurance) AS cnt 
    FROM MajorTable2
) AS g 
    SET m.num_employees_not_insured = g.cnt;

Fiddle

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.