PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Update records by matching with each other within one table

I have the following table:

create table sales 
(id,transaction_ref,type,amount,je_id)
as values
 (1,'a','invoice',100,null)
,(2,'b','invoice',200,null)
,(3,'c','invoice',300,null)
,(4,'d','invoice',400,null)
,(5,'a','entry',10,null)
,(6,'b','entry',20,null)
,(7,'c','entry',30,null)
,(8,'d','entry',40,null);

I want to match the records on the basis of transaction_ref and then update the ids of entry type records in invoice type records in column je_id.

The result should look like this:

id transaction_ref type amount je_id
1 a invoice 100 5
2 b invoice 200 6
3 c invoice 300 7
4 d invoice 400 8
5 a entry 10 null
6 b entry 20 null
7 c entry 30 null
8 d entry 40 null

I tried the following but it’s not working: DB<>Fiddle

UPDATE sales SET je_id = id 
WHERE type = 'invoice' 
  AND transaction_ref = transaction_ref;
id transaction_ref type amount je_id
1 a invoice 100 1
2 b invoice 200 2
3 c invoice 300 3
4 d invoice 400 4
5 a entry 10 null
6 b entry 20 null
7 c entry 30 null
8 d entry 40 null
Tags:

4

Answers


  1. 0

    With join:

    UPDATE A
SET A.je_id = B.id 
FROM Table_1 AS A
INNER JOIN Table_1 AS B 
       ON B.transaction_ref = A.transaction_ref
       AND B.type = 'entry'
  where A.type='invoice'

    Result:

    id  transaction_ref type    amount  je_id
1   a           invoice     99  5
2   b           invoice     99  6
3   c           invoice     99  7
4   d           invoice     99  8
5   a           entry       99  NULL
6   b           entry       99  NULL
7   c           entry       99  NULL
8   d           entry       99  NULL
    Login or Signup to reply.
  2. 0

    Try:

    Update sales
    SET je_id = b.id
from sales a 
join sales b
on a.transaction_ref=b.transaction_ref and a.type='invoice' and b.type='entry'
    Login or Signup to reply.
  3. 0

    Typically, you would get the IDs in a sub query:

    UPDATE sales i
SET i.je_id = 
(
  SELECT e.id 
  FROM sales e
  WHERE e.type = 'entry'
  AND e.transaction_ref = i.transaction_ref
)
WHERE i.type = 'invoice';

    This is a quite basic update statement and works in about every RDBMS.

    As to your own statement: This cannot work. WHERE transaction_ref = transaction_ref is true for every row in the table of course, so you update every row. SET je_id = id updates a row’s je_id with the row’s id. This is not what you want.

    Login or Signup to reply.
  4. 0

    UPDATE..FROM results in an implicit JOIN. Give your target and source records an alias, then specify what needs to join to what.
    In your case, targets are type='invoice', sources type='entry':
    demo at db<>fiddle

    update sales as t1
set je_id=s2.id
from sales as s2
where t1.type='invoice'
  and s2.type='entry'
  and t1.transaction_ref=s2.transaction_ref;
    id transaction_ref type amount je_id
    1 a invoice 100 5
    2 b invoice 200 6
    3 c invoice 300 7
    4 d invoice 400 8
    5 a entry 10 null
    6 b entry 20 null
    7 c entry 30 null
    8 d entry 40 null

    Quoting the UPDATE doc:

    When a FROM clause is present, what essentially happens is that the target table is joined to the tables mentioned in the from_item list, and each output row of the join represents an update operation for the target table.

    Further:

    from_item
    A table expression allowing columns from other tables to appear in the WHERE condition and update expressions. This uses the same syntax as the FROM clause of a SELECT statement; for example, an alias for the table name can be specified. Do not repeat the target table as a from_item unless you intend a self-join (in which case it must appear with an alias in the from_item).

    And that’s exactly what you need: repeat the target to get a self-join, give it an alias.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search