SQL subquery error giving can't specify against target-CodePudding

update customers 
set transaction_id = ( 
    select transaction_id from transactions
    where transaction_type IN (select transaction_type from customers where id = 1)
);

gives the error 1093. You can't specify target table for update in where clause? Anyone know why I am not allowed to use this Subquery?

CodePudding user response：

error 1093 comes from mysql, so i think you misstagged it.

But basically you need to create a subquery, so the database, doesn't use customers, which you are trying to change.

You must be sure that the subquery only reuturns one scalar value

CREATE tABLe customers (id int , transaction_id int)

CREATE tABLE transactions (transaction_id int,transaction_type int)

update customers 
set transaction_id = ( 
    select transaction_id from transactions
    where transaction_type IN (select transaction_type from (SELECT transaction_type  FROM customers where id = 10) c )
);

Rows matched: 0  Changed: 0  Warnings: 0

fiddle