update a huge data in one query.

Question

Post reply

update a huge data in one query.

pallavi.unde

SSCommitted

Points: 1987
More actions
December 8, 2011 at 2:32 am

#251766

I have id in my column like
65
65,87,890
90,89
now i want to add comma at before first digit and after last digit compulsory
means i want a data like
,65,
,65,87,890,
,90,89,
there are total 18,000 records in the table so i need
how could i right a one query to update all record ???
pls reply
:crying:
Thanks & Regards,
Pallavi

Viewing 12 posts - 1 through 11 (of 11 total)

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

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

Ant-Green

SSC Guru

Points: 113507

December 8, 2011 at 6:33 am

#1418647

update table1 set col1 = ','+col1+','

pallavi.unde SSCommitted Points: 1987 More actions · Answer 2

i mean to say...

i want to update : 65,87,56 => ,65,87,56,

98,67,45 => ,98,67,45,

772 => ,772,

the query u given there i have to pass the condition of other reference id.

there are too many combinations but only i have to add the comma at first and last.

as i mentioned above

:discuss:

Thanks & Regards,
Pallavi

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

anthony.green (12/8/2011)
update table1 set col1 = ','+col1+','

This does exactly what you requested. Just test on a test db and see for yourself.

pallavi.unde SSCommitted Points: 1987 More actions · Answer 4

actually it is updating for all column with same value... i need to pass condition like where referenceid=@id

e.g :

collegeid name cityid stateid

1 ABC 34,56,54 1

2 PQR 78 2

3 LMN 78,56 2

here i need below o/p

collegeid name cityid stateid

1 ABC ,34,56,54 , 1

2 PQR ,78, 2

3 LMN ,78,56, 2

and the query u have given updates all cityid column

for that i need to give the condition,

update collegemaster set cityid=',78,56,' where collegeid=3

which is consuming so time as the table is having 18,000 record with different combination.

so need a script which will directly add 1 comma before first number and last number.

pls help...

Thanks & Regards,
Pallavi

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Why do you save your data like that? It's certainly NOT standard.

pallavi.unde SSCommitted Points: 1987 More actions · Answer 6

I am saving data in this manner because please take a look on below example..

streamgroupmaster table 1

streamgroupid NAme

1 Physics

2 Chemictry

3 Biology

Streammaster table 2

streamid name id(foreign key)

101 bsc. in physics,chemistry,bio 1,2,3

102 b.sc in physics 1

here the data is been selected via drop down so in drop down we are showing single as like table 2

but if user wants to retrieve the record of physics then the above stream will be display.

thats why i took the decision to take a data in this format..

Thanks & Regards,
Pallavi

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 7

Streammaster table 2
streamid name id(foreign key)
101 bsc. in physics,chemistry,bio 1,2,3
102 b.sc in physics 1

Who designed this table you, your organization or your customer? If it's given by customer I wonder why it's been accepted by your organization. If it's designed by someone in your organization, your customer will throw it straight away. The reason is simple 'It's far far away from database design standards'.

If it’s a homework question we would be happy to help you in reaching to a good design.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

+1 on it's a bad design. Stop the headache right now and learn how to do concatenation when you present the data. NOT when you save it.

Ant-Green SSC Guru Points: 113507 More actions · Answer 9

i dont get how the foreign key is working, thats if a foreign key has been recreated

how can a column with values

1

2

3

reference a row with value

1,2,3

just not possible.

Cadavre SSC-Forever Points: 41690 More actions · Answer 10

1. 18,000 is not a "huge data".

2. The query you were given works fine

3. Your database design is awful and will cause performance problems as your data-set increases.

BEGIN TRAN

SET NOCOUNT ON

SELECT collegeid, name, cityid, stateid

INTO #badTableDesign

FROM (SELECT 1, 'ABC', CONVERT(VARCHAR(20),'34,56,54'), 1

UNION ALL SELECT 2, 'PQR', '78', 2

UNION ALL SELECT 3, 'LMN', '78,56', 2) a(collegeid, name, cityid, stateid)

PRINT '========== Before Update =========='

SELECT * FROM #badTableDesign

PRINT REPLICATE('=',80)

PRINT '========== Update =========='

UPDATE #badTableDesign

SET cityid = ','+cityid+','

PRINT REPLICATE('=',80)

PRINT '========== After Update =========='

SELECT * FROM #badTableDesign

PRINT REPLICATE('=',80)

ROLLBACK

Which returns: -

========== Before Update ==========

collegeid name cityid stateid

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

1 ABC 34,56,54 1

2 PQR 78 2

3 LMN 78,56 2

================================================================================

========== Update ==========

================================================================================

========== After Update ==========

collegeid name cityid stateid

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

1 ABC ,34,56,54, 1

2 PQR ,78, 2

3 LMN ,78,56, 2

================================================================================

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

mikejean17 (12/9/2011)
yap i am totally agree with anthony.green
must be follow it
he is saying right thing which can remove ur problem
snip

Crappy spam reported.