compare column of one row with same column of other row and then if there is match update the third column

Question

0.00/5 (No votes)

See more:

Hi, I have 1 Million records in my table.
I want to compare one field called "name" for 1st row to same field of other row and in case of match update a column "parent" with that name . How do i do..?

Example :

Name code area Parent
------------------------------------
Nokia 610 cube
Investments 240 ahnc
Investments 241 hnnc
apple 500 sumo
Iphone 210 robert

so when Name = Name , then update Parent with that name

in this case :

Name code area Parent
------------------------------------
Nokia 610 cube
Investments 240 ahnc Investments
Investments 241 hnnc Investments
apple 500 sumo
Iphone 210 robert

Posted 18-Jan-16 9:28am

Member 12271876

Add a Solution

Comments

jaket-cp 19-Jan-16 5:23am

It should be possible to do it with an update on a recursive cte.
Or an update on lead/lag - not sure on this one.
But a simulated lead/lag on an join update should work.

In all the above possibilities the ordering of the records need to be defined.
Can you define the ordering of the records.

Also in your example, shouldn't
Investments 240 ahnc Investments
Investments 241 hnnc Investments
be
Investments 240 ahnc
Investments 241 hnnc Investments

As if comparing previous with current,
Investments (240) would be compared with Nokia (610) - no match so Parent would be empty.

Please clarify these points.
Thanks

3 solutions

Solution 1

Break the query up into concepts:

You only need to look at values that exist at least (or only?) twice. So you need to group-by and count them.

Then, all those with a count > 1 (you may need to use a HAVING clause) can be returned in a subquery for an UPDATE Statement setting parent=name

Now, refine this: to save processing time, you may find it useful to reduce the list for the UPDATE by checking to make sure at least one in each set has not already been attended to. This could be handled with a WHERE clause in your UPDATE.

Posted 19-Jan-16 1:56am

W∴ Balboos, GHB

Solution 4

Hi,

Hope this query will get the result. Since update is involved please try this with sample development data before testing with real records. If you are using 2012 please try with Lead Functions which will get rid of this CTE.

SQL

create table #tempDha(Id varchar(50),Code int,Parent varchar(50))

insert into #tempDha values('Nokia',610, 'cube')
,('Invest' ,240 ,'ahnc')
,('Invest', 241 ,'hnnc')
,('apple' ,500 ,'sumo')
,('Iphone', 210 ,'robert' )

with cte as
(
select dense_rank()over(PARTITION by Id order by code) as Ra,* from #tempDha)
update t set t.Parent= t.Parent + ' '+ t.Id
from #tempDha t join cte c on t.Id=c.Id
where Ra>1

Thanks
Dharani

Posted 21-Jan-16 1:23am

dharan1990

Updated 21-Jan-16 1:30am

v3

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sri Nivas (Vasu) · Accepted Answer · 2016-01-19T03:50:00

Solution 2

following is the required query for you.

update TableName set parent = name where  name in (	SELECT NAME FROM TableName GROUP BY Name having count(NAME) > 1)

Hope this will helps you.

Posted 19-Jan-16 3:50am

Sri Nivas (Vasu)