Updation Querey

Question

Hello friends

I am actualy doing cleaning of data in my database for my organization

here i am facing the sceneraio  i have Column UniuqeLabels with their Partydesigncodes

in some uniquelabels  for some transaction their partydesigndcode is missing  but in other transactions i can see their partydesigncodes.

Each unique label belongs to only one partydesigncode but one partydesigncode can have so many uniquelabels.

eg

UniqueLabel        PartyDesigncode

1. 11                        a

2. 12                        b

3. 13                        a

4. 12                       

5. 13                        c

6. 16                         d

7. 11                         a

8. 11                          

9. 14

10. 13                         a

So you can see for label 11 i have partydesigncode as 'a' but it is missing for its transaction at row no 8

same for 12 partydesigncode is ' b' but is missing for its transaction at serial 4

same is situation with 14 for which there is no refrence there so we can leave that as it is.

It needs simple self joinging but i am not getting idea how to update the whole  databases with that querey

which contains  15 millon rows atleast

With Regards

---

Asrar

Answer 1

This query will do it.

update t
set PartyDesigncode=(select top 1 PartyDesigncode from test t2 where t.uniquelabel=t2.UniqueLabel and PartyDesigncode is not null)
from test t