今天有個朋友問我一個SQL問題,大體是一個update語句,看起來邏輯沒有問題,但是執行的時候卻總是報錯。
語句和報錯信息為:
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
UPDATE payment_data rr
但是這個問題才是開始,我對比了一下在兩個數據庫的表現,還是有很大的差別,我們先在MySQL復現一下。
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'));
創建兩個表如下:
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));
在Oracle中數據類型稍有變化。
create table payment_data(id int,data_no varchar2(30), penalty_date date);
然後我會逐個運行下面的4個語句,同時標識在MySQL,Oracle的表現。
create table user_debt(out_trade_no varchar2(30), data_no varchar2(30));
1)語句1
UPDATE payment_data rr
MySQL: 會報錯,提示DML語句不能同時查詢同一個表。
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'));
Oracle:無報錯,可以正確解析和執行。
2)語句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:執行成功
Oracle: 執行成功
3)語句3
UPDATE payment_data rr, (SELECT min(r.id) id
MySQL:能夠正確執行
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;
Oracle:解析出錯
4)語句4UPDATE payment_data rr
MySQL:執行失敗
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');
Oracle:執行正常