how to update value from c2 to c1

Question

how to update value from c2 to c1

s.ravisankar

SSChasing Mays

Points: 647
More actions
April 4, 2014 at 8:59 am

#284272

c1<---->c2
1<---->2
2<---->3
3<---->4
11<---->12
12<---->13
13<---->14
We want to update last value of c2 to c1 , which value is linking c2 and c1
output
c1<---->c2
4<---->2
4<---->3
4<---->4
14<---->12
14<---->13
14<---->14

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Can you provide more information on what your rules for performing the update are?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

s.ravisankar SSChasing Mays Points: 647 More actions · Answer 2

1 is updated to 2

then 2 is updated to 3

then 3 is updated to 4

here I want to update 4 for all (1,2,3) because 4 is the final value

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 3

s.ravisankar (4/7/2014)
1 is updated to 2
then 2 is updated to 3
then 3 is updated to 4
here I want to update 4 for all (1,2,3) because 4 is the final value

How do make the distinction between different groups of c1?

Because there is a gap between 3 and 11?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

gstarsaini Ten Centuries Points: 1010 More actions · Answer 4

"c1<---->c2

4<---->2

4<---->3

4<---->4

14<---->12

14<---->13

14<---->14"

How are you deciding when to use 4 and to use 14??

s.ravisankar SSChasing Mays Points: 647 More actions · Answer 5

c1<---->c2

1<---->2

2<---->3

3<---->4

11<---->12

12<---->13

13<---->14

We have data like this

I)

In transaction 1 : 1 updated to 2

In transaction 2 : 2 updated to 3

In transaction 3 : 3 updated to 4

II)

In transaction 1 : 11 updated to 12

In transaction 2 : 12 updated to 13

In transaction 3 : 13 updated to 14

There is no groups are available. we no need to think about gaps.

In this scenario I, I want to replace 4 (last transaction of 1 in c2) in place of C1 (1,2,3)

In this scenario II, I want to replace 14 (last transaction of 11 in c2) in place of C1 (11,12,13)

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 6

You didn't really specify the rules for your update. Currently we are mainly guessing the way that it should work. I wrote a small code that transform your input to the output that you requested, but since you didn't specify your needs, I'm not sure that this will work for you.

declare @t table (st varchar(20))

insert into @t (st)

values ('c1<---->c2'), ('1<---->2'), ('2<---->3'),('3<---->4'),('11<---->12'),('12<---->13'),('13<---->14')

update @t

set st = CASE WHEN substring(st,1, charindex('<',st)-1) < 10 THEN '4'+right(st,len(st)-1)

WHEN substring(st,1, charindex('<',st)-1) >= 10 AND substring(st,1, charindex('<',st)-1) < 100

THEN cast (CAST(substring(st,1, charindex('<',st)-1) as tinyint) / 10 * 10 + 4 as varchar(20))

+ right(st,len(st)-2)

end

where isnumeric(substring(st,1, charindex('<',st)-1)) = 1 and st not like '%[a-z]%<%'

select * from @t

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 7

s.ravisankar (4/7/2014)
c1<---->c2
1<---->2
2<---->3
3<---->4
11<---->12
12<---->13
13<---->14
We have data like this
I)
In transaction 1 : 1 updated to 2
In transaction 2 : 2 updated to 3
In transaction 3 : 3 updated to 4
II)
In transaction 1 : 11 updated to 12
In transaction 2 : 12 updated to 13
In transaction 3 : 13 updated to 14
There is no groups are available. we no need to think about gaps.
In this scenario I, I want to replace 4 (last transaction of 1 in c2) in place of C1 (1,2,3)
In this scenario II, I want to replace 14 (last transaction of 11 in c2) in place of C1 (11,12,13)

If you want anyone to be able to help you, please provide table DDL, sample data and desired output.

Please read the link in my signature about how to post questions.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 8

Talking about getting it wrong. I completely misunderstood your needs. You really need to explain what you want. It would be helpful if you'll also have a small script that creates the table, inserts the data and explain the output that you expect to get. Also can you explain why do you need to update 1 to 2 and then 2 to 3 and then 3 to 4 instead of just updating all of it directly to 4?

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 9

