0

hey i have been working on mysql for a school project. i made an employee table and a kids table.but i get an error 'you cant specify target table for update in from clause' what i am trying to do is give 10% raise to all employees who have been working for more than 20 and have a kid. here is the code.

update employee
set salary=salary*1.1
where exists (
select * from kids k,employee e where e.art=k.art and e.hire_date<='1998-11- 
16');

please help me guys :D

Kostas Kostakis
  • 1
  • 1
  • 1
    Details of table structure with sample data will be handy. In MySQL, you cannot refer the same table for update, which is also specified in a subquery inside the `Where` clause. – Madhur Bhaiya Nov 16 '18 at 17:02
  • Also, please don't use Old comma based Implicit joins and use Modern [Explicit `Join` based syntax](https://stackoverflow.com/q/5654278/2469308) – Madhur Bhaiya Nov 16 '18 at 17:03
  • i tried removing employee and the e in front on the columns but i get error 1175 saying i use safe update mode and that i dont use a key column :/ – Kostas Kostakis Nov 16 '18 at 17:19
  • Please read this link: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/q/333952/2469308). Based in it, please edit the question with some more details. Based on that, it will be easier to provide an answer by the community. – Madhur Bhaiya Nov 16 '18 at 17:20
  • What is the primary key in the `employee` table ? – Madhur Bhaiya Nov 16 '18 at 17:23
  • the primary key for employee is art and the primary key for kids is art which is a foreign key connecting employee and kids table – Kostas Kostakis Nov 16 '18 at 17:26
  • Then how come you are joining them using their individual PKs. what is FK in the child table ? – Madhur Bhaiya Nov 16 '18 at 17:28
  • @MadhurBhaiya I think that's implicit from his query, even though it is malformed. – Havenard Nov 16 '18 at 17:28
  • @Havenard my question was to OP. As per OP's previous comment, `art` column exists in both the table and it is PK in both the tables. So, in real life an employe can have more than one kid. So it really does not make sense that PK from the `child` table is being joined to PK from `employee` table. – Madhur Bhaiya Nov 16 '18 at 17:31
  • @MadhurBhaiya I see what you mean. This query rules out employees without kids, but would have to check if it isn't giving 10% raise per kid rather than 10% total. Perhaps `LEFT JOIN` works best, that is, making sure `kids` isn't `NULL`. – Havenard Nov 16 '18 at 17:36
  • art is pk in both tables and art fk in the child table. i wanted to use another column as pk in the kids table and art only as a fk but the professor said we had to have 2 primary on that table :/ by the way thank you so much for the code it worked. i will do some extra research on joins since we have been taught them in theory but not in sql commands :D – Kostas Kostakis Nov 16 '18 at 17:41
  • `UPDATE employees a LEFT JOIN kids b ON a.art = b.art SET a.salary = a.salary * 1.1 WHERE b.art IS NOT NULL AND a.hire_date <= '1998-11-16';` – Havenard Nov 16 '18 at 17:47

1 Answers1

0

you can use JOINs to achieve that. Please try below query.

UPDATE employee AS e
INNER JOIN kids AS k ON e.art=k.art
SET e.salary=salary*1.1
WHERE e.hire_date<='1998-11-16'
Mathavan Jeyadev
  • 41
  • 3
  • Semantically this query would be giving 10% raise *per kid*. It doesn't, but just because of the obscure ways how MySQL works internally. To make this query more secure and future proof, I'd compose it in a way that explicitly prevents that. – Havenard Nov 16 '18 at 17:54
  • This will work perfectly for all scenarios. UPDATE employee AS e INNER JOIN (SELECT DISTINCT art FROM kids) AS k ON e.art=k.art SET e.salary=salary*1.1 WHERE e.hire_date<='1998-11-16' – Mathavan Jeyadev Nov 17 '18 at 06:36