有X、Y2个表,都有a、b、c三个字段。现在想用Y表的c字段数据替换X表的c字段数据,条件是当X.a=Y.a and X.b=Y.b 时用Y.c替换X.c,请问这个语句要怎么写?
可以这样写吗:
update x set x.c=y.c where x.a=y.a and x.b=y.b
谢谢!
可以这样写吗:
update x set x.c=y.c where x.a=y.a and x.b=y.b
谢谢!
where exists (select 1 from y where x.a=y.a and x.b=y.b)
update "GADATA0017"."ITEMACTIVITY" set "GADATA0017"."ITEMACTIVITY"."STRCONTRACTNO" = (select "GADATA0063"."ITEMACTIVITY"."STRCONTRACTNO" from "GADATA0063"."ITEMACTIVITY" WHERE ( ( "GADATA0063"."ITEMACTIVITY"."LNGRECEIPTTYPEID" = '13' ) AND ( "GADATA0063"."ITEMACTIVITY"."BYTPERIOD" = '6' ) AND ( "GADATA0017"."ITEMACTIVITY"."STRRECEIPTNO" = "GADATA0063"."ITEMACTIVITY"."STRRECEIPTNO" ) AND ( "GADATA0017"."ITEMACTIVITY"."LNGRECEIPTNO" ="GADATA0063"."ITEMACTIVITY"."LNGRECEIPTNO") ) ) where exists (select 1 from "GADATA0063"."ITEMACTIVITY" where ( ("GADATA0017"."ITEMACTIVITY"."STRRECEIPTNO" = '"GADATA0063"."ITEMACTIVITY"."STRRECEIPTNO" ) and ( "GADATA0017"."ITEMACTIVITY"."LNGRECEIPTNO" = "GADATA0063"."ITEMACTIVITY"."LNGRECEIPTNO" ) and ( "GADATA0063"."ITEMACTIVITY"."LNGRECEIPTTYPEID" = '13' ) AND ( "GADATA0063"."ITEMACTIVITY"."BYTPERIOD" = '6' ) ) )
where x.a||x.b in (select x.a||x.b from x,y where x.a=y.a and x.b=y.b)不过,y.c每次必须只能取出一条才可以。
(select "GADATA0063"."ITEMACTIVITY"."STRCONTRACTNO" from "GADATA0063"."ITEMACTIVITY" WHERE ( ( "GADATA0063"."ITEMACTIVITY"."LNGRECEIPTTYPEID" = '13' ) AND ( "GADATA0063"."ITEMACTIVITY"."BYTPERIOD" = '6' ) AND ( "GADATA0017"."ITEMACTIVITY"."STRRECEIPTNO" = "GADATA0063"."ITEMACTIVITY"."STRRECEIPTNO" ) AND ( "GADATA0017"."ITEMACTIVITY"."LNGRECEIPTNO" ="GADATA0063"."ITEMACTIVITY"."LNGRECEIPTNO") ) )
...
---->出现多条就会报错了!
从新表中读出第一条记录,然后用其中一个字段在旧表中查找,找到的话把旧表的那条记录删除,然后把新表的插进去;如果找不到就直接插进去。效率极低,慢得要命,不知道有什么好办法。