mysql Setting the default value of a column on a parent table whose value is dependent on a column value of a child table?

3z6pesqy 于 2022-12-22 发布在 Mysql

关注(0)|答案(1)|浏览(128)

I'm a bit new to SQL and having some trouble coming up with this query. I have two tables, a parent policies table, and a child policies_sub table which references the parent table via a policy_id foreign key:
policies table:
| policy_id | col_to_update |
| ------------ | ------------ |
| 1 | 0 |
| 2 | 0 |
policies_sub table:
| policy_id | factor_enum |
| ------------ | ------------ |
| 1 | 1 |
| 2 | 0 |
I'd like to add the new column col_to_update and set its default value based on whether there exists a row in policies_sub with the same policy_id and a value set for factor_enum . For example, for policy_id=1 , since there is a row in policies_sub for that policy_id and a value set for factor_enum , I want to then default the col_to_update for that policy_id to 1 . Hopefully that makes sense. I think this involves a JOIN clause but I'm unsure of how everything is supposed to come together.

mysql

来源：https://stackoverflow.com/questions/74821705/setting-the-default-value-of-a-column-on-a-parent-table-whose-value-is-dependent

1条答案

按热度按时间

oalqel3c1#

One-time update:

UPDATE policies 
SET col_to_update = EXISTS ( SELECT NULL
                             FROM policies_sub 
                             WHERE policies_sub.policy_id = policies.policy_id
                               AND policies_sub.factor_enum
                            )

On-the-fly actualization:

CREATE TRIGGER tr_ai_factor_enum
AFTER INSERT ON factor_enum
FOR EACH ROW
UPDATE policies 
SET col_to_update = EXISTS ( SELECT NULL
                             FROM policies_sub 
                             WHERE policy_id = NEW.policy_id
                               AND factor_enum
                            )
WHERE policy_id = NEW.policy_id

and the same triggers AFTER DELETE (use OLD.policy_id) and AFTER UPDATE (use both - i.e. 2 UPDATEs).
After triggers creation run one-time UPDATE once for current values actualization.

赞(0）回复(0）举报 2022-12-22

我来回答

mysql Setting the default value of a column on a parent table whose value is dependent on a column value of a child table?

1条答案

相关问题

热门标签

最新问答