Nulls not getting updated - 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

Nulls not getting updated

Author

Topic

rds207
Posting Yak Master

198 Posts

Posted - 2010-06-10 : 12:22:05

Hi

I need to update the build size values from another table where crm_build_size column from dw_t_ec_tgt table is null, dw_t_ec_tgt already has some values , those values should not change, only where crm_build_size is null the values from crmbuildmetrics has to be updated so i used below query

Update dbo.DW_T_EC_TGT set
CRM_BUILD_SIZE = (select ((CRMBuildMetrics.ByteCount)/1024)FROM CRMBuildMetrics
where CAST(CRMBuildMetrics.Jobname AS NVARCHAR(255)) = DW_T_EC_TGT.EC_JOB_NAME
and CRMBuildMetrics.Action='COPY' and CRM_BUILD_SIZE is NULL)

But all the existing values in crm_build_size column seems to become NULL , could any body please tell me whats wrong with my query ?

Please Help...

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-06-10 : 12:43:30

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

rds207
Posting Yak Master

198 Posts

Posted - 2010-06-10 : 12:59:52

DDL
table : dw_t_ec_tgt
crm_build_size bigint
ec_job_name nvarchar(255)

table : crmbuildmetrics
jobname text
bytecount bigint
action varchar(32)

Sample data:

select * from crmbuildmetrics

jobname Action ByteCount
M7501BXDCLKASD5786 Build NULL
M7501BXDCLKASD5786 Compile NULL
M7501BXDCLKASD5786 Copy 16303271936
M7501BXDCLKASD5786 Sync NULL
M7630AAABQMAAM112101A Build NULL
M7630AAABQMAAM112101A Compile NULL
M7630AAABQMAAM112101A Copy 28371058688
M7630AAABQMAAM112101A Sync NULL
M7630AAABQMAAM1240490A Build NULL
M7630AAABQMAAM1240490A Compile NULL
M7630AAABQMAAM1240490A Copy 26365203456

select * from dw_t_ec_tgt

ec_job_name crm_build_size
M7501BXDCLKASD5786 NULL
M7630AAABQMAAM1240490A NULL
M7630AAABQMAAM112101A NULL
M7345AAABQMAAM112101A 45455632
M7501BXDCLKASD5555 12365478

Expected output

Table dw_t_ec_tgt :

ec_job_name crm_build_size
M7501BXDCLKASD5786 15921164
M7630AAABQMAAM1240490A 25747269
M7630AAABQMAAM112101A 3463264
M7345AAABQMAAM112201A 45455632
M7501BXDCLKASD5555 12365478

Hope this helps!!

quote:
Originally posted by Lamprey

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-10 : 15:02:30

This?

update a
set a.crm_build_size = b.ByteCount
from dw_t_ec_tgt a
inner join
(
select * from crmbuildmetrics where Action = 'Copy'
) b
on a.ec_job_name = b.jobname and a.crm_build_size is null

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources