How to update table without primary key

Question

How to update table without primary key

vision59

SSCrazy

Points: 2601
More actions
May 23, 2008 at 6:13 am

#70489

I'm in situation I need to mask data for testing purpose and table do not have primary key to cursor for mask it . For example student table below I need to mask. I'm looking for logic to mask as below
OPEN c_table
FETCH NEXT FROM c_table INTO @table_counter
WHILE @@fetch_status = 0
BEGIN
UPDATE student
set class_id =
studnet_id =
ssn =

Viewing 10 posts - 1 through 9 (of 9 total)

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

Kent Waldrop Hall of Fame Points: 3064 More actions · Answer 1

It looks to me like you can use a straight-up UPDATE statement without bothering with the cursor -- what is preventing this?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

I'd have to agree with Kent, partly because you are only giving us a portion of what you are trying to do. It would help to see all of your code, not just a snippet.

vision59 SSCrazy Points: 2601 More actions · Answer 3

I want to use cursor to do this way

OPEN c_table

FETCH NEXT FROM c_table INTO @table_counter

WHILE @@fetch_status = 0

BEGIN

UPDATE student

set class_id = "1234" so next row will be 1235 ....

studnet_id =

ssn =

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

Use a tally table for that. It's a lot more efficient:

http://www.sqlservercentral.com/articles/TSQL/62867/[/url]

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Could also be a running total solution. Not having enough information it is hard to say. How about the DDL for the tables, sample data, and expected results. All of this will give you better results from your request for help.

Read this article, it will help you when asking for help: http://www.sqlservercentral.com/articles/Best+Practices/61537/

😎

Sergiy SSC Guru Points: 110208 More actions · Answer 6

Why not just add IDENTITY column?

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 7

Why not, indeed... that would be the way to go...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Could be, but it is hard to say when you don't have enough information to give a proper answer.

😎

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 9

True enough...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)