By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
| version() |
|---|
| 8.0.27 |
--
-- Shows how to do updates with the queries
--
CREATE TABLE cas -- French for "case" - case is an SQL keyword and should
( -- not be used for table names!
case_id INTEGER NOT NULL,
cat TEXT NOT NULL,
birth DATE NOT NULL, -- store as a date - only requires 4 bytes
c_type TEXT NULL -- type is a keyword - https://www.postgresql.org/docs/8.1/sql-keywords-appendix.html
);
INSERT INTO cas (case_id, cat, birth) VALUES
(20033738, 'CASE_OWNER', '1996-04-08'),
(20033738, 'WIFE' , '1995-08-22'),
(20033831, 'CASE_OWNER', '1975-03-05'),
(20033831, 'CHILD' , '2005-03-19'),
(20033831, 'CHILD' , '2006-03-25'),
(20033831, 'CHILD' , '2010-05-20'),
(20033831, 'CHILD' , '2013-10-25'),
(20039301, 'CASE_OWNER', '1999-07-27'),
(20039301, 'WIFE' , '2001-07-05'),
(20039301, 'CHILD' , '2018-10-22'),
(20039334, 'CASE_OWNER', '1994-03-10'),
(30033333, 'CASE_OWNER', '1980-01-01'), -- added a single case owner with one child!
(30033333, 'CHILD' , '2012-09-01'),
(30044444, 'CASE_OWNER', '2015-08-10'), -- added a case owner < 21 yrs of age!
(30055555, 'CASE_OWNER', '1970-02-10'), -- added a couple whose children are all > 21!
(30055555, 'WIFE' , '1972-07-05'),
(30055555, 'CHILD' , '1995-11-22'),
(30055555, 'CHILD' , '1997-05-19'),
(30066666, 'CASE_OWNER', '1970-02-10'), -- added single case owner whose children
(30066666, 'CHILD' , '1989-07-05'), -- are all over 21!
(30066666, 'CHILD' , '1992-11-22'),
(30066666, 'CHILD' , '1994-05-19');
SELECT * FROM cas;
| case_id | cat | birth | c_type |
|---|---|---|---|
| 20033738 | CASE_OWNER | 1996-04-08 | null |
| 20033738 | WIFE | 1995-08-22 | null |
| 20033831 | CASE_OWNER | 1975-03-05 | null |
| 20033831 | CHILD | 2005-03-19 | null |
| 20033831 | CHILD | 2006-03-25 | null |
| 20033831 | CHILD | 2010-05-20 | null |
| 20033831 | CHILD | 2013-10-25 | null |
| 20039301 | CASE_OWNER | 1999-07-27 | null |
| 20039301 | WIFE | 2001-07-05 | null |
| 20039301 | CHILD | 2018-10-22 | null |
| 20039334 | CASE_OWNER | 1994-03-10 | null |
| 30033333 | CASE_OWNER | 1980-01-01 | null |
| 30033333 | CHILD | 2012-09-01 | null |
| 30044444 | CASE_OWNER | 2015-08-10 | null |
| 30055555 | CASE_OWNER | 1970-02-10 | null |
| 30055555 | WIFE | 1972-07-05 | null |
| 30055555 | CHILD | 1995-11-22 | null |
| 30055555 | CHILD | 1997-05-19 | null |
| 30066666 | CASE_OWNER | 1970-02-10 | null |
| 30066666 | CHILD | 1989-07-05 | null |
| 30066666 | CHILD | 1992-11-22 | null |
| 30066666 | CHILD | 1994-05-19 | null |
--
-- This UPDATE works with MySQL version 5.5 (earliest version available to me)
--
UPDATE cas
INNER JOIN
(
SELECT
s.case_id,
s.cat,
s.birth,
CASE
WHEN s.c_cnt = 1
THEN
CASE
WHEN s.a_min >= 21
THEN 'SINGLE PERSON'
ELSE 'UNKNOWN'
END
WHEN s.c_cnt = 2 AND s.w_cnt = 1
THEN 'PAIR_NO_CHILD'
WHEN s.c_cnt >= 3 AND s.w_cnt = 1
THEN
CASE
WHEN s.a_min < 21
THEN 'PAIR_WITH_CHILD'
ELSE 'OTHER'
END
WHEN s.c_cnt >= 2 AND s.w_cnt = 0
THEN
SELECT * FROM cas;
| case_id | cat | birth | c_type |
|---|---|---|---|
| 20033738 | CASE_OWNER | 1996-04-08 | PAIR_NO_CHILD |
| 20033738 | WIFE | 1995-08-22 | PAIR_NO_CHILD |
| 20033831 | CASE_OWNER | 1975-03-05 | SINGLE_WITH_CHILD |
| 20033831 | CHILD | 2005-03-19 | SINGLE_WITH_CHILD |
| 20033831 | CHILD | 2006-03-25 | SINGLE_WITH_CHILD |
| 20033831 | CHILD | 2010-05-20 | SINGLE_WITH_CHILD |
| 20033831 | CHILD | 2013-10-25 | SINGLE_WITH_CHILD |
| 20039301 | CASE_OWNER | 1999-07-27 | PAIR_WITH_CHILD |
| 20039301 | WIFE | 2001-07-05 | PAIR_WITH_CHILD |
| 20039301 | CHILD | 2018-10-22 | PAIR_WITH_CHILD |
| 20039334 | CASE_OWNER | 1994-03-10 | SINGLE PERSON |
| 30033333 | CASE_OWNER | 1980-01-01 | SINGLE_WITH_CHILD |
| 30033333 | CHILD | 2012-09-01 | SINGLE_WITH_CHILD |
| 30044444 | CASE_OWNER | 2015-08-10 | UNKNOWN |
| 30055555 | CASE_OWNER | 1970-02-10 | OTHER |
| 30055555 | WIFE | 1972-07-05 | OTHER |
| 30055555 | CHILD | 1995-11-22 | OTHER |
| 30055555 | CHILD | 1997-05-19 | OTHER |
| 30066666 | CASE_OWNER | 1970-02-10 | MULTIPLE |
| 30066666 | CHILD | 1989-07-05 | MULTIPLE |
| 30066666 | CHILD | 1992-11-22 | MULTIPLE |
| 30066666 | CHILD | 1994-05-19 | MULTIPLE |
UPDATE cas SET c_type = NULL;
SELECT * FROM cas;
| case_id | cat | birth | c_type |
|---|---|---|---|
| 20033738 | CASE_OWNER | 1996-04-08 | null |
| 20033738 | WIFE | 1995-08-22 | null |
| 20033831 | CASE_OWNER | 1975-03-05 | null |
| 20033831 | CHILD | 2005-03-19 | null |
| 20033831 | CHILD | 2006-03-25 | null |
| 20033831 | CHILD | 2010-05-20 | null |
| 20033831 | CHILD | 2013-10-25 | null |
| 20039301 | CASE_OWNER | 1999-07-27 | null |
| 20039301 | WIFE | 2001-07-05 | null |
| 20039301 | CHILD | 2018-10-22 | null |
| 20039334 | CASE_OWNER | 1994-03-10 | null |
| 30033333 | CASE_OWNER | 1980-01-01 | null |
| 30033333 | CHILD | 2012-09-01 | null |
| 30044444 | CASE_OWNER | 2015-08-10 | null |
| 30055555 | CASE_OWNER | 1970-02-10 | null |
| 30055555 | WIFE | 1972-07-05 | null |
| 30055555 | CHILD | 1995-11-22 | null |
| 30055555 | CHILD | 1997-05-19 | null |
| 30066666 | CASE_OWNER | 1970-02-10 | null |
| 30066666 | CHILD | 1989-07-05 | null |
| 30066666 | CHILD | 1992-11-22 | null |
| 30066666 | CHILD | 1994-05-19 | null |
--
-- This UPDATE works with MySQL versions >= 8 (contains window functions and a cte)
--
WITH cte AS
(
SELECT
case_id,
cat,
birth,
c_type,
CASE
-- 1st section:
--
-- The logic here is that if the COUNT of case_id is = 1, then there can **only**
-- be a single case_owner and nobdody else is associated with the case!
--
-- Then, there's a choice between the age of the case_owner being greater than
-- or equal to 21 ('SINGLE_PERSON') or less than 21 ('UNKNOWN')
--
WHEN (COUNT(case_id) OVER (PARTITION BY case_id)) = 1
THEN
CASE
WHEN (DATEDIFF(NOW(), birth) / 365.25) >= 21
THEN 'SINGLE_PERSON'
ELSE 'UNKNOWN'
END
-- 2nd section:
SELECT * FROM cas;
| case_id | cat | birth | c_type |
|---|---|---|---|
| 20033738 | CASE_OWNER | 1996-04-08 | PAIR_NO_CHILD |
| 20033738 | WIFE | 1995-08-22 | PAIR_NO_CHILD |
| 20033831 | CASE_OWNER | 1975-03-05 | SINGLE_WITH_CHILD |
| 20033831 | CHILD | 2005-03-19 | SINGLE_WITH_CHILD |
| 20033831 | CHILD | 2006-03-25 | SINGLE_WITH_CHILD |
| 20033831 | CHILD | 2010-05-20 | SINGLE_WITH_CHILD |
| 20033831 | CHILD | 2013-10-25 | SINGLE_WITH_CHILD |
| 20039301 | CASE_OWNER | 1999-07-27 | PAIR_WITH_CHILD |
| 20039301 | WIFE | 2001-07-05 | PAIR_WITH_CHILD |
| 20039301 | CHILD | 2018-10-22 | PAIR_WITH_CHILD |
| 20039334 | CASE_OWNER | 1994-03-10 | SINGLE_PERSON |
| 30033333 | CASE_OWNER | 1980-01-01 | SINGLE_WITH_CHILD |
| 30033333 | CHILD | 2012-09-01 | SINGLE_WITH_CHILD |
| 30044444 | CASE_OWNER | 2015-08-10 | UNKNOWN |
| 30055555 | CASE_OWNER | 1970-02-10 | OTHER |
| 30055555 | WIFE | 1972-07-05 | OTHER |
| 30055555 | CHILD | 1995-11-22 | OTHER |
| 30055555 | CHILD | 1997-05-19 | OTHER |
| 30066666 | CASE_OWNER | 1970-02-10 | MULTIPLE |
| 30066666 | CHILD | 1989-07-05 | MULTIPLE |
| 30066666 | CHILD | 1992-11-22 | MULTIPLE |
| 30066666 | CHILD | 1994-05-19 | MULTIPLE |