oracle 如何使用另一个表的列值更新一个表的列值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4378847/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to update One table column values with another table's column values?
提问by tasmohan
i have table called Student with columns uniquename, age,department,city,Homecountry and another table called Employee with columns uniquename, exp,qualification, Homecountry.
我有一个名为 Student 的表，其中包含 uniquename、age、department、city、Homecountry 和另一个名为 Employee 的表，其中包含 uniquename、exp、qualification、Homecountry 列。
now i want to update Student table's department column with Employee table's qualification column values under the where condition Student.uniquename = Employee.uniquename and Student.Homecountry = Employee.Homecountry.
现在我想在条件 Student.uniquename = Employee.uniquename 和 Student.Homecountry = Employee.Homecountry 下用员工表的资格列值更新学生表的部门列。
please help me to write the update statement.
请帮我写更新声明。
回答by Rajesh Chamarthi
This kind of query is called a correlated sub query. For your requirement, the query would be as below....
这种查询称为相关子查询。根据您的要求，查询如下....
update students s
set s.department = (
select e.qualification
from employee e
where s.uniquename = e.uniquename
and s.Homecountry = e.Homecountry
);
updating this post based on your replies below.
根据您在下面的回复更新此帖子。
Again, going forward, always post the create table and insert statements (and the expected results) to reproduce your case. If you don't see the expected results or if you see an erro when you execute the query, post the exact message instead of just saying "not working". Here is the results of my sqlplus session.
再次重申，始终发布 create table 和 insert 语句（以及预期结果）以重现您的案例。如果您没有看到预期的结果，或者您在执行查询时看到错误，请发布确切的消息，而不是仅仅说“不工作”。这是我的 sqlplus 会话的结果。
---create table and insert statements
---创建表和插入语句
create table student(
name varchar2(20),
age number,
department varchar2(3),
HomeCountry varchar2(10)
);
Table created.
create table employee5(
name varchar2(20),
exp number,
qualification varchar2(3),
homecountry varchar2(10)
);
Table created.
insert into student values ('Mohan',25,'EEE','India');
insert into student values ('Raja',27,'EEE','India');
insert into student values ('Ahamed',26,'ECE','UK');
insert into student values ('Gokul',25,'IT','USA');
commit;
insert into employee5 values ('Mohan',25,'ECE','India');
insert into employee5 values ('Raja',24,'IT','India');
insert into employee5 values ('Palani',26,'ECE','USA');
insert into employee5 values ('Sathesh',29,'CSE','CANADA');
insert into employee5 values ('Ahamed',28,'ECE','UK');
insert into employee5 values ('Gokul',29,'EEE','USA');
commit;
Before updating the data...
在更新数据之前...
SQL> select * from student;
NAME AGE DEP HOMECOUNTR
-------------------- ---------- --- ----------
Mohan 25 EEE India
Raja 27 EEE India
Ahamed 26 ECE UK
Gokul 25 IT USA
SQL> select * from employee5;
NAME EXP QUA HOMECOUNTR
-------------------- ---------- --- ----------
Mohan 25 ECE India
Raja 24 IT India
Palani 26 ECE USA
Sathesh 29 CSE CANADA
Ahamed 28 ECE UK
Gokul 29 EEE USA
Update statement and results
更新语句和结果
1 update student s set s.age =
2 ( select e.exp
3 from employee5 e
4 where e.name = s.name
5 and e.homecountry = s.homecountry
6* )
SQL> /
4 rows updated.
SQL> select * from student;
NAME AGE DEP HOMECOUNTR
-------------------- ---------- --- ----------
Mohan 25 EEE India
Raja 24 EEE India
Ahamed 28 ECE UK
Gokul 29 IT USA
SQL> commit;
Commit complete.
回答by debasish
update student s
set s.age = (select e.exp
from employee5 e
where e.name = s.name
and e.homecountry = s.homecountry
and rownum < 2
)
where s.age in (select age from employee5)
It wont show the message subquery returns more than one record
它不会显示消息子查询返回多条记录
