oracle – 如何使用另一个表的列值更新一个表列值？

参见英文答案 > 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.

现在我想用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.

（编辑：武汉站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!