How To UPdate the Multiple Record

Question

How To UPdate the Multiple Record

sqlfriends@sql.com

SSCommitted

Points: 1504
More actions
October 28, 2009 at 6:37 am

#73003

Dear All;
Pls Help This To Create The SP 🙂
The Table Like this
Id RegNo Name Doe
1 001 Vino 28-08-2009
2 002 Asha 10-08-2009
3 003 Rahu 11-11-2009
4 004 Suresh 12-11-2009
5 005 Suba 15-11-2009
I Want To Delete The Regnumber : 2
The Next Registernumber To Update The Sequence Number
Like this
Id RegNo Name Doe
1 001 Vino 28-08-2009
2 002 Rahu 11-11-2009
3 003 Suresh 12-11-2009
4 004 Suba 15-11-2009
Thanks & Advance;
A.Faijurrahuman

Viewing 8 posts - 1 through 7 (of 7 total)

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

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 1

Hi A.Faijurrahuman

Seems like you usin' your "Id" column in a wrong direction. Sequenced numbers without any gap should not be handled in a database but generated in client (when shown to the user).

Just think about this:

You've got a table with 1,000,000 rows and your delete the first row. This would cause 999,999 updates, which becomes a huge performance problem.

If you need a sequenced Id to show to the user handle this in your client application.

Greets

Flo

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 2

I agree with Flo. You don't want to do this. Renumbering all rows is a mistake based on data changes.

Perhaps you want to examine why you think you need to do this and find another way to handle this.

sqlfriends@sql.com SSCommitted Points: 1504 More actions · Answer 3

Yes i know ; But in case Such a Situation I Want To Update This Record;

How It's Possible;

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 4

Anyway... On SQL Server 2000 the only secure way to do this is a exclusive lock for your table and a index hint over your PK-column:

IF (OBJECT_ID('tempdb..#t') IS NOT NULL)

DROP TABLE #t;

GO

CREATE TABLE #t (Id INT PRIMARY KEY CLUSTERED, SomeInt INT);

INSERT INTO #t

SELECT 1, 100

UNION ALL SELECT 2, 200

UNION ALL SELECT 3, 300

UNION ALL SELECT 4, 400

DELETE FROM #t WHERE Id = 2;

DECLARE @id INT

SELECT @id = 1;

UPDATE t

SET @id = Id = @id + 1

FROM #t t WITH(INDEX(0), TABLOCKX);

SELECT * FROM #t;

Greets

Flo

sqlfriends@sql.com SSCommitted Points: 1504 More actions · Answer 5

Thanks for your response

Hi;

I Have to Execute the Query

DECLARE @REGNO int

SELECT @REGNO='090101502002'

UPDATE STUDENTMASTER

SET @REGNO = REGISTERNO =@REGNO + 1

FROM STUDENTMASTER STUDENTMASTER WITH(INDEX(0),TABLOCKX) WHERE REGISTERNO= '098001502053'

Table Types

Registerno varchar(12)

The Following Error ;

Msg 425, Level 16, State 1, Line 5

Data type varchar of receiving variable is not equal to the data type char of column 'RegisterNo'.

How to Run this pls Help me ya...........

Thanks&Advance;

A.Faijurrahuman

Software Developer

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 6

Please don't start asking a new question in this thread. Start a new thread if you have another error.

And please read the error message and look it up.

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 7

As the error says, you have to use CONVERT to convert an incremental number to a CHAR column.