Error updating record with sub query

Question

I am updating record on the base of sub query but it giving me error

you can't specify target table for update in from clause

my query is

UPDATE paymentinfo set customer_id = 
(
   SELECT transation.transactionid 
   FROM paymenttransaction AS transation 
   LEFT JOIN paymentinfo as payment 
   ON (transation.paymentinfoid=payment.paymentinfoid) 
   where payment.hash="0b576d33c57484692131471a847eab7c"
) 
WHERE hash="0b576d33c57484692131471a847eab7c"

where am i wrong and what will be perfect solution for that problem

try `UPDATE paymentinfo set paymentinfo.customer_id` it once — khalid, Oct 05 '16 at 12:47

Gordon Linoff · Answer 1 · 2016-10-06T03:13:50.930

I think it is simplest (in your case) to use the double subquery method:

UPDATE paymentinfo 
    SET customer_id = (SELECT transactionid
                       FROM (SELECT t.transactionid
                             FROM paymenttransaction pt LEFT JOIN
                                  paymentinfo pi
                                  ON t.paymentinfoid = pi.paymentinfoid
                             WHERE p.hash = '0b576d33c57484692131471a847eab7c'
                            ) t
                      ) 
    WHERE hash = '0b576d33c57484692131471a847eab7c';

Usually, you want to switch these to use JOIN, but I think that is a bit complicated in this case.

The table aliases are not correctly used when you reference the fields. — Shadow, Oct 05 '16 at 13:03

score 1 · Answer 2 · answered Oct 05 '16 at 12:36

1

You are updating the table 'paymentinfo' also at same time you are using this table for selection in subquery . Please break this query in two parts and it will work .

answered Oct 05 '16 at 12:36

Mukesh Swami

415
2
11

How i will break. You mean i use two queries instead of single one? – Umar Akbar Oct 05 '16 at 12:39

Error updating record with sub query

2 Answers2