Shulou(Shulou.com)06/01 Report--

Today, a friend asked me a SQL question, which is basically a update statement. There seems to be nothing wrong with the logic, but it always reports an error when it is executed.

Statements and error messages are:

UPDATE payment_data rr

SET rr.penalty_date = '2017-4-12'

Where rr.id =

(SELECT min (r.id)

FROM payment_data r

Where data_no =

(SELECT data_no

FROM user_debt

WHERE out_trade_no = 'bestpay_order_no1491812746329'))

ERROR 1093 (HY000): You can't specify target table 'rr' for update in FROM clause for this problem, if you don't want to use an intermediate table, you can rewrite it to the following form, with a wrapper in the middle to circumvent the problem.

UPDATE payment_data rr

SET rr.penalty_date = '2017-4-12'

Where rr.id =

(SELECT min (t.id)

FROM (select id,data_no from payment_data r) t

Where t.data_no =

(SELECT data_no

FROM user_debt

WHERE out_trade_no = 'bestpay_order_no1491812746329'); but this problem is just the beginning, I compared the performance in the two databases, there is still a big difference, let's reproduce it in MySQL.

Create two tables as follows:

Create table payment_data (id int,data_no varchar (30), penalty_date date)

Create table user_debt (out_trade_no varchar (30), data_no varchar (30))

The data type changes slightly in Oracle.

Create table payment_data (id int,data_no varchar2 (30), penalty_date date)

Create table user_debt (out_trade_no varchar2 (30), data_no varchar2 (30)); then I will run the following four statements one by one, identifying the performance in MySQL,Oracle.

1) statement 1

UPDATE payment_data rr

SET rr.penalty_date = '2017-4-12'

Where rr.id =

(SELECT min (r.id)

FROM payment_data r

Where data_no =

(SELECT data_no

FROM user_debt

WHERE out_trade_no = 'bestpay_order_no1491812746329'))

MySQL: will report an error, indicating that the DML statement cannot query the same table at the same time.

Oracle: no error is reported, and can be parsed and executed correctly.

2) statement 2

UPDATE payment_data rr

SET rr.penalty_date = '2017-4-12'

Where rr.id =

(SELECT min (t.id)

FROM (select id,data_no from payment_data r) t

Where t.data_no =

(SELECT data_no

FROM user_debt

WHERE out_trade_no = 'bestpay_order_no1491812746329'))

MySQL: execution succeeded

Oracle: execution succeeded

3) statement 3

UPDATE payment_data rr, (SELECT min (r.id) id)

FROM payment_data r

Where data_no =

(SELECT data_no

FROM user_debt

WHERE out_trade_no = 'bestpay_order_no1491812746329')) t

SET rr.penalty_date = '2017-4-12'

Where rr.id = t.id _ MySQL: can execute correctly

Oracle: parsing error

4) statement 4

UPDATE payment_data rr

SET rr.penalty_date = '2017-4-12'

Where rr.id =

(SELECT min (r.id)

FROM payment_data r

Inner join user_debt b on r.data_no = b.data_no

WHERE b.out_trade_no = 'bestpay_order_no1491812746329'); MySQL: execution failed

Oracle: execute normally

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.