求教update的问题

xinruinet

表a
name        score
aa        null
bb        null
dd            null

表b
name        score
aa        34       
bb        56
cc        67
dd        45
我想把a表中的score那个字段更新和b表name一致的分数

ddmmdd

update a set score ＝（select  b.score from b，a where b.name=a.name) where name=(select b.score from b，a where b.name=a.name)

xinruinet

update counter set regtime=(
select to_char(b.da,'YYYY-MM-DDHH24:MI:SS') from (select distinct usernum as us ,min(datetime) as da from user_interface_log where command in (2,3) group by usernum ) b,counter
where counter.phone=b.us
)
where phone=(
select b.us from (select distinct usernum as us ,min(datetime) as da from user_interface_log where command in (2,3) group by usernum ) b,counter where counter.phone=b.us
)

该句报错！ora－01427

catt_wab

update a  a set score =(
select score from b b where a.name=b.name);

xinruinet

请问您试过吗？我觉得不太对！不符合update语句的语法吧！

catt_wab

对的，我这么做过的

xinruinet

多谢，果真ok了！

taocb006

update aa  a set score=(select b.score from bb b where a.name=b.name)
是有问题的，它会将 aa表中有但bb中没有的记录也改了！

taocb006

用shell作：
#!/bin/sh


sqlplus -s .... EOF<<
set echo off feeback off ........
spool  a.sql
select 'update aa set score='||to_char(b.score) from bb b,aa a
where b.name=a.name;
spool off;
exit;
EOF

sqlplus ...
@a.sql