72

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 != '' 
3
  • 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. Commented Apr 7, 2011 at 0: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? Commented Apr 7, 2011 at 0: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 Commented Apr 10, 2011 at 0:45

6 Answers 6

81

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
Sign up to request clarification or add additional context in comments.

7 Comments

no it's not possible, but i'm selecting from a temporary table t1 that I'm creating from update target.
nope, but I read this article xaprb.com/blog/2006/06/23/…. Let's wait for the OP to test ... ;)
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!
@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.
Trying this on sql developer for Oracle DB. It gives error at the , and says missing SET keyword. Any leads on this?
|
23

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.

Comments

5
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
                )

6 Comments

what is Test2 in Update Test2 clause?
@zerkms - Realized he wasn't talking about updating one table from another but instead updating the same table.
as in manji's answer I think that this will not work (since you're selecting the same table you're updating)
@zerkms - Nope. It will work. You simply have to alias the table in the subqueries.
This works in HSQLDB, in addition to MySQL. (Some of the other MySQL answers don't work in HSQLDB.)
|
3
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

2 Comments

this does not work
The correct SQL is: UPDATE financialyear SET firstsemfrom = dt2.firstsemfrom, firstsemto = dt2.firstsemto, secondsemfrom = dt2.secondsemfrom, secondsemto = dt2.secondsemto from financialyear, financialyear dt2 WHERE financialyear.financialyearkey = 141 AND dt2.financialyearkey = 140
-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;

Comments

-1
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

1 Comment

Working Properly,verified

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.