Combining rows within the table

Question

I have Cart and Item tables as below

Cart
+---------+-------------+
| cart_id | customer_id |
+---------+-------------+
|       1 |           1 |
|       2 |           2 |
+---------+-------------+

and

Item
+---------+---------+------------+------------+
| item_id | cart_id | product_id |   quantity |
+---------+---------+------------+------------+
|       1 |       1 | AAAA       |          7 |
|       2 |       1 | BBBB       |          2 |
|       3 |       2 | AAAA       |          5 |
|       4 |       2 | CCCC       |          3 |
+---------+---------+------------+------------+

I want to write query which will add items in one cart into another cart. For example items in cart2 into cart1. After this query content of table should be like this

Item
+---------+---------+------------+------------+
| item_id | cart_id | product_id | product_id |
+---------+---------+------------+------------+
|       1 |       1 | AAAA       |          5 |
|       2 |       1 | BBBB       |          2 |
|       4 |       1 | CCCC       |          3 |
+---------+---------+------------+------------+

Table has such output because addition works in following way

1. Product AAAA was in both cart1 and cart2, so cart1 updated quantity=5 for item1 and cart2 removed item3
2. Item2 did not changed because there is no such item in cart2
3. There is no item with product_id=CCCC in cart1, so it changed cart_id of item4 to cart1
4. Cart2 should be removed, because items in cart2 merged into cart1

Answer 1

While you may be correct in your contention "I don't need to insert new values ..." the insert process provided by @GordonLinoff is by far easier than any update devised and likely to be much more understandable as a result. What's missing however is the removal of the now defunct cart, from both tables. That can be handled cascading CTEs(?) or a SQL function. Since I do not care for cascading DML CTEs I will provide a function.

create or replace function merge_carts(from_cart integer, to_cart integer)
  returns void
  language sql 
as $$
   insert into item(cart_id,product_id,quantity)
     select to_cart, product_id, quantity
       from item   
      where cart_id = from_cart
         on conflict (cart_id,product_id)
         do update set quantity = excluded.quantity; 
   delete 
     from item
    where cart_id = from_cart; 
   delete 
     from cart
    where cart_id = from_cart;            
$$;

Now once I know the cart_ids involved I just call the Merge_Carts function.

The critical issue is you making very sure your "anonymous user" identified as customer 2 is actually customer 1.
See full case example here.