Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE a (
    a_id SERIAL PRIMARY KEY,
    a_value  varchar(20),
    confirm boolean
);
CREATE TABLE b (
    b_id SERIAL PRIMARY KEY,
    a_id INT REFERENCES a(a_id) NOT NULL,
    b_value varchar(20)
);

INSERT INTO a (a_value) VALUES ('a1'),('a2'),('a3');
INSERT INTO b (a_id, b_value) VALUES (1, 'a1-b1'),(1, 'a1-b2'),(2, 'f2-b1'),(2, 'a2bbb'),(3, 'a3b3');
3 rows affected

5 rows affected

UPDATE a SET confirm = true WHERE a_id = 1;
UPDATE a SET confirm = false WHERE a_id = 2;
UPDATE a SET confirm = false WHERE a_id = 3;
1 rows affected

1 rows affected

1 rows affected

--Не получится изменить для 1
UPDATE a SET a_value = 'AA1' WHERE NOT confirm AND a_id = 1;
--Так получится
UPDATE a SET a_value = 'AA2' WHERE NOT confirm AND a_id = 2;

SELECT * FROM a;
1 rows affected



a_id
a_value
confirm


1
a1
t

3
a3
f

2
AA2
f


--Не получится изменить для 1
DELETE FROM b WHERE (SELECT NOT confirm FROM a WHERE a_id = 1) AND a_id = 1;
INSERT INTO b (a_id, b_value) SELECT * FROM (VALUES (1, 'AA1-BB1'),(1, 'AA1-BB2'),(1, 'AA1-BB3')) val 
    WHERE (SELECT NOT confirm FROM a WHERE a_id = 1);

DELETE FROM b WHERE (SELECT NOT confirm FROM a WHERE a_id = 2) AND a_id = 2;
2 rows affected

SELECT * FROM (VALUES (1, 'AA1-BB1'),(1, 'AA1-BB2'),(1, 'AA1-BB3')) val


column1
column2


1
AA1-BB1

1
AA1-BB2

1
AA1-BB3


SELECT NOT confirm FROM a WHERE a_id = 2


?column?


t


SELECT * FROM (VALUES (1, 'AA1-BB1'),(1, 'AA1-BB2'),(1, 'AA1-BB3')) val 
    WHERE (SELECT NOT confirm FROM a WHERE a_id = 2);


column1
column2


1
AA1-BB1

1
AA1-BB2

1
AA1-BB3


INSERT INTO b (a_id, b_value) SELECT * FROM (VALUES (1, 'AA1-BB1'),(1, 'AA1-BB2'),(1, 'AA1-BB3')) val 
    WHERE (SELECT NOT confirm FROM a WHERE a_id = 2);
3 rows affected

SELECT * FROM b;


b_id
a_id
b_value


1
1
a1-b1

2
1
a1-b2

5
3
a3b3

6
1
AA1-BB1

7
1
AA1-BB2

8
1
AA1-BB3