Specifying same table in subquery in main UPDATE statement

Question

I'm getting error below for second query at the bottom. Any idea idea why?

Thanks

ERROR: Error Number: 1093

You can't specify target table 'menus' for update in FROM clause

WORKS:

INSERT INTO menus
(name, controller, parent)
VALUES
('A', 'B', (SELECT id FROM menus menus_alias WHERE SHA1(menus_alias.id) = '1'))

BOTH DON'T WORK:

UPDATE menus SET
parent = (SELECT id FROM menus menus_alias WHERE SHA1(menus_alias.id) = '1')
WHERE SHA1(id) = '5'

UPDATE menus menus_alias SET
menus_alias.parent = (SELECT id FROM menus WHERE SHA1(id) = '1')
WHERE SHA1(menus_alias.id) = '5'

Checked out these:

and some others

score 3 · Accepted Answer · answered Jun 07 '13 at 11:50

3

Use JOIN

UPDATE menus m1 JOIN menus m2
    ON SHA1(m1.id) = '5' AND SHA1(m2.id) = '1'
   SET m1.parent = m2.id

answered Jun 07 '13 at 11:50

peterm

91,357
15
148
157

Exactly what I wanted. Thank you. – BentCoder Jun 07 '13 at 12:13

score 1 · Answer 2 · answered Jun 07 '13 at 11:44

1

You can try this

UPDATE menus SET
parent =(
    SELECT id FROM (
        SELECT id FROM menus
    ) AS x 
    WHERE SHA1(id) = '1'
)
WHERE SHA1(menus_alias.id) = '5'

answered Jun 07 '13 at 11:44

R.P

500
2
6
17

score 0 · Answer 3 · answered Jun 07 '13 at 11:37

0

Try this workaround (both queries must be exec in the same mysql session):

SELECT 
    id INTO @za_id 
FROM 
    menus 
WHERE 
    SHA1(id) = '1';

UPDATE menus 
    SET parent = @za_id
WHERE 
    SHA1(id) = '5';

Also you are trying to update a table with values from the same table and since mysql doesn't know that the data selected is from different records then the records that should be updated it throws an error.

answered Jun 07 '13 at 11:37

Stephan

8,000
3
36
42

I wanted to do in a single query but if not possible I'll have to use two anyway. Lets see if it is possible in others' eyes first. Thanks – BentCoder Jun 07 '13 at 11:40

Specifying same table in subquery in main UPDATE statement

3 Answers3