0

I have an employees table with an employeeNumber 1313 which I would like to change in the table by finding the maximum of all employee numbers and adding 1 to it. Which means 1313 will be changed to max(employeeNumber) + 1

I have a MySQL statement which returns the error 1093:

update employees set employeeNumber = (select max(employeeNumber) from employees) + 1 
where employeeNumber = 1313;

I have looked everywhere but I haven't found any question close to this one. I have seen solutions of inner join but don't know how to make it work in this situation.

A simple MySQL statement would be much appreciated.

pk.
  • 99
  • 3
  • 12
  • The linked question will help provide some explanation, but it doesn't solve the OP's problem. What you'll want to do is an outer join, so that it grabs the max() from ALL the records in employees. You can try something like this (untested, but should do what you want): `update employees set employeeNumber = max(t1.id) + 1 from employees t1 left outer join employees t2 on (t1.employeeNumber=t2.employeeNumber) where t2.employeeNumber = 1313` – RToyo Jun 08 '17 at 18:25
  • I think your 'from' is not valid in its position. – pk. Jun 08 '17 at 18:31
  • Sorry about that, my brain was in TSQL mode. The same idea should apply for MySQL though. I don't have a MySQL environment handy to toss a test together, but an outer join should solve your problem. – RToyo Jun 08 '17 at 20:15

0 Answers0