Help with SQL update

Question

Help with SQL update

Jpotucek

SSCoach

Points: 18583
More actions
December 28, 2012 at 10:11 am

#268146

I have one table here is the DDL
Create table dbo.UFM_AGENTS (
TS_ID int not null,
TS_TITLE NVARCHAR(160) null,
TS_ACTIVEINACTIVE int null,
TS_LASTMODIFIER int null,
TS_FA_LAST_NAME NVARCHAR(60) null,
TS_FA_FIRST_NAME NVARCHAR(40) null,
TS_FA_NUM NVARCHAR(12) null,
TS_GA_NUM NVARCHAR(12) null,
TS_GA_LAST_NAME NVARCHAR(40) null,
TS_FA_MIDDLE_INIT NVARCHAR(40) null,
TS_FA_NAME_SUFFIX NVARCHAR(10) null,
TS_FA_STATUS NVARCHAR(30) null,
TS_FA_ADDRESS_LINE_1 NVARCHAR(100) null,
TS_FA_ADDRESS_LINE_2 NVARCHAR(100) null,
TS_FA_CITY NVARCHAR(100) null,
) ;
and here is some sample data:
TS_IDTS_TITLETS_ACTIVEINACTIVETS_LASTMODIFIERTS_FA_LAST_NAMETS_FA_FIRST_NAMETS_FA_NUMTS_GA_NUMTS_GA_LAST_NAMETS_FA_MIDDLE_INITTS_FA_NAME_SUFFIXTS_FA_STATUSTS_FA_ADDRESS_LINE_1TS_FA_ADDRESS_LINE_2TS_FA_CITY
78GA1124LastDaniel0222702227LastNNULLTERM-DEBT RECOV3121 Peterson Pkwy NFargo
I have a need to select on 1200 different TS_FA_NUM's and change the TS_ACTIVEINACTIVE value from a 0 to a 1. How can I do this?

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

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

Roland Alexander STL SSCrazy Points: 2915 More actions · Answer 1

Which 1200? Any 1200? Or 1200 selected on some criteria?

Roland Alexander
The Monday Morning DBA
There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer

Lowell SSC Guru Points: 323546 More actions · Answer 2

the key is what criteria to use to find that 1200, or whatever the number actually is;

Create table dbo.UFM_AGENTS (

TS_ID int not null,

TS_TITLE NVARCHAR(160) null,

TS_ACTIVEINACTIVE int null,

TS_LASTMODIFIER int null,

TS_FA_LAST_NAME NVARCHAR(60) null,

TS_FA_FIRST_NAME NVARCHAR(40) null,

TS_FA_NUM NVARCHAR(12) null,

TS_GA_NUM NVARCHAR(12) null,

TS_GA_LAST_NAME NVARCHAR(40) null,

TS_FA_MIDDLE_INIT NVARCHAR(40) null,

TS_FA_NAME_SUFFIX NVARCHAR(10) null,

TS_FA_STATUS NVARCHAR(30) null,

TS_FA_ADDRESS_LINE_1 NVARCHAR(100) null,

TS_FA_ADDRESS_LINE_2 NVARCHAR(100) null,

TS_FA_CITY NVARCHAR(100) null,

)

insert into ufm_Agents

SELECT '78','GA','1','124','Last','Daniel','02227','02227','Last','N','NULL','TERM-DEBT ','RECOV','3121 Peterson Pkwy N','Fargo' UNION ALL

SELECT '79','GA','0','124','Last','Daniel','02227','02227','Last','N','NULL','TERM-DEBT ','RECOV','3121 Peterson Pkwy N','Fargo'

select * from UFM_AGENTS WHERE TS_ACTIVEINACTIVE = 0

UPDATE UFM_AGENTS

SET TS_ACTIVEINACTIVE = 1

WHERE TS_ACTIVEINACTIVE = 0

AND OtherCriteria = ????

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jpotucek SSCoach Points: 18583 More actions · Answer 3

My criteria is I was given a list of 1200 TS_FA_NUM values and they all have to be changed from TS_ACTIVEINACTIVE = 0 to TS_ACTIVEINACTIVE = 1

Sounds like I will have to craft the update this way?

UPDATE UFM_AGENTS_test

SET TS_ACTIVEINACTIVE = 0

WHERE TS_ACTIVEINACTIVE = 1

AND TS_FA_NUM =02479

or TS_FA_NUM = 02407

or TS_FA_NUM = 02707

or TS_FA_NUM = 03487

;

Roland Alexander STL SSCrazy Points: 2915 More actions · Answer 4

Jpotucek (12/28/2012)
My criteria is I was given a list of 1200 TS_FA_NUM values and they all have to be changed from TS_ACTIVEINACTIVE = 0 to TS_ACTIVEINACTIVE = 1
Sounds like I will have to craft the update this way?
UPDATE UFM_AGENTS_test
SET TS_ACTIVEINACTIVE = 0
WHERE TS_ACTIVEINACTIVE = 1
AND TS_FA_NUM =02479
or TS_FA_NUM = 02407
or TS_FA_NUM = 02707
or TS_FA_NUM = 03487
;

Is there an index on TS_FA_NUM? Is the list in a text file that could be imported into SQL Server, say via SSIS? If not, I'd build a temp table, index UFM_AGENTS on TS_FA_NUM (or not, depending on the total number of rows vs. the number of rows we need to update), and use the temp table in a join.

Roland Alexander
The Monday Morning DBA
There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer

Jpotucek SSCoach Points: 18583 More actions · Answer 5

Jpotucek

SSCoach

Points: 18583

December 28, 2012 at 12:10 pm

#1572190

awesome stuff! Thank you all !!!