update sql query where select max

Question

I want a query that updates the max number of the id... here is my code..

UPDATE `jobs` SET `sectors` = '12' WHERE `id` = (SELECT MAX(`id`) FROM `jobs`)

the error says : #1093 - You can't specify target table 'wpjb_job' for update in FROM clause

what can I do to make this happen?

doublesharp · Accepted Answer · 2012-09-24 04:57:10Z

3

If id is a unique field then you can use the query below:

UPDATE `jobs` 
SET `sectors` = '12' 
ORDER BY `id` DESC LIMIT 1

If it is not unique then you need to use a temporary table:

UPDATE `jobs`
SET `sectors` = '12'
WHERE `id` = (SELECT `id` FROM (SELECT MAX(`id`) AS `id` FROM `jobs`) as temp)

answered Sep 24, 2012 at 4:54

doublesharp

27.9k6 gold badges56 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Buzz · Accepted Answer · 2012-09-24 04:56:59Z

0

as AVD says use table alias like this

  UPDATE jobs
    SET sectors= '12'
    WHERE ID = (select * from (SELECT MAX(ID) FROM jobs) as t)

answered Sep 24, 2012 at 4:56

Buzz

6,3806 gold badges37 silver badges48 bronze badges