Adi Cohn-120898 (4/7/2014)
Talking about getting it wrong. I completely misunderstood your needs. You really need to explain what you want. It would be helpful if you'll also have a small script that creates the table, inserts the data and explain the output that you expect to get. Also can you explain why do you need to update 1 to 2 and then 2 to 3 and then 3 to 4 instead of just updating all of it directly to 4?
Adi

It seems your crystal ball is in the shop for repairs? 😀

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 10

Koen Verbeeck (4/7/2014)
It seems your crystal ball is in the shop for repairs? 😀

repairs and upgrade:-).

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

s.ravisankar SSChasing Mays Points: 647 More actions · Answer 11

I am sorry, This is first time I am coming for Forum, Give suggestions if I want to improve the things both Personally and Technically.

CREATE TABLE #Temp1

(

[Id] varchar(5),

[NewId] varchar(5)

)

INSERT INTO #Temp1 ([Id], [NewId])

select '1','2'

union all

select '2','3'

union all

select '3','4'

union all

select '5','6'

union all

select '6','7'

union all

select '7','8'

union all

select '11','12'

union all

select '12','13'

union all

select '13','14'

select * from #Temp1

In #Temp1, you can see

"1" updated to "2" then

"2" updated to "3" then

"3" updated to "4"

For this "4" is the FinalId so I want to update "4" instead of 1/2/3 in #Temp2,(Note : "4" not updated to Any value)

"5" updated to "6" then

"6" updated to "7" then

"7" updated to "8"

For this "8" is the FinalId so I want to update "8" instead of 5/6/7 in #Temp2,(Note : "8" not updated to Any value)

CREATE TABLE #Temp2

(

[Id] varchar(5),

[Note] varchar(100),

date1 datetime

)

INSERT INTO #Temp2 ([Id], [Note],[date1])

select '1','A1',GETDATE()

union all

select '2','A2',GETDATE()-1

union all

select '3','A3',GETDATE()-2

union all

select '4','A4',GETDATE()-4

union all

select '5','A11',GETDATE()-3

union all

select '6','A12',GETDATE()-4

union all

select '7','A13',GETDATE()-2

union all

select '8','A14',GETDATE()-1

union all

select '11','A5',GETDATE()-3

union all

select '12','A6',GETDATE()-4

union all

select '13','A7',GETDATE()-2

union all

select '14','A8',GETDATE()-1

select * from #Temp2

I want to update #Temp2 [Id] column with FinalId(i.e. [NewId]) from #Temp1

Output Required in #Temp2 is

select '4'[Id],'A1'[Note],GETDATE()[Date1]

union all

select '4','A2',GETDATE()-1

union all

select '4','A3',GETDATE()-2

union all

select '4','A4',GETDATE()-4

union all

select '8','A11',GETDATE()-3

union all

select '8','A12',GETDATE()-4

union all

select '8','A13',GETDATE()-2

union all

select '8','A14',GETDATE()-1

union all

select '14','A5',GETDATE()-3

union all

select '14','A6',GETDATE()-4

union all

select '14','A7',GETDATE()-2

union all

select '14','A8',GETDATE()-1

Hope this will give clear Idea. If not reply me not an issue.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 12

This might work for you. You should study about recursive CTEs to understand what it is doing.

If you have questions, please ask them.

WITH rCTE AS(

SELECT Id

,[NewId]

,1 AS n

FROM #Temp1

UNION ALL

SELECT r.Id --This will remain as our row identifier

,t.[NewId] --This will change until it gets to the last value

,r.n + 1 --This is a counter. The highest value indicates the last value

FROM #Temp1 t

JOIN rCTE r ON t.Id = r.[NewId]

),

rownums AS(

SELECT [NewId] --This contains all subsequent values

,Id --This is our row identifier

,ROW_NUMBER() OVER( PARTITION BY Id ORDER BY n DESC) rn --This function will generate an inverse value of n (our counter)

FROM rCTE

)

UPDATE t SET

Id = r.[NewId] --using our CTE, we update the second table with the last value that it's now identified by rn = 1

FROM rownums r

JOIN #Temp2 t ON r.Id = t.Id

WHERE rn = 1;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2