Howto update 3000 specific records with a single statement ?

Question

Howto update 3000 specific records with a single statement ?

SQL Kidu

Ten Centuries

Points: 1296
More actions
March 30, 2011 at 12:54 am

#237048

I have a list of 3000 customer IDs not in random sequence. There is another table which contains this IDs.
I have to update a string field with the value 'S' for all these particular records.
What would be the best way to create a script ?
Do I have to write 3000 update statements ?

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

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

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

Hard to know what to tell you as we can't see from here what you see. It would help if you would provide the DDL for the tables involved, sample data for the tables, expected results based on the sample data, and what you have tried so far to solve your problem.

Please read the first article I reference below regarding asking for help, it will show you what we need to provide you the best help possible. In return you will get tested code in return.

SQL Kidu Ten Centuries Points: 1296 More actions · Answer 2

Hi,

Sorry . I am sharing the scripts below

--------------------------------------------------------------------------------------------------------

/****** Object: Table [dbo].[tmpCustomers] Script Date: 03/30/2011 02:11:47 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[tmpCustomers](

[CustomerNo] [varchar](50) NULL,

[ChargedYN] [varchar](1) NULL,

[CustomerName] [nchar](100) NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'''Y'' for Yes ''N'' for No' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tmpCustomers', @level2type=N'COLUMN', @level2name=N'ChargedYN'

--------------------------------------------------------------------------------------------------------

-- Use the following script to insert records

--------------------------------------------------------------------------------------------------------

Insert into tmpCustomers select 'S16','Y','TRACA ENTERTAINMENTS'

Insert into tmpCustomers select 'S17','Y','TAXG ENTERTAINMENTS'

Insert into tmpCustomers select 'S18','Y','XXX ENTERTAINMENTS'

Insert into tmpCustomers select 'S19','Y','NEXT ENTERTAINMENTS GROUP, INC'

Insert into tmpCustomers select 'S1A','Y','ARETE'

Insert into tmpCustomers select 'S1B','Y','TAURUS ENTERTAINMENTS INC'

Insert into tmpCustomers select 'S1C','Y','AMERICA DANCE'

Insert into tmpCustomers select 'S1D','Y','PR GROUP'

Insert into tmpCustomers select 'S1E','Y','MAGIC VISUALS'

Insert into tmpCustomers select 'S1F','Y','AVARN GROUP'

Insert into tmpCustomers select 'S1G','Y','SHAHIS WORKD'

Insert into tmpCustomers select 'S1H','Y','PRIYA WORLD'

Insert into tmpCustomers select 'S1I','Y','PARK ENGTRA'

Insert into tmpCustomers select 'S1J','Y','FAITHFUL'

Insert into tmpCustomers select 'S1K','Y','GALFAR'

You can see different records now. My problem is this ;

Some one sent me customer numbers S1B,S1H and asked me to set ChargedYN flag to 'N'. For this should I write two update statements or is there any better way ?

( Actual scenario has 3000 customer numbers to update )

Hope this clarifies the question

HowardW One Orange Chip Points: 29109 More actions · Answer 3

Ok, I think I see what you're after. So, saying you had a table where you'd imported this list called rowstoupdate, you could use MERGE (or the proprietary UPDATE...FROM) to update the rows that matched on your key column. Something like the below:

MERGE tmpCustomers AS Target

USING rowstoupdate AS Source

ON ( TARGET.CustomerNo = Source.CustomerNo )

WHEN MATCHED

THEN UPDATE

SET TARGET.ChargedYN = 'N';

UMG Developer SSChampion Points: 13482 More actions · Answer 4

Here is an UPDATE version of what Howard provided:

UPDATE Target

SET ChargedYN = 'N'

FROM tmpCustomers AS Target

INNER JOIN rowstoupdate AS Source

ON Target.CustomerNo = Source.CustomerNo;

If you don't have a table with the customer numbers in it you can do it this way:

UPDATE tmpCustomers

SET ChargedYN = 'N'

WHERE

CustomerNo IN ('S1B', 'S1H');

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

Here is another version of the UPDATE statement using a table (dbo.updCustomers in the code below) with a list of customer numbers to update:

update dbo.tmpCustomers set

ChargedYN = 'N'

where

ChargedYN = 'Y'

and CustomerNo in (select CustomerNo from dbo.updCustomers);