I need for a given USER_ID (for example 999) to find all CAR_ID that the given USER_ID does not yet have, but other USER_ID do.

My assumptions:

1. In the subquery SELECT, find all CAR_ID for all other USER_IDs without duplicates
2. Now you need to replace all found USER_ID with my only USER_ID (for example 999) and INSERT these values.

But I can't replace all CAR_ID that I get as a result of executing the SELECT subquery:

Maybe something like this:

INSERT INTO task(instance_id, user_id, car_id) (SELECT DISTINCT instance_id, 999, car_id
                                                FROM task
                                                WHERE user_id <> 999);

CodePudding user response：

Use aggregation and set the condition in the HAVING clause:

INSERT INTO task(user_id, car_id)
SELECT 999, car_id
FROM task
GROUP BY car_id
HAVING SUM((user_id = 999)::int) = 0;

See the demo.

or, with NOT EXISTS:

INSERT INTO task(user_id, car_id)
SELECT DISTINCT 999, t1.car_id
FROM task t1
WHERE NOT EXISTS (SELECT * FROM task t2 WHERE t2.user_id = 999 AND t2.car_id = t1.car_id);

See the demo.

CodePudding user response：

Maybe this query will help you I've not tested it yet but you can replace your sub-query with the following, this query will return all the unique car-ids that user 999 doesn't have.

Select Distinct null as instance_id,999 as user_id,car_id From task
where user_id!=999 and car_id not in 
(Select Distinct car_id From task where user_id=999)

So after this, your whole query looks like this

INSERT INTO task(instance_id, user_id, car_id) Select Distinct null as 
instance_id,999 as user_id,car_id From task where user_id!=999 and car_id 
not in (Select Distinct car_id From task where user_id=999)

So let me know if this will work for you or not