Retrofitting an Incremental ID column with UPDATE

I have a MySQL table with an ID column, intended to be imported from external data, but for this one case there were no IDs provided. Therefore, I have a column of pure zeros. I need to update this column to have unique values in each row, and these numbers have no significance. I can think of several ways to do it, but I'm wondering what t开发者_Go百科he correct way would be. My first idea was something like UPDATE table SET id = (SELECT max(id)+1 FROM table) - but I like elegant solutions when available.

Edit: It turns out running this query triggers 1093: You can't specify target table for update in FROM clause - I guess that means I really need a more elegant solution, too.

---

UPDATE t
SET    t.id = q.id
FROM   TABLE t
       INNER JOIN
              ( SELECT MAX(id)+1 AS id
              FROM    TABLE
              )
              q
       ON     1 = 1

---

Can't you generate a View of the table where you add an expression column wich can generate a unique identifier much like you are proposing and then import the view?

---

I ended up doing this, since nothing was working:

update table set accountnumber = floor(rand() * 999999999)