CODEWITHSUNDEEP
oracle-databasesql-updatesqlplus
Gorka Sillero
Gorka Sillero

Reputation: 25

updating with a correlated subquery

I'm practicing with some oracle examples from the book, into the Oracle express and I found a problem when updating a table with a correlated subquery. This is the table, to which I want to update to a certain amount to the longest tenure in the company:

SQL> select * from employee;

    ID NAME                 MOBILE     ADDRESS                            SALARY HIRE_DATE DEPARTMENT_ID PROJECT_ID
     1 Maria                6985452    marias address                       1425 14-JAN-07             1
     2 Sonja                6988752    sonjas address                       1425 28-JUL-04             1
     3 Lilly                6965452    lillys address                       1425 11-JUN-01             2
     4 Martin               6974552    martins address                      1425 13-JUN-10             3
     5 Suzy                 6917452    suzys address                        1425 16-FEB-11             3
     6 Tom                  6945652    toms address                         1425 26-MAR-09             3
     7 Albert               6944452    alberts address                      1425 30-JAN-07             4
     8 Roger                6932152    rogers address                       1425 17-JUL-02             4
     9 Kara                 6941452    karas address                        1425 14-JUN-05             3

I'm trying to update using the minimum hire_date, with the following update:

    SQL> update employee emp
      2  set salary = 1550
      3  where hire_date = (select min(hire_date)
      4  from employee
      5  where name = emp.name);

  9 rows updated.

it ignores the "min(hire_date), at proceeds to update the whole table. I've tried to convert the dates but it's useless. I'm pretty sure the solution is in front of my nose, but I can't see it! any help guys?? please...

Upvotes: 2

Views: 112

Answers (1)

Przemyslaw Kruglej
Przemyslaw Kruglej

Reputation: 8123

The problem is that you are selecting the MIN(hire_date) for each of of the employees in the table - instead, you should take the MIN(hire_date) for the whole table like this:

update employee emp
  set salary = 1550
where hire_date = (select min(hire_date) from employee);

There is no need in this example to join the tables together.

Upvotes: 2

Related Questions