May 18, 2011 at 11:44 am
Hi Guys,
Update help,
Here is my data
ID# VALUE
C-1234 NULL
M-3456 NULL
After I run update statement end result should be like this
ID# VALUE
C-1234 M-3456
M-3456 NULL
Thanks for Help.
May 18, 2011 at 11:56 am
Is there anything in there that says which row's ID goes into which row's Value?
It looks like what you're trying to do is take the next row, and put it's ID into the prior row's Value. SQL doesn't really work that way, without something telling it what the relationship is between the two rows. Like a sequence column or something like that.
Without something telling it "this row is next after this other row", you can't guarantee that you'll get the results you want.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 18, 2011 at 12:25 pm
ID# VALUE ID
C-1234 NULL 1
M-3456 NULL 2
IF SOMETHING LIKE THIS THEN?
May 18, 2011 at 12:51 pm
That one's easy, assuming I got it right on what you're trying to do.
Start with this:
select *
from MyTable as MT1
inner join MyTable as MT2
on MT1.ID = MT2.ID -1;
You'll see the values in that.
Then you can either turn it into a Merge statement or an Update From statement. Use Merge if you're in SQL 2008, use Update From if you're in 2000 or 2005.
Since you posted in the 2008 forum, I'm assuming you're using 2008. So, here's a sample of how that might look:
USE ProofOfConcept ;
GO
IF OBJECT_ID(N'tempdb..T') IS NOT NULL
DROP TABLE #T ;
CREATE TABLE #T
(Col1 VARCHAR(10),
Col2 VARCHAR(10),
ID INT PRIMARY KEY) ;
INSERT INTO #T
(Col1, Col2, ID)
VALUES ('C10', NULL, 1),
('M10', NULL, 2) ;
SELECT *
FROM #T ;
MERGE INTO #T AS T1
USING #T AS T2
ON T1.ID = T2.ID - 1
WHEN MATCHED
THEN UPDATE
SET T1.Col2 = T2.Col1 ;
SELECT *
FROM #T ;
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 18, 2011 at 12:52 pm
Something to keep in mind:
The sample I gave only works if the ID column doesn't have any missing values. If it does, you need to start the Merge with a CTE and use a Row_Number or other ranking function for the Join column.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 18, 2011 at 1:04 pm
I appreciate your help, Here is the error that i am getting
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'H:1105181028588904' to data type int.
Note:- "H:1105181028588904' IS MY REAL ID VALUE.
May 19, 2011 at 6:57 am
Is there a way to tell from that what the next record in the sequence is?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 19, 2011 at 8:21 am
I am using MM+D+HH+N+SS+AUTOINCREAMET# FOR MY ID COLUMN.
May 19, 2011 at 8:45 am
IF OBJECT_ID(N'tempdb..T') IS NOT NULL
DROP TABLE #T ;
CREATE TABLE #T
(Col1 VARCHAR(10),
Col2 VARCHAR(10),
ID varchar(30) PRIMARY KEY) ;
INSERT INTO #T
(Col1, Col2, ID)
VALUES ('C10', NULL, 'H:1105181028588904'),
('M10', NULL, 'H:1106181028588905'),
('N10', NULL, 'H:1107181028588906'),
('O10', NULL, 'H:1105181028588902')
SELECT *
FROM #T ;
--9 is starting position of Auto Number
;WITH T11
AS
(
SELECT
Col1
,Col2,ID
,SUBSTRING(ID,9,LEN(ID)+9) AutoNumber
,ROW_NUMBER() OVER (ORDER BY SUBSTRING(ID,9,LEN(ID)-9)) AS RowNum FROM #T
)
update t2
set t2.Col2=t.Col1
from T11 t2
inner join T11 t
on t2.RowNum=t.RowNum+1
SELECT *
FROM #T ;
Try Using this Code..
Spandan Buch
May 19, 2011 at 9:27 am
Here is the problem, ID Column is not in sequence,
ID = H:1105181028581180
ID = H:1105181028581976
And so on……
May 19, 2011 at 9:41 am
rocky_498 (5/19/2011)
Here is the problem, ID Column is not in sequence,ID = H:1105181028581180
ID = H:1105181028581976
And so on……
Well then you don't have all the business requirement. That's a very EASY problem to solve once you define what previous / next row is.
Go back and ask the users.
May 19, 2011 at 10:14 am
As you said earlier "AUTOINCREAMET" is autoincrement no Correct?
May 20, 2011 at 6:28 am
rocky_498 (5/19/2011)
Here is the problem, ID Column is not in sequence,ID = H:1105181028581180
ID = H:1105181028581976
And so on……
Those look sequential to me. They'll sort correctly as strings if they all follow that format. That means Row_Number should give you sequential numerical IDs for the rows if you order it by that column.
Or are there IDs that don't sort correctly and the sample you gave is incomplete in that regard?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply