這個是原來的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
-- ----------------------------
我網上查了 好像是要取別名 但是實在不知道怎么弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/92104.html
標籤：基礎和管理
上一篇：在linux中配置oracle client 后輸入./sqlplus 報錯：-bash: ./sqlplus: /lib/ld-linux.so.2: bad
下一篇：大資料批量匹配資料庫