Oracle 改 mysql 报错

Question

最近要把ORACLE数据库改成MYSQL 有些语句要改
有一条改了之后 mysql报错[Err] 1093 - You can't specify target table 'r' for update in FROM clause
这个是原来的Oracle 语句
update TB_CLS_PROFIT_REPORT r
set r.LAST_AMT=IFNULL((select c.CURR_AMT from TB_CLS_PROFIT_REPORT c where c.acct_Date=date_format(ADD_MONTHS(str_to_date(#{yearMonth,jdbcType=CHAR},'%Y%m'),-1),'%Y%m') ),0),
r.CHANGE_RATE=(r.CURR_AMT/decode(
IFNULL((select c.CURR_AMT from TB_CLS_PROFIT_REPORT c where c.acct_Date=date_format(ADD_MONTHS(str_to_date(#{yearMonth,jdbcType=CHAR},'%Y%m'),-1),'%Y%m')),r.CURR_AMT),
0,
r.CURR_AMT,IFNULL((select c.CURR_AMT from TB_CLS_PROFIT_REPORT c where c.acct_Date=date_format(ADD_MONTHS(str_to_date(#{yearMonth,jdbcType=CHAR},'%Y%m'),-1),'%Y%m')),r.CURR_AMT)))
where r.acct_Date=#{yearMonth,jdbcType=CHAR}
我改成
UPDATE TB_CLS_PROFIT_REPORT r
SET r.CHANGE_RATE =(r.CURR_AMT/(case
when (SELECT c.CURR_AMT FROM TB_CLS_PROFIT_REPORT c WHERE c.acct_Date = date_format(date_add(str_to_date('201511','%Y%m'),INTERVAL -1 month),'%Y%m')) = 0 then r.CURR_AMT
when (SELECT c.CURR_AMT FROM TB_CLS_PROFIT_REPORT c WHERE c.acct_Date = date_format(date_add(str_to_date('201511','%Y%m'),INTERVAL -1 month),'%Y%m')) is NULL then r.CURR_AMT
else (select * FROM (SELECT c.CURR_AMT FROM TB_CLS_PROFIT_REPORT c WHERE c.acct_Date = date_format(date_add(str_to_date('201511','%Y%m'),INTERVAL -1 month),'%Y%m')) tb)
END)
)
where r.acct_Date = '201511'
总是报错[Err] 1093 - You can't specify target table 'r' for update in FROM clause
表结构
/*
Navicat MySQL Data Transfer

Source Server : 20170607
Source Server Version : 50636
Source Host : 172.168.65.26:3316
Source Database : inetpay_clear

Target Server Type : MYSQL
Target Server Version : 50636
File Encoding : 65001

Date: 2017-06-16 10:37:22
*/

SET FOREIGN_KEY_CHECKS=0;

---

-- Table structure for tb_cls_profit_report

---

DROP TABLE IF EXISTS tb_cls_profit_report;
CREATE TABLE tb_cls_profit_report (
ID varchar(36) NOT NULL,
LAST_AMT decimal(18,0) NOT NULL,
CURR_AMT decimal(18,0) NOT NULL,
CHANGE_RATE decimal(5,2) NOT NULL,
ACCT_DATE char(8) NOT NULL,
SUBJECT_ONE char(1) NOT NULL,
SUBJECT_TWO char(4) NOT NULL,
SUBJECT_THREE char(7) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

---

-- Records of tb_cls_profit_report

---

我网上查了 好像是要取别名 但是实在不知道怎么弄_**

Answer 1

1，把要更新的几列数据查询出来做为一个第三方表，然后筛选更新。如：

update pe_bzz_examscore s set s.total_grade = '不合格' where s.id in (
        select t.id from pe_bzz_examscore t 
            where t.fk_exam_batch_id = '0000'  and t.status = '111' and ifnull(t.total_score, 0) < 60
)

改成

 update pe_bzz_examscore s set s.total_grade = '不合格' where s.id in (
            select t.id from (select id,fk_exam_batch_id,status,total_score from pe_bzz_examscore
) t  where t.fk_exam_batch_id = '0000'  and t.status = '111' and ifnull(t.total_score, 0) < 60)