UPDATE - SQL Server Forums

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

UPDATE

Author

Topic

tariq2
Posting Yak Master

125 Posts

Posted - 2011-09-27 : 13:33:39

Hi,

Below I have a query which fields a set of records with
NUll in a.comments and b.jccomnts

I would like to adjust the following statement so that it
UPDATES a.comments with b.jccomnts

Many thanks in advance

select a.JobID,a.Comments,b.jccomnts,* from D_JobAttributes a inner join F590690 b
on LTRIM(RTRIM(a.jobid)) = LTRIM(rtrim(b.jcmcu))
where a.BUKey in (select BUKey from D_BusinessUnits where Year > '2009' and TerritoryName = 'Switzerland')
and a.Comments is NULL and b.jccomnts <>''

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-09-27 : 13:47:56

Try using update with inner join.
some thing like below. This should work but try to avoid alias name in this query if this does not work.

update D_JobAttributes set Comments=b.jccomnts from D_JobAttributes a inner join F590690 b
on LTRIM(RTRIM(a.jobid)) = LTRIM(rtrim(b.jcmcu))
where a.BUKey in (select BUKey from D_BusinessUnits where Year > '2009' and TerritoryName = 'Switzerland')
and a.Comments is NULL and b.jccomnts <>''

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-09-27 : 13:51:09

UPDATE a
SET a.comments = b.jccomnts
FROM
from D_JobAttributes a inner join F590690 b
on LTRIM(RTRIM(a.jobid)) = LTRIM(rtrim(b.jcmcu))
where
exists (select *
from D_BusinessUnits d
where [Year] > 2009 -- is Year really a string? You should
-- convert it to an int
and TerritoryName = 'Switzerland'
and a.BUKey = d.BUKey )
and a.Comments is NULL and b.jccomnts <>''

Jim

Everyday I learn something that somebody else already knew

tariq2
Posting Yak Master

125 Posts

Posted - 2011-09-27 : 13:54:46

Worked a treat guys, thank you very much :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources