Assign A Certain Country To All Orders Of A User If The User Placed At Least One Order In A Specific Other Country

DB-Fiddle CREATE TABLE customers ( id SERIAL PRIMARY KEY, order_date DATE, country VARCHAR(255), customer VARCHAR(255) ); INSERT INTO customers (order_date, countr

Solution 1:

Use window functions!

select . . . ,
       (casewhencount(*) filter (where country ='DE') over (partitionby customer) >0then'DE'else country
        end) as country
from customer c;

Solution 2:

You can do this a few ways, one is to check if the customer has any other row for country 'DE' and use that if so:

SELECT c.order_date, 
  coalesce( casewhenexists (
    select*from customers c2 
    where c2.customer=c.customer and c2.country='DE'
  ) then'DE'end, c.country) Country, 
c.customer
FROM customers c
GROUPBY1,2,3ORDERBY1,2,3;

DB Fiddle