CODEWITHSUNDEEP
sqloracle-database
user3553846
user3553846

Reputation: 342

SELECT clause then update the data

Table : Employee AND Application

//Employee
NAME            SALARY
-----------------------
CHIRS           40000
JOHN            30000
MARY            20000

//Application
NAME          P#
-----------------
CHIRS        0001
JOHN         0002

As the table above, we can see that only MARY don't have any application, so salary of MARY will be increase 10%

Following query:

UPDATE FROM EMPLOYEE E SET E.SALARY = E.SALARY*1.10
WHERE NOT EXISTS (SELECT NAME FROM APPLICATION);

isn't my following query correct or wrong? thanks you

Upvotes: 0

Views: 43

Answers (2)

Orlando Herrera
Orlando Herrera

Reputation: 3531

UPDATE FROM EMPLOYEE E
SET E.SALARY = E.SALARY*1.10
WHERE (SELECT COUNT(A.Name) FROM APPLICATION A WHERE A.NAME = E.NAME) = 0;

The performance of your query could improve if you try to use a "Count" clause

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269543

You need a correlated subquery:

UPDATE FROM EMPLOYEE E
    SET E.SALARY = E.SALARY*1.10
    WHERE NOT EXISTS (SELECT 1 FROM APPLICATION A WHERE A.NAME = E.NAME);

Without the correlation, the subquery would always return 2 rows. Clearly, these exist, so the where clause always fails.

Upvotes: 1

Related Questions