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

本文介绍了如何用另一个表的列值更新一个表的列值?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个名为Student的表，该表具有唯一名称，年龄，部门，城市，家乡和列，而另一个名为Employee的表则具有唯一名称，exp，资格，本国和列.

现在我要在条件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,'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.

i have table called Student with columns uniquename, age,department,city,Homecountry and another table called Employee with columns 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.

please help me to write the update statement.

解决方案

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 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.