Table A id b_id type size Table B id name
What important in A table is there are three values of type column: New, Activated, Archive.
And I set default value 0 to size column of A table and after insert command
insert into A (id, b_id, type, size) values (1, 101, 'New', 10)
I expected following result in A table:
Query
select * from A;
Table A result
| id | b_id | type | size |
|---|---|---|---|
| 1 | 101 | New | 10 |
| 2 | 101 | Activated | 0 |
| 3 | 101 | Archive | 0 |
But, it does not work. So how to reach that ?
Advertisement
Answer
You are inserting just one row, so this will not automatically create three rows.
If you want three rows, including two with a default size value:
insert into A (id, b_id, type, size) values (1, 101, 'New', 10); insert into A (id, b_id, type) values (1, 101, 'Activated'); insert into A (id, b_id, type) values (1, 101, 'Archive');
If you wanted to automatically duplicate the inserted row for the two other statuses, you could consider something like:
insert into A (id, b_id, type, size)
values d.id, d.b_id, t.type, case when d.type = t.type then d.size else default end
from (values (1, 101, 'New', 10)) d(id, b_id, type, size)
cross join (values ('New'), ('Activated'), ('Archive')) t(type)