mysql - Update new SQL column with random strings -

i have table , want add field identity

id | name | identity  1 |  sam |   2 |  joe |   3 |  jen | 

right there no data identity. have string of 5 random character (ex: kdu3k) populate each row.

what best way alter/update table in manner?

---

since have php backend, technically loop through sql statement identity = null, want know how sql.

while do not recommend doing this, because mysql makes aspects less fun, can done entirely in mysql dml without use of user-defined procedures. procedures allow use of procedural while loops, etc.

i've created sqlfiddle. first step create random values; in case ensured distinct in table afterwards, ensures there 1 less thing worry about.

-- create lots of random values without using proceure , loop. -- there may duplicates created. temporary table. -- simplified if there numbers table. create table idents (value char(5)); insert idents (value) values (left(md5(rand()), 5));               -- 1 insert idents (value) select (left(md5(rand()), 5)) idents;   -- 2 insert idents (value) select (left(md5(rand()), 5)) idents;   -- 4 insert idents (value) select (left(md5(rand()), 5)) idents; insert idents (value) select (left(md5(rand()), 5)) idents; insert idents (value) select (left(md5(rand()), 5)) idents; insert idents (value) select (left(md5(rand()), 5)) idents;   -- 64  -- delete duplicate values. while there may rare duplicate -- still left many random values. similar process -- used weed out existing used values. delete idents value in (   -- select * mysql quirk   select value (select * idents)   group value   having count(value) > 1); 

then random values have associated each person. done horrid simulation of "row_number" on derived relations , join.

set @a = 0; set @b = 0;  -- here ugly mysql magic, variables used simulate -- row_number. ymmv, "works here, now". note suspicious -- hack assign @b 0 "for each" joined item. update people p2   join (select p.id, i.value     -- give each person record row number     (select @a := @a + 1 rn1, id, @b := 0 hack people) p     -- give each random number row number     join (select @b := @b + 1 rn2, value idents)     -- , join on row number     on p.rn1 = i.rn2) pv   on p2.id = pv.id set p2.identity = pv.value 

again, ymmv.