3

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.

tasmohan
  • 121
  • 1
  • 3
  • 7

2 Answers2

18

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.
Rajesh Chamarthi
  • 18,568
  • 4
  • 40
  • 67
  • thanks..Rajesh, here my problem in your query is i don't want to update all the department values from Student table. Actually i am in need of use the same WHERE condition which you used inside for select statement. – tasmohan Dec 07 '10 at 16:44
  • Can you please explain with some data? It's a little difficult to understand requirements in words. – Rajesh Chamarthi Dec 07 '10 at 17:06
  • I'm not sure what else you're looking for - @Rajesh's query does what you asked i.e., `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.` if you don't want to update all department values - just add the selected department values to the where condition - `update students set (..) where ` – Sathyajith Bhat Dec 07 '10 at 17:11
  • here my where condition includes 2 tables( Student.Homecountry = Employee.Homecountry). but in update statement we can not use more than one table table. And i want to update 100 records in Student table. when ever i run the above query it says sub-query returns multiple rows. should i use cursor to update multiple records in a one table column with another table's column. i am new to oracle, so please correct me, if i wrong – tasmohan Dec 07 '10 at 17:23
  • "sub-query returns multiple rows" happens when for a given student, you find more than one record from employee table. if you could take the time to post your data and table details, it would be very helpful. – Rajesh Chamarthi Dec 07 '10 at 17:33
  • table name = student uniquename = Mohan,Raja,Ahamed,Gokul age = 25, 27, 26,25 department = EEE,EEE,ECE,IT Homecountry = India,India,UK,USA table name = Employee uniquename = Mohan,Raja,Palani,Sathesh,Ahamed,Gokul exp= 25,24,26,29,28,29 qualification = ECE,IT,ECE,CSE,ECE,EEE Homecountry = India,India,USA,CANADA,UK,USA i have to update student table's age column values with Employee table's exp cloumn value when ever it accepts the where condition "Student.uniquename = Employee.uniquename and Student.Homecountry = Employee.Homecountry" – tasmohan Dec 07 '10 at 18:02
  • Go through this link : http://tkyte.blogspot.com/2005/06/how-to-ask-questions.html – Rajesh Chamarthi Dec 07 '10 at 19:16
  • @RajeshChamarthi, thank you for telling us that this is called a "correlated sub query". When I read your answer, I didn't understand how it could work: how can we set the value to an entire table? (I am new to SQL.) But I Googled "correlated sub query", and the Wikipedia page cleared things up: "The subquery is evaluated once for each row processed by the outer query." https://en.wikipedia.org/wiki/Correlated_subquery – DavidS Sep 17 '14 at 22:19
2
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

Pawel
  • 798
  • 8
  • 28
debasish
  • 21
  • 1