oracle update left join查询

对于有的更新语句，要更新的表可能条件不够，需要用到left join关联其他表，

但是不能直接关联，否则报错：错误如下：

update  imim_gireqbillitems gi

left join imim_gireqbills g on g.guid=gi.guid

set gi.quantity=

(

    select sum(gi2.quantity)

    from imim_gireqbillitems gi2

    left join imim_gireqbills g2 on g2.guid=gi2.guid

    where g2.ismerge='' and g2.mergeid=g.mergeid

)

where g.ismerge='' and exists

(

    select 1

    from imim_gireqbillitems gi2

    left join imim_gireqbills g2 on g2.guid=gi2.guid

    where g2.ismerge='' and g2.mergeid=g.mergeid

)

正确实例：

update

(

  select * from imim_gireqbillitems gi

  left join imim_gireqbills g on g.guid=gi.guid

  where g.ismerge=''

) T

set T.quantity=

(

    select sum(gi2.quantity)

    from imim_gireqbillitems gi2

    left join imim_gireqbills g2 on g2.guid=gi2.guid

    where g2.ismerge='' and g2.mergeid=T.mergeid

)

where exists

(

    select 1

    from imim_gireqbillitems gi2

    left join imim_gireqbills g2 on g2.guid=gi2.guid

    where g2.ismerge='' and g2.mergeid=T.mergeid

)

巴特西

oracle update left join查询

最新文章

热门文章