67

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 != ''
rogerdpack
  • 62,887
  • 36
  • 269
  • 388
Chris B
  • 825
  • 1
  • 6
  • 6
  • Something of a noob, but I'm sure this could be done using temporary tables. Select names where value is null into a temp table, then fill the values in, then fill those values back into the original table where the name matches. –  Apr 07 '11 at 00:23
  • 1
    Could you be more specific in how they take data from other rows? From the example, it looks as if you want to give all the ones with the same NAME the same VALUE as well, is this correct? Does the ID matter here? – Gustav Larsson Apr 07 '11 at 00:24
  • @Gustav, I found a solution (see below) but yes, the intention is to have the same columns of the same NAME to have the same VALUE. ID is irrelevant – Chris B Apr 10 '11 at 00:45

6 Answers6

79

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
manji
  • 47,442
  • 5
  • 96
  • 103
  • Is it possible in mysql to select from the table you're currently updating? – zerkms Apr 07 '11 at 00:30
  • 2
    no it's not possible, but i'm selecting from a temporary table `t1` that I'm creating from update target. – manji Apr 07 '11 at 00:33
  • 1
    nope, but I read this article http://www.xaprb.com/blog/2006/06/23/how-to-select-from-an-update-target-in-mysql/. Let's wait for the OP to test ... ;) – manji Apr 07 '11 at 00:37
  • 12
    I didn't use your exact code, but it prompted me to try another query: UPDATE data_table dt1, data_table dt2 SET dt1.VALUE = dt2.VALUE WHERE dt1.NAME = dt2.NAME AND dt1.VALUE = '' AND dt2.VALUE != '' which did work. I didnt realise you could use the same table twice in a query with different references. Thanks! – Chris B Apr 07 '11 at 03:31
  • 1
    @slick: It's entirely acceptable to answer your own question and accept that answer. That highlights the "correct" answer. Or you could edit manji's answer to be what actually worked. – T.J. Crowder Jul 04 '13 at 10:07
  • I used OPs method and it worked perfectly! Different references does the trick! – amurrell Aug 20 '15 at 18:24
  • 1
    Trying this on sql developer for Oracle DB. It gives error at the `,` and says missing SET keyword. Any leads on this? – Sudoss Jun 03 '20 at 12:50
20

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.

Gustav Larsson
  • 8,199
  • 3
  • 31
  • 51
4
Update MyTable
Set Value = (
                Select Min( T2.Value )
                From MyTable As T2
                Where T2.Id <> MyTable.Id
                    And T2.Name = MyTable.Name
                )
Where ( Value Is Null Or Value = '' )
    And Exists  (
                Select 1
                From MyTable As T3
                Where T3.Id <> MyTable.Id
                    And T3.Name = MyTable.Name
                )
Thomas
  • 63,911
  • 12
  • 95
  • 141
  • what is `Test2` in `Update Test2` clause? – zerkms Apr 07 '11 at 00:30
  • @zerkms - Realized he wasn't talking about updating one table from another but instead updating the same table. – Thomas Apr 07 '11 at 00:34
  • as in manji's answer I think that this will not work (since you're selecting the same table you're updating) – zerkms Apr 07 '11 at 00:36
  • @zerkms - Nope. It will work. You simply have to alias the table in the subqueries. – Thomas Apr 07 '11 at 16:11
  • This works in HSQLDB, in addition to MySQL. (Some of the other MySQL answers don't work in HSQLDB.) – Lyrl Apr 20 '16 at 18:26
  • When I run this I get `SQL Error (1093): Table 'MyTable' is specified twice, both as a target for 'UPDATE' and as a separate source for data` – Barry Oct 28 '19 at 15:11
2
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
sjngm
  • 12,423
  • 14
  • 84
  • 114
Prashant Khunte
  • 29
  • 1
0
UPDATE t SET t.VALUE = t1.VALUE

FROM data_table t,

(
  
SELECT DISTINCT ID, NAME, VALUE
    FROM data_table
    WHERE VALUE IS NOT NULL AND VALUE != ''

) t1

WHERE t.ID = t1.ID

AND t.NAME = t1.NAME
j__carlson
  • 1,346
  • 3
  • 12
  • 20
Mohd mueen raza
  • 1
-1

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;
Ishan Liyanage
  • 2,237
  • 1
  • 26
  • 25