How does one modify all DB column data based on current data?

Question

In my particular case, we've got a db table that, among other fields, has an "Image" field. We'd like to append ".jpg" to every row in this table for this column. To keep it simple we'll say it just has two columns...an ID column and an Image column. (bonus points for skipping rows that have an empty string for "Image".)

The question title is a bit more open ended because I can imagine someone wanting to just about anything to all rows. ("update all rows so that column 'X' has its values divided by 2", "update all rows so that column 'Y' is prepended with this path", etc.)

Although I'm using SQL server, I suspect that the answers will be pretty universal...but if your particular answer is not, don't let that stop you from responding...but please do add in your post what system it is for.

Answer 1

You mean like this? This works in SQL Server.

UPDATE  [table]
SET     [image] = [image] + '.jpg'
WHERE   ISNULL([image], '') <> ''

If you want this to be repeatable:

UPDATE  [table]
SET     [image] = [image] + '.jpg'
WHERE   ISNULL([image], '') <> ''
AND     [image] NOT LIKE '%.jpg'

Answer 2

like this

update Table
set image = image + '.jpg'
where image <> ''

or

update Table
set image = image + '.jpg'
where rtrim(image) <> ''

or

update Table
set image = image + '.jpg'
where rtrim(coalesce(image,'')) <> ''

Answer 3

Something along the lines of:

Update tableName
Set Image = Image+'.jpg'
where
  IsNUlL(Image,'') != ''

This is aimed at the T-SQL (SQL Server) dialect.

Answer 4

UPDATE imageTbl SET image = image + '.jpg' WHERE image IS NOT NULL

Answer 5

SQLServer 2k

declare @Image table (
Id int,
Name varchar(10)
)

insert into @Image values (1,'hello')
insert into @Image values (2,'')
insert into @Image values (3,'world')
update @Image set Name = Name + '.jpg'
from @Image where Name <> ''

select * from @Image

The update BLAH from YIKES syntax is really useful when your criteria for update span multiple tables, as it permits joins.