Give a raise to people who work in X city?

Question

Let's say these are the 2 tables:

workers[name, dep_id, salary]

and

 department[id, name, city]

How can I achieve to raise the salary of those who work in a particular city?

I'd only know how to do it if the city was in the workers table (then it'd be just UPDATE workers SET salary = salary * 1.1 WHERE city = 'X'), but it doesn't work when the salary and city are in different tables.

Possible duplicate of [SQL update query using joins](http://stackoverflow.com/questions/982919/sql-update-query-using-joins) — Max, Jan 18 '16 at 17:11
This is done differently in different flavors of SQL, which database backend are you using. IT is important. — HLGEM, Jan 18 '16 at 20:17
I have to point out that this design is flawed for many workplaces. Just because the department is located in a city is no guarantee that all employees are. My department is located in a city in Virginia but we have employees who are located in Colorado, Pennsylvania,Texas, Illinois, Maryland, and other cities in Virginia as well as overseas. — HLGEM, Jan 18 '16 at 20:20

score 0 · Accepted Answer · answered Jan 18 '16 at 17:11

You can use this query:

UPDATE workers w
SET salary = salary * 1.1
WHERE EXISTS (SELECT * 
              FROM departments d
              WHERE d.id = w.dep_id
              AND d.city = 'X')

You can use also other queries, depending on your specific system.

HappyCoding · Answer 2 · 2016-01-18T17:19:15.073

0

UPDATE w
SET w.salary = w.salary*1.1
FROM workers w
LEFT JOIN department d
ON d.id = w.dept_id
WHERE d.city = 'denver'

I believe this will work. Left joining to department will give you the extra columns you're looking for for each row in workers.

edited Jan 18 '16 at 17:19

answered Jan 18 '16 at 17:14

HappyCoding

641
16
36

Nino Mirza Mušić · Answer 3 · 2016-01-18T20:14:35.677

0

UPDATE
    w
SET
    w.salary = w.salary * 1.1
FROM
    workers w
INNER JOIN
    departments d
ON
    w.dep_id = d.id
WHERE
    d.city = 'X'

Best and most safe way to do any data update is to first select rows which you want to update. So First step will be

    SELECT (*)  // Select all     
    FROM
        workers w  // From workers
    INNER JOIN
        departments d // and departments 
    ON
        w.dep_id = d.id // where workers dep_id vakue is in 
                        // departments id column 
                        // INNER JOIN get ONLY  that data
    WHERE
        d.city = 'X' // and filter by city name

now you can replace select part with

 UPDATE
        w  // Update table which name or alias is w
    SET
        w.salary = w.salary * 1.1 / set salary to current salary * 1.1

edited Jan 18 '16 at 20:14

answered Jan 18 '16 at 17:15

Nino Mirza Mušić

140
1
4

inner join on workers and departments will return all workers which dep_id are in id column of table departments. Where will filter this result, so we will have only workers for departments which are in city 'X'. now we will update salary for all these workers to current salary * 1.1 – Nino Mirza Mušić Jan 18 '16 at 19:27
Can you update the answer with the same thing? I'm aware of how SQL works but just a request to follow community guidelines. – Phani Jan 18 '16 at 19:53
I believe that now is god enough :) – Nino Mirza Mušić Jan 18 '16 at 20:15

Give a raise to people who work in X city?

3 Answers3