Update row with data from another row in the same table

Question

I ve got a table which looks something like this   ID       NAME       VALUE    ----------------------------  1       Test       VALUE1    2       Test2      VALUE2    1       Test2                4       Test                 1       Test3      VALUE3     I m looking for a way to update the values  Test2  and  Test  with the data from other rows in the  VALUE  column with the same  NAME   The ID is not unique here  a composite key of the ID and NAME make a row unique   For example  the output I m looking for is   ID       NAME       VALUE    ----------------------------  1       Test       VALUE1    2       Test2      VALUE2    1       Test2      VALUE2    4       Test       VALUE1    1       Test3      VALUE3     If it was in another table I d be fine  but I m at a loss as to how I can reference a different row within the current table with the same NAME value   Update  After modifying manji query  below is the query I used for a working solution   Thanks all   UPDATE data table dt1  data table dt2  SET dt1 VALUE   dt2 VALUE  WHERE dt1 NAME   dt2 NAME AND dt1 VALUE      AND dt2 VALUE

User · Answer

Here s my go   UPDATE test as t1      INNER JOIN test as t2 ON          t1 NAME   t2 NAME AND          t2 value IS NOT NULL  SET t1 VALUE   t2 VALUE    EDIT  Removed superfluous t1 id    t2 id condition

User · Answer

UPDATE financialyear    SET firstsemfrom   dt2 firstsemfrom         firstsemto   dt2 firstsemto         secondsemfrom   dt2 secondsemfrom         secondsemto   dt2 secondsemto   from financialyear dt2  WHERE financialyear financialyearkey   141    AND dt2 financialyearkey   140

User · Answer

Update MyTable Set Value                     Select Min  T2 Value                   From MyTable As T2                 Where T2 Id  lt  gt  MyTable Id                     And T2 Name   MyTable Name                   Where   Value Is Null Or Value            And Exists                    Select 1                 From MyTable As T3                 Where T3 Id  lt  gt  MyTable Id                     And T3 Name   MyTable Name

User · Answer

Try this   UPDATE data table t   SELECT DISTINCT ID  NAME  VALUE                         FROM data table                        WHERE VALUE IS NOT NULL AND VALUE        t1    SET t VALUE   t1 VALUE  WHERE t ID   t1 ID    AND t NAME   t1 NAME

User · Answer

If you just need to insert a new row with a data from another row       insert into ORDER ITEM select   from ORDER ITEM where ITEM NUMBER  123

[mysql] Update row with data from another row in the same table

Examples related to mysql