Modify all Data within a specific column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Modify all Data within a specific column

Author

Topic

bobshishka
Yak Posting Veteran

72 Posts

Posted - 2007-08-15 : 17:04:17

I have a column (PERIMAGE_PATH) in my table. The data in this column is imported from a fixed width text file and looks like the following:

07J06274
05J03254
04J11245

I need to modify the data in the column to look like this:

..\images\07J06274.jpg
..\images\05J03254.jpg
..\images\04J11245.jpg

How can I do this?

Thanks for all your guys' help. This is the last question for the day. I promise

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-15 : 17:05:17

update Table
Set Column = '..\images\' + column + '.jpg'
Where <condition>

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

bobshishka
Yak Posting Veteran

72 Posts

Posted - 2007-08-15 : 17:26:02

Too easy!

I love SQL!

So I lied about the above question being my last.... :)

I have a column name PERIMAGE_ID that is currently <NULL> because it is not populated by the text file that I am importing. I would like to fill it with something- ANYTHING- as long as it is unique per row. Basically like an "Autonumber" field in Access. How can I do this?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-15 : 17:44:44

Do you have a primary key in the table?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-08-15 : 17:44:57

Make a copy of your current table. Change the datatype for PERIMAGE_ID to int IDENTITY(1,1)
ALTER TABLE TABLENAME
ALTER COLUMN PERIMAGE_ID int IDENTITY(11)

Then reinsert your backup copy. PERIMAGE_ID will increase by 1 everytime you add a new record

Jim

bobshishka
Yak Posting Veteran

72 Posts

Posted - 2007-08-15 : 17:55:33

quote:
Originally posted by dinakar

Do you have a primary key in the table?

Yes I do. But for other reasons I need this field to also be unique

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-15 : 18:09:41

You can either do what Jimf suggested or update the column set it the value in the PK column. You will still be assured of uniqueness and not have NULLs.

update table
set PERIMAGE_ID = Primarykeycolumn
where PERIMAGE_ID is null

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

bobshishka
Yak Posting Veteran

72 Posts

Posted - 2007-08-15 : 18:40:27

quote:
Originally posted by jimf

Make a copy of your current table. Change the datatype for PERIMAGE_ID to int IDENTITY(1,1)
ALTER TABLE TABLENAME
ALTER COLUMN PERIMAGE_ID int IDENTITY(11)

Then reinsert your backup copy. PERIMAGE_ID will increase by 1 everytime you add a new record

Jim

Im not sure what you mean by 'reinsert your backup copy'...

When I run the following:

ALTER TABLE PERIMAGE_NEW
ALTER COLUMN PERIMAGE_ID int IDENTITY(1,1)

I get this error:

Incorrect syntax near the keyword 'IDENTITY'.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-08-15 : 18:56:01

Hmmm, you may not be able to alter to identity

1) select col1,col2,col3 etc. (all but perimage_id)
into table1_backup
from table1
-- this makes a copy of your data

2) right-click table1 and choose script table as create to
--this gives you the

3)DROP TABLE table1

4) CREATE TABLE TABLE1 (Perimage_id int identity(1,)
, rest of the columns from the script
(

4)INSERT INTO table1
select col1,col2,etc
from table1_backup
-- don't worry about the perimage_id, it gets incremented automatically
-- if something goes wrong, you still have your backup!

Jim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources