错误信息不是很清楚告诉你了吗?你的语句造成了某个非空字段被set 为null,所以出错。应该是你的exits子句有问题,改一下: UPDATE payroll_tag_item a SET (emp_id, amount) = (SELECT emp_id, nvl(amount,0) FROM payment_item b WHERE b.pay_id = 2 AND b.item_id = '9999' AND TO_CHAR (pay_date, 'yyyy-mm') = '2002-12' AND a.emp_id = b.emp_id) WHERE a.pay_id = 2 AND a.item_id = '0210' AND EXISTS ( SELECT 'x' FROM payment_item c WHERE c.emp_id = a.emp_id AND b.pay_id = 2 AND b.item_id = '9999' AND TO_CHAR (pay_date, 'yyyy-mm') = '2002-12');
和楼上的意思差不多,用下面的语句试一下。update payroll_tag_item A set (emp_id,amount)=(select emp_id,amount from payment_item B where B.pay_id=2 and B.item_id='9999' and to_char(pay_date,'yyyy-mm')='2002-12' and A.emp_id=B.emp_id) where A.pay_id=2 and A.item_id='0210' and exists(select emp_id,amount from payment_item C where C.emp_id=A.emp_id);不行再说了。
弱水兄厉害!一眼就看出问题在哪儿了,佩服佩服!不过好像有个小错误 :)EXISTS 后面的子查询中不能用到b了吧? 而且两个表中的amount都可以为NULL的,可以不用NVL吧?是不是应该这样?UPDATE payroll_tag_item a SET (emp_id, amount) = (SELECT emp_id, amount) FROM payment_item b WHERE b.pay_id = 2 AND b.item_id = '9999' AND TO_CHAR (b.pay_date, 'yyyy-mm') = '2002-12' AND b.emp_id = a.emp_id) WHERE a.pay_id = 2 AND a.item_id = '0210' AND EXISTS ( SELECT 'x' FROM payment_item c WHERE c.emp_id = a.emp_id AND c.pay_id = 2 AND c.item_id = '9999' AND TO_CHAR (c.pay_date, 'yyyy-mm') = '2002-12');
UPDATE payroll_tag_item a
SET (emp_id, amount) =
(SELECT emp_id, nvl(amount,0)
FROM payment_item b
WHERE b.pay_id = 2
AND b.item_id = '9999'
AND TO_CHAR (pay_date, 'yyyy-mm') = '2002-12'
AND a.emp_id = b.emp_id)
WHERE a.pay_id = 2
AND a.item_id = '0210'
AND EXISTS (
SELECT 'x'
FROM payment_item c
WHERE c.emp_id = a.emp_id
AND b.pay_id = 2
AND b.item_id = '9999'
AND TO_CHAR (pay_date, 'yyyy-mm') = '2002-12');
和楼上的意思差不多,用下面的语句试一下。update payroll_tag_item A set (emp_id,amount)=(select emp_id,amount from payment_item B where B.pay_id=2 and B.item_id='9999' and to_char(pay_date,'yyyy-mm')='2002-12' and A.emp_id=B.emp_id) where A.pay_id=2 and A.item_id='0210' and exists(select
emp_id,amount from payment_item C where C.emp_id=A.emp_id);不行再说了。
而且两个表中的amount都可以为NULL的,可以不用NVL吧?是不是应该这样?UPDATE payroll_tag_item a
SET (emp_id, amount) =
(SELECT emp_id, amount)
FROM payment_item b
WHERE b.pay_id = 2
AND b.item_id = '9999'
AND TO_CHAR (b.pay_date, 'yyyy-mm') = '2002-12'
AND b.emp_id = a.emp_id)
WHERE a.pay_id = 2
AND a.item_id = '0210'
AND EXISTS (
SELECT 'x'
FROM payment_item c
WHERE c.emp_id = a.emp_id
AND c.pay_id = 2
AND c.item_id = '9999'
AND TO_CHAR (c.pay_date, 'yyyy-mm') = '2002-12');