一千萬個為什麽

搜索

如何使用另一個表的列值更新一個表列值?

我有一個名為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
       );

updating this post based on your replies below.

再次,繼續,始終發布創建表並插入語句(和預期結果)來重現您的案例。如果您沒有看到預期的結果,或者在執行查詢時看到錯誤,請發布確切的消息,而不是僅僅說“不工作”。這是我的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,'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;

在更新數據之前......

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.

轉載註明原文: 如何使用另一個表的列值更新一個表列值?