update (
select a.contact_name cc,
b.owner_contact do
from contctsm1 a, device2m1 b
where substr(b.logical_name, 5, 4) = a.station_id
and a.contact_name <>b.owner_contact
) set do=cc需要更新多条记录 但是我这样写报错 “无法修改与非键值保存表对应列”
select a.contact_name cc,
b.owner_contact do
from contctsm1 a, device2m1 b
where substr(b.logical_name, 5, 4) = a.station_id
and a.contact_name <>b.owner_contact
) set do=cc需要更新多条记录 但是我这样写报错 “无法修改与非键值保存表对应列”
set t.owner_contact = (select contact_name
from contctsm1 s
where substr(t.logical_name, 5, 4) = s.station_id
and s.contact_name <> t.owner_contact);
一 如果select contact_name
from contctsm1 s
where substr(t.logical_name, 5, 4) = s.station_id
and s.contact_name <> t.owner_contact
返回值不唯一,按你自己需要添加其他条件.
二 如果不是要更新owner_contact中所有数据,需要增加where条件对需更新数据进行限定.
update device2m1 b set owner_contact =(SELECT contact_name FROM contctsm1 a ,device2m1 b
WHERE substr(b.logical_name, 5, 4) = a.station_id
and a.contact_name <>b.owner_contact)
update (
select /*+ BYPASS_UJVC */
a.contact_name cc,
b.owner_contact do
from contctsm1 a, device2m1 b
where substr(b.logical_name, 5, 4) = a.station_id
and a.contact_name <>b.owner_contact
) set do=cc
update device2m1 t
set t.owner_contact = (select contact_name
from contctsm1 s
where substr(t.logical_name, 5, 4) = s.station_id
and s.contact_name <> t.owner_contact);
update device2m1 t
set t.owner_contact = (select contact_name
from contctsm1 s
where substr(t.logical_name, 5, 4) = s.station_id
and s.contact_name <> t.owner_contact)
where exists(select 1 from contctsm1 q
where substr(t.logical_name, 5, 4) = q.station_id
and q.contact_name <> t.owner_contact);
set t.owner_contact = (select contact_name
from contctsm1 s
where substr(t.logical_name, 5, 4) = s.station_id
and s.contact_name <> t.owner_contact);--这个不行嘛?
所以,update只能更新一个值,可以对单行,也可以是多行,但更新结果肯定是一样的。
楼主你这种情况,要用merge into
或者存储过程。。
MERGE into device2m1 b
USING contctsm1 a
ON (substr(b.logical_name, 5, 4) = a.station_id
and a.contact_name <>b.owner_contact)
WHEN MATCHED THEN
update set
b.owner_contact=a.contact_name;
我对这个和存储过程比较菜 请高手详细指点一下 你这个我运行了一下 还是报错 无法在字句应用 b.owner_contact
MERGE into device2m1 b
USING contctsm1 a
ON (substr(b.logical_name,5,4) = a.station_id
and b.owner_contact<>a.contact_name)
WHEN MATCHED THEN
update set
b.owner_contact=a.contact_name;
merge into t_table t
using(select id,name,age,re,t_id from t_table_1) t1
on(t.id=t1.id)
when matched then
update set t.name=t1.name,t.age=t1.age,t.re=t1.re,t.t_id=t1.t_id(需要更新字段)
when not matched then insert (t.id,t.name,t.age,t.re,t.t_id)
values(t1.id,t1.name,t1.age,t1.re,t1.t_id)
MERGE into device2m1 b
USING contctsm1 a
ON (substr(b.logical_name,5,4) = a.station_id)
WHEN MATCHED THEN
update set
b.owner_contact=a.contact_name;
where b.owner_contact<>a.contact_name
MERGE into device2m1 b
USING contctsm1 a
ON (substr(b.logical_name,5,4) = a.station_id)
WHEN MATCHED THEN
update set
b.owner_contact=a.contact_name;
where b.owner_contact<>a.contact_name