Update data

Question

Update data

Zia Khan

Old Hand

Points: 341
More actions
July 13, 2006 at 12:24 am

#112857

Hi All,
I was trying to update data of a field.
column = Items
Data = 1234AF
I want to add 'D' in every record I have total 58000 records in this column.
Udated data like '1234ADF'
Is there any way to update all in one go.
Thanks in advance.
Zia Khan

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

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

SQLZ SSChampion Points: 12872 More actions · Answer 1

Hi Zia,

We'd need to know a little more about the format of the data in the Items column. Are the first 4 characters always numeric? Are the last two characters always AF?

Depending on what the format of the string is you could use various string functions to achieve what you want.

Assuming that you will always want to insert a "D" between the characters "A" and "F" and there is only one occurrence of "AF" in a given field you could do something like this:

update mytable

set Items = replace(Items,'AF','ADF')

Like I said though, the validity of the above statement is dependant on how the data is formatted.

If the data is formatted differently you'll need to use other string functions to achieve your result.

Karl
source control for SQL Server

Zia Khan Old Hand Points: 341 More actions · Answer 2

Hi Karl,

Its data type is varchar 20.And its index also on same column PK_Items_1_10

I have one more worry, we have also around 40,000 rows in the same column without A, means

12345F.

Will it change this also and I really dont want to change which is without A.

Thanks

Zia

SQLZ SSChampion Points: 12872 More actions · Answer 3

Hi Zia,

the update statement I provided will replace all occurrences of the string 'AF' with 'ADF' in the Items column. So it won't update fields like '12345F'.

You just have to be sure that there are no other occurrences of 'AF' that you wouldn't want to update.

If you do go ahead and run this update statement I would backup your database beforehand, just in case you need to recover it to the point prior to making the change.

Karl
source control for SQL Server

Zia Khan Old Hand Points: 341 More actions · Answer 4

Hi Karl,

Yes you are right I always take back up when doing any updates for safe side.

And thanks to solve my issue.

I am sure world has still good and helpfull guys like you.I am impressed.

Thanks again

Zia

sasikumard Grasshopper Points: 13 More actions · Answer 5

Hi

UPDATE <table name>

set <field name> = SUBSTRING('123AF',1,4)+'D'+SUBSTRING('123AF',5,1)

Regards,

Sasikumar.

David Burrows SSC Guru Points: 65142 More actions · Answer 6

UPDATE

SET Items = STUFF(Items,6,0,'D')

WHERE Items LIKE '[0-9][0-9][0-9][0-9]AF'

This will save you updating unnecessary rows

Far away is close at hand in the images of elsewhere.
Anon.