Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2009
    Posts
    4

    Unanswered: Compare a column's value

    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,

  2. #2
    Join Date
    May 2002
    Location
    General Deheza, Cba, Arg.
    Posts
    276
    try this...
    update .... where ... like rtrim(t1.nombre) || '%';

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by achiola
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    May 2002
    Location
    General Deheza, Cba, Arg.
    Posts
    276
    Quote Originally Posted by achiola
    try this...
    update .... where ... like rtrim(t1.nombre) || '%';
    UPDATE Table1, Table2 SET Table1.IDnumber=Table2.IDnumber WHERE rtrim(Table1.Name) LIKE concat(rtrim(Table2.Name), '%');

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •