[SQL] Update records by matching with eachother within one table

I have the following table structure:



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.



I tried the following but its not working:

UPDATE sales SET je_id = id
WHERE type = 'invoice' AND transaction_ref = transaction_ref;

Continue reading...