Update Query issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update Query issue

Author

Topic

anish20in
Starting Member

18 Posts

Posted - 2010-05-20 : 11:01:13

I have following table
CREATE table #tempM(MId int,MNo varchar(50),MDate datetime)
INSERT INTO #tempM values (121,'LPT050510230','05/05/2010')
INSERT INTO #tempM values (222,'LPT050610230','05/06/2010')
INSERT INTO #tempM values (320,'LPT050710230','05/07/2010')
INSERT INTO #tempM values (411,'LPT050710230','05/07/2010')
INSERT INTO #tempM values (412,'LPT050810230','05/08/2010')
INSERT INTO #tempM values (528,'LPT050910230','05/09/2010')

I want a random number increment for the Mno from the position 10.
here the number from 10th position is 230.Need to increase 230,231,232 depends on the order of MId

The following result wanted

121 LPT050510230 2010-05-05 00:00:00.000
222 LPT050610231 2010-05-06 00:00:00.000
320 LPT050710232 2010-05-07 00:00:00.000
411 LPT050710233 2010-05-07 00:00:00.000
412 LPT050810234 2010-05-08 00:00:00.000
528 LPT050910235 2010-05-09 00:00:00.000

Please help me.Urgent.............

ANI

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-05-20 : 11:09:06

Here is one way:

SELECT
	Mid,
	MNoStart + CAST(MNoEnd + RowNum AS VARCHAR(3)) AS MNo,
	MDate
FROM
(	
	SELECT
		Mid,
		LEFT(MNo, LEN(MNo) - 3) AS MNoStart,
		CAST(RIGHT(MNo, 3) AS INT) AS MNoEnd,
		ROW_NUMBER() OVER (ORDER BY MidMNo) AS RowNum,
		MDate
	FROM 
		#tempM
) AS T

Replace Mno with Mid in the ROW_NUMBER

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-05-20 : 11:12:29

Here is the update to yout temp table. I miss read teh requirement

UPDATE 
	M
SET 
	MNo = T.MNoStart + CAST(T.MNoEnd + T.RowNum AS VARCHAR(3))
FROM
	#TempM AS M
INNER JOIN
	(
		SELECT
			Mid,
			LEFT(MNo, LEN(MNo) - 3) AS MNoStart,
			CAST(RIGHT(MNo, 3) AS INT) AS MNoEnd,
			ROW_NUMBER() OVER (ORDER BY Mid MNo) AS RowNum
		FROM 
			#tempM
	) AS T
	ON M.Mid = T.Mid
	
SELECT *
FROM #TempM

Replace MNo with Mid in ROW_NUMBER

anish20in
Starting Member

18 Posts

Posted - 2010-05-20 : 11:43:27

i also have the rows like
INSERT INTO #tempM values (833,'LPT050710003','05/07/2010')
INSERT INTO #tempM values (885,'LPT050710006','05/07/2010')
INSERT INTO #tempM values (894,'LPT050710015','05/07/2010')
INSERT INTO #tempM values (1030,'LPT050710111','05/07/2010')
also other rows between these

Then I want the reult
121 LPT050510001 2010-05-05 00:00:00.000
222 LPT050610002 2010-05-06 00:00:00.000
320 LPT050710003 2010-05-07 00:00:00.000
411 LPT050710004 2010-05-07 00:00:00.000
412 LPT050810005 2010-05-08 00:00:00.000
528 LPT050910006 2010-05-09 00:00:00.000
.
.
.
833 LPT050910057 2010-05-09 00:00:00.000
.
.
.
.
1030 LPT050910128 2010-05-09 00:00:00.000

ANI

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-20 : 11:46:25

Thats not what you said in your OP. Please provide your complete requirement to begin with.

What do you want to do with these records? How should they be updated?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-05-20 : 12:04:29

I made a slight change, if this is not what you are looking for please post a more precise definition of what you want for output:

UPDATE 
	M
SET 
	MNoStart + RIGHT('000' + CAST(MNoEnd + RowNum AS VARCHAR(3)), 3) AS MNo,
FROM
	#TempM AS M
INNER JOIN
	(
		SELECT
			Mid,
			LEFT(MNo, LEN(MNo) - 3) AS MNoStart,
			CAST(RIGHT(MNo, 3) AS INT) AS MNoEnd,
			ROW_NUMBER() OVER (ORDER BY Mid) AS RowNum
		FROM 
			#tempM
	) AS T
	ON M.Mid = T.Mid

anish20in
Starting Member

18 Posts

Posted - 2010-05-24 : 01:27:31

Thank you.........All.I change the last query.Finally I got the answer.

ANI

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources