CODEWITHSUNDEEP
mysqlsql
chella anilkumar
chella anilkumar

Reputation: 23

how to select update in the same table in mysql?

I have a table called emp(eid, ename, sal, mgr). Now I want to update the manager salary whose salary is less than 50000/-. 

UPDATE emp AS t1 INNER JOIN
(SELECT mgr FROM emp WHERE mgr = t1.empno) AS t2
SET sal = sal + 5000
WHERE t1.sal <50000;

what should I do?

Upvotes: 1

Views: 3014

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270181

In MySQL, I much prefer using JOIN for this type of update to using the doubly nested IN expression. The JOIN allows the optimizer to make use of available indexes:

UPDATE emp mgr INNER JOIN
       emp e
       ON e.empno = mgr.mgr
    SET mgr.sal = mgr.sal + 5000
    WHERE mgr.sal < 50000;

Upvotes: 0

2oppin
2oppin

Reputation: 1991

you can use subquery, to update data that depends on same table i.e:

UPDATE sometable set field = null 
WHERE sometable.id in (SELECT * FROM (SELECT id from sometable) as a);

Upvotes: 0

Rahul
Rahul

Reputation: 77896

I want to update the manager salary whose salary is less than 50000/

why you need a SELECT BTW. Just perform UPDATE operation like

update emp
set sal = 70000
where sal < 50000;

Upvotes: 1

Related Questions