请教:oracle 如何将一个表的某个字段赋值给另一张表的某个字段
表tc_customer,tc_friend,通过friend_id字段进行关联
要把tc_friend表的friend_score字段的值赋给对应的tc_customer的cust_dec001字段。update tc_customer tcc
set tcc.cust_dec001=tc_friend.friend_score
where tcc.friend_id=tc_friend.friend_id我这样写,提示出错了:
ORA-00904:“TC_FRIEND"."FRIEND_ID":Invalid identifier
表tc_customer,tc_friend,通过friend_id字段进行关联
要把tc_friend表的friend_score字段的值赋给对应的tc_customer的cust_dec001字段。update tc_customer tcc
set tcc.cust_dec001=tc_friend.friend_score
where tcc.friend_id=tc_friend.friend_id我这样写,提示出错了:
ORA-00904:“TC_FRIEND"."FRIEND_ID":Invalid identifier
set tcc.cust_dec001=(select tcf.friend_score from tc_friend tcf where tcf.friend_id =tcc.friend_id)
where exists (select 'a' from friend_score tt where tt.friend_id =tcc.friend_id);
where exists (select 'a ' from friend_score tt where tt.friend_id =tcc.friend_id);
set tcc.cust_dec001=(select tcf.friend_score from tc_friend tcf where tcf.friend_id =tcc.friend_id)
我照着这个去写,结果是一直在那算,算个没完了,好几个小时(30万数据)
where exists (select 'a ' from friend_score tt where tt.friend_id =tcc.friend_id);老大这句我还是没看怎么懂,这个'a'是什么啊?这个'friend_score'是表还是字段啊?
set tcc.cust_dec001=(select tcf.friend_score from tc_friend tcf where tcf.friend_id =tcc.friend_id)
where exists (select 1 from tc_friend tcf where tcf.friend_id =tcc.friend_id);只有在tc_friend中有对应的记录,才可以udpate tc_customer.
否则,cust_dec001会为null.exists语句是避免这种情况的.
create table tc_customer_bak
select * from tc_customer where 1 = 2;insert into tc_customer_bak
select tcc.*(不含cust_dec001字段), tcf.friend_score(注意顺序)
from tc_friend tcf, tc_customer tcc where tcf.friend_id =tcc.friend_id;delete from tc_customer where friend_id in ( select friend_id from tc_customer_bak);insert into tc_customer select * from tc_customer_bak;drop table tc_customer_bak;