1

I have such a query:

UPDATE `user` 
SET mail='abc@abc' 
WHERE id = 
    IF((SELECT admin FROM user WHERE id = '1') > 2, 
       (SELECT id FROM user WHERE id='2'), 
       (SELECT id FROM user WHERE id = '1')
      )

as a result I get error message:

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

I want tu update column named 'mail' in all rows from 'user' table where 'id' of row is 1 or 2 depending on value of another column named 'admin'. Please help with corect syntax.

Kalreg
  • 931
  • 3
  • 12
  • 31

2 Answers2

0

  1. when using same table multiple times in a query, you need to use aliases:

    SELECT * FROM users u1 INNER JOIN users u2 ON ...

  2. whats the point of those sub-selects? for example, isn't this part "(SELECT id FROM user WHERE id='2')" equal to just writing "2" ?

poncha
  • 7,726
  • 2
  • 34
  • 38
  • value id=2 is just an example. Value 2 in this example changes depeneding on user login values stored in session. – Kalreg Mar 26 '12 at 23:00
  • @Kalreg: O.K., but it's a nonsensical example. `SELECT id FROM user WHERE id = example_value_stored_in_session` is always either `example_value_stored_in_session` or `NULL`. – ruakh Mar 26 '12 at 23:01
  • @Kalreg: yes, but selecting an **id** for a record for which you already know the id is pointless... ;) – poncha Mar 26 '12 at 23:01
  • my query is a little more complex, so it doesnt seem pointless. I just made it more basic for putting it all easier to answer :) – Kalreg Mar 26 '12 at 23:08
0
UPDATE `user`
SET email='abc@abc'
WHERE id = (SELECT id FROM (SELECT IF((SELECT admin FROM user WHERE id = '1') > 2,
                                      (SELECT id FROM user WHERE id = '2'),
                                      (SELECT id FROM user WHERE id = '1')) AS id
                           ) AS t
           );
Sujoy Gupta
  • 1,424
  • 1
  • 10
  • 12