参见英文答案 >
Update rows in one table with data from another table based on one column in each being equal 5个
我有一个名为Student的表,列名为uniquename,age,department,city,Homecountry和另一个名为Employee的表,列为uniquename,exp,qualification,Homecountry.
我有一个名为Student的表,列名为uniquename,age,department,city,Homecountry和另一个名为Employee的表,列为uniquename,exp,qualification,Homecountry.
现在我想用where条件Student.uniquename = Employee.uniquename和Student.Homecountry = Employee.Homecountry下的Employee表的资格列值更新Student表的department列.
请帮我写一下更新声明.
解决方法
这种查询称为相关子查询.根据您的要求,查询如下….
update students s
set s.department = (
select e.qualification
from employee e
where s.uniquename = e.uniquename
and s.Homecountry = e.Homecountry
);
根据您的回复更新此帖子.
再次,继续,始终发布创建表并插入语句(和预期结果)来重现您的案例.如果您没有看到预期的结果,或者在执行查询时看到错误,请发布确切的消息,而不是仅仅说“不工作”.这是我的sqlplus会话的结果.
—创建表和插入语句
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,'India');
insert into student values ('Ahamed',26,'ECE','UK');
insert into student values ('Gokul','IT','USA');
commit;
insert into employee5 values ('Mohan','India');
insert into employee5 values ('Raja',24,'India');
insert into employee5 values ('Palani','USA');
insert into employee5 values ('Sathesh',29,'CSE','CANADA');
insert into employee5 values ('Ahamed',28,'UK');
insert into employee5 values ('Gokul','USA');
commit;
在更新数据之前……
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
更新声明和结果
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.
