2007-04-03

oracle关联update犯错

关键字: sql
Update card a
Set
(card_limit,open_date)=
(Select b.card_limit,b.issue_date From etl_cardinfo b Where a.card_number = b.card_no)
Where Exists (Select 1 From card a,etl_cardinfo b Where a.card_number = b.card_no)

总是报错说：ora-01407:can't set card.card_limit to null
于是改为
Update card a
Set open_date =(Select issue_date From etl_cardinfo b Where a.card_number = b.card_no)
,
card_limit=nvl(Select b.card_limit From etl_cardinfo b Where a.card_number = b.card_no,a.card_limit)
Where Exists (Select 1 From card a,etl_cardinfo b Where a.card_number = b.card_no)

现在报错：ora-00936:missing expression。干脆语法错误了。
看了半天后才发现，都是犯了两个愚蠢的小错误。
正确的写法是： 
Update card a 
Set 
(card_limit,open_date)=
(Select b.card_limit,b.issue_date From etl_cardinfo b Where a.card_number = b.card_no)
Where Exists (Select 1 From etl_cardinfo b Where a.card_number = b.card_no)
exists里面不用card a，如果是from a,b的话就不再是是子查询了，要么是存在要么是不存在，和where 1=1和where 1<>1一样了。
第二个的写法是： 
Update card a 
Set open_date =(Select issue_date From etl_cardinfo b Where a.card_number = b.card_no),
card_limit=
nvl((Select b.card_limit From etl_cardinfo b 
Where a.card_number = b.card_no),a.card_limit)
Where Exists (Select 1 From etl_cardinfo b Where a.card_number = b.card_no)
nvl里面的select * from X 必须括号起来
评论
发表评论

您还没有登录，请登录后发表评论

eyejava
搜索本博客
我的相册
5882c820-4b57-4dfd-b8d1-046e88098b0f-thumb
Gmail
共 8 张
存档
最新评论