我的一句sql又出错了: update rsm_activity_headers
set patient_name = sop_activity_headers.users_last_name
from sop_activity_headers
where sop_activity_headers.eh_id = rsm_activity_headers.id
/ 出错信息:
from sop_activity_headers ah,rsm_activity_headers rah
*
ERROR 位于第3行:
ORA-00933: SQL command not properly ended 请问以上的写法应该怎样改正? (是在Oracle SQL*PLUS里运行的... / 要用sop_activity_headers中的用户名来更新rsm_activity_headers中与前表eh_id一样的记录 )
set patient_name = sop_activity_headers.users_last_name
from sop_activity_headers
where sop_activity_headers.eh_id = rsm_activity_headers.id
/ 出错信息:
from sop_activity_headers ah,rsm_activity_headers rah
*
ERROR 位于第3行:
ORA-00933: SQL command not properly ended 请问以上的写法应该怎样改正? (是在Oracle SQL*PLUS里运行的... / 要用sop_activity_headers中的用户名来更新rsm_activity_headers中与前表eh_id一样的记录 )
update rsm_activity_headers
set patient_name =(select a..users_last_name
from sop_activity_headers a,rsm_activity_headers b
where a.eh_id=b.id)
where sop_activity_headers.eh_id in(
select a.eh_id
from sop_activity_headers a,rsm_activity_headers b
where a.eh_id=b.id)
set patient_name = (select users_last_name
from sop_activity_headers
where eh_id = rsm_activity_headers.id)
where exists(
select 1 from sop_activity_headers where eh_id = rsm_activity_headers.id)
/
2 set patient_name =(select a.users_last_name
3 from sop_activity_headers a,rsm_activity_headers b
4 where a.eh_id=b.id)
5 where sop_activity_headers.eh_id in(
6 select a.eh_id
7 from sop_activity_headers a,rsm_activity_headers b
8 where a.eh_id=b.id)
9 /
where sop_activity_headers.eh_id in(
*
ERROR 位于第5行:
ORA-00904: "SOP_ACTIVITY_HEADERS"."EH_ID": invalid identifier
2 set patient_name = (select users_last_name
3 from sop_activity_headers
4 where eh_id = rsm_activity_headers.id)
5 where exists(
6 select 1 from sop_activity_headers where eh_id = rsm_activity_headers.id)
7 /
set patient_name = (select users_last_name
*
ERROR 位于第2行:
ORA-01427: single-row subquery returns more than one row
2 /D_order
-------
800000SQL>
set patient_name =(select a..users_last_name
from sop_activity_headers a,rsm_activity_headers b
where a.eh_id=b.id)
where rsm_activity_headers .id in(
select a.id
from sop_activity_headers a,rsm_activity_headers b
where a.eh_id=b.id)红字部分要改一下
update rsm_activity_headers
set patient_name =(select a..users_last_name
from sop_activity_headers a,rsm_activity_headers b
where a.eh_id=b.id)
where rsm_activity_headers .id in(
select b.id
from sop_activity_headers a,rsm_activity_headers b
where a.eh_id=b.id) 红字部分要改一下
2 set patient_name =(select a.users_last_name
3 from sop_activity_headers a,rsm_activity_headers b
4 where a.eh_id=b.id)
5 where rsm_activity_headers .id in(
6 select b.id
7 from sop_activity_headers a,rsm_activity_headers b
8 where a.eh_id=b.id)
9 /
set patient_name =(select a.users_last_name
*
ERROR 位于第2行:
ORA-01427: single-row subquery returns more than one row想起说了,sop_activity_headers里会有二条记录的eh_id都同时等于rsm_activity_headers里的id的。在sop_activity_headers里的相同eh_id的记录的users_last_name保证也是一样的。.
.
.
from sop_activity_headers a,rsm_activity_headers b
where a.eh_id=b.id
/我写了这个,可以得到唯一的users_last_name了,但不知怎么加上去。 急啊
set patient_name = (select users_last_name
from sop_activity_headers
where sop_activity_headers.eh_id = rsm_activity_headers.id )
set patient_name =(select a.users_last_name
from sop_activity_headers a,rsm_activity_headers b
where a.eh_id=b.id
group by a.users_last_name
)
where rsm_activity_headers .id in(
select b.id
from sop_activity_headers a,rsm_activity_headers b
where a.eh_id=b.id) 或者
update rsm_activity_headers
set patient_name =(select distinct a.users_last_name
from sop_activity_headers a,rsm_activity_headers b
where a.eh_id=b.id
group by a.users_last_name
)
where rsm_activity_headers .id in(
select b.id
from sop_activity_headers a,rsm_activity_headers b
where a.eh_id=b.id) 这两句应该都可以,应该可以保证a.users_last_name只出来一个,不出现两个
2 set patient_name =(select distinct a.users_last_name
3 from sop_activity_headers a,rsm_activity_headers b
4 where a.eh_id=b.id
5 group by a.users_last_name
6 )
7 where rsm_activity_headers .id in(
8 select b.id
9 from sop_activity_headers a,rsm_activity_headers b
10 where a.eh_id=b.id)
11 /
set patient_name =(select distinct a.users_last_name
*
ERROR 位于第2行:
ORA-01427: single-row subquery returns more than one row
2 set patient_name =(select a.users_last_name
3 from sop_activity_headers a,rsm_activity_headers b
4 where a.eh_id=b.id
5 group by a.users_last_name
6 )
7 where rsm_activity_headers .id in(
8 select b.id
9 from sop_activity_headers a,rsm_activity_headers b
10 where a.eh_id=b.id)
11 /
set patient_name =(select a.users_last_name
*
ERROR 位于第2行:
ORA-01427: single-row subquery returns more than one row
2 set patient_name =(select a.users_last_name
3 from sop_activity_headers a,rsm_activity_headers b
4 where a.eh_id=b.id
5 group by a.users_last_name
6 )
7 where rsm_activity_headers .id in(
8 select b.id
9 from sop_activity_headers a,rsm_activity_headers b
10 where a.eh_id=b.id)
11 /
set patient_name =(select a.users_last_name
*
ERROR 位于第2行:
ORA-01427: single-row subquery returns more than one row
SQL> 或者 SQL> update rsm_activity_headers
2 set patient_name =(select distinct a.users_last_name
3 from sop_activity_headers a,rsm_activity_headers b
4 where a.eh_id=b.id
5 group by a.users_last_name
6 )
7 where rsm_activity_headers .id in(
8 select b.id
9 from sop_activity_headers a,rsm_activity_headers b
10 where a.eh_id=b.id)
11 /
set patient_name =(select distinct a.users_last_name
*
ERROR 位于第2行:
ORA-01427: single-row subquery returns more than one row
SQL> 还是不行阿!! HELP!!!
TABLE1: sop_activity_headers
COLUMN1: eh_id 数值型 8
COLUMN2: users_last_name 字符型 16
DATA:
eh_id users_last_name
1 BLANK
1 BLANK
2 张三
3 李四
3 李四
4 王五
5 BLANK
6 BLANK
6 BLANK
6 BLANK
7 赵六TABLE2: rsm_activity_headers
COLUMN1: id 数值型 8
COLUMN2: patient_name 字符型 16
DATA:
id patient_name
1
2
3
4
5
6
7 现在要做的,就是用Table1的数据来更新Table2的patient_name列。谢谢!