Hi all,

I have a table in which i have 2 columns.i.e. name and ID number as

Table1
--------------------------
Name || ID number
--------------------------
Mark || 204
Roger || 207
Ronny || 209
Robert || 309
mac || 390
black ||
Mark ||
Roger ||
Mac ||
Mark ||
Ronny ||
black || 409

I want to do a query on column 'Name' and where ever i find same name, i want to to insert the same ID number into the corresponding ID number. i.e output should be as:
Table1
--------------------------
Name || ID number
--------------------------
Mark || 204
Roger || 207
Ronny || 209
Robert || 309
mac || 390
black || 409
Mark || 204
Roger || 207
Mac || 390
Mark || 204
Ronny || 209
black || 409

I tried implementing this as:

UPDATE Table1 SET Table1.IDnumber = Table1.IDnumber WHERE Table1.Name LIKE Table1.Name;

But it does not do anything and gives the original table as the output.
I tried it by using 2 tables as well,by creating a duplicate table Table2 of Table1 and then putting a query on Table1.Name as:

UPDATE Table1, Table2 SET Table1.IDnumber=Table2.IDnumber WHERE Table1.Name LIKE Table2.Name;

But in this case also, i dont get the desired output. It shows the same original table only.
Please help...
Thanking you,

try this...
update .... where ... like rtrim(t1.nombre) || '%';

update it from where? a self-join is required, and that's what's the hard part of this problem

also, mysql doesn't use || for concatenation unless you take specific steps to run in standards mode

UPDATE Table1, Table2 SET Table1.IDnumber=Table2.IDnumber WHERE rtrim(Table1.Name) LIKE concat(rtrim(Table2.Name), '%');