Solved

remove NULL values from table

Posted on 2010-11-28
17
298 Views
Last Modified: 2012-08-14
Hi I have an issue i want to resolve, I have a temp table that looks like (see below) and I want to remove SOME of the NULL values, table looks like this..
client_id_1      client_id_2      yyyymm       total1        total2        total3
500      101784082      200907       400.00        400.00        400.00
500      101784082      200908       6.00        6.00        6.00
500      101784082      200909       400.00        400.00        400.00
500      101784082      200910       200.00        200.00        200.00
500      101784082      200911       300.00        300.00        300.00
500      101784082      200912       700.00        700.00        700.00
500      101784082      201001       00        00        00
500      101784082      201002       300.00        300.00        300.00
500      101784082      201003       200.00        200.00        200.00
500      101784082      201004       300.00        300.00        300.00
500      101784082      201005       100.00        100.00        100.00
500      NULL      201006       NULL        NULL        NULL
500      101784082      201007       10         20         30  
500      101784082      201008       250.00        1,000.00        800.00
500      101784082      201009       350.00        200.00        900.00
501      101890626      200907       49.80        49.80        49.80
501      101890626      200908       51.25        51.26        51.25
501      101890626      200909       51.68        51.69        51.68
501      101890626      200910       54.15        54.16        54.15
501      101890626      200911       53.15        53.16        53.15
501      101890626      200912       54.92        54.93        54.92
501      101890626      201001       53.57        53.57        53.57
501      101890626      201002       52.88        52.88        52.88
501      101890626      201003       52.58        52.59        52.58
501      101890626      201004       52.88        52.88        52.88
501      NULL      201005       NULL        NULL        NULL
501      NULL      201006       NULL        NULL        NULL
501      NULL      201007       NULL        NULL        NULL
501      NULL      201008       NULL        NULL        NULL
501      NULL      201009       NULL        NULL        NULL

I have 2 client ids in this example 500 & 501 (but hundreds in my temp table) and I only want to remove the NULLS when it falls in the middle of my client_id_1 i.e.  For client 500 in '201006' I have no value but I have a value in '201007', I want to remove the row at YYYYMM '201006'.
I do not want to remove any of the NULLS from client 501 as I need to populate these fields with '0.00'. Bit stuck so any assistance with be greatly appreciated.
0
Comment
Question by:twfw123
  • 8
  • 3
  • 2
  • +4
17 Comments
 
LVL 16

Expert Comment

by:Easwaran Paramasivam
Comment Utility
delete table where client_id_1 =  500  AND client_id_2    is NULL  AND total1 is NULL AND total2 is NULL AND total3 is NULL
0
 

Author Comment

by:twfw123
Comment Utility
hi - no that will not work as I have 100's of customers in my table, so I can not select 1 at a time. So could have alot of situtations where there is 1 missing months data in the middle of a customers data.  
0
 
LVL 1

Expert Comment

by:Praveen_Venkat
Comment Utility
Use isnull(clientId,'') in your database to remove the null values. You can also replace the database name
0
 
LVL 5

Expert Comment

by:Priya Perumpilavil
Comment Utility
delete table1 where isnull(client_id_2,'')='' or  isnull(total1,'')='' or isnull(total2,'')='' or  isnull(total3,'')=''

try this
0
 

Author Comment

by:twfw123
Comment Utility
hi - but will this not delete ALL my NULL customers – i only want to remove NULL values that fall in the middle of my client, i.e. 500 has a NULL value for  '201006' i want to delete this from the table but retain all of customer 501’s NULL values.
0
 
LVL 2

Expert Comment

by:svalekar
Comment Utility
can you please exact scenario, what actually you want. Getting confused about your requirement
0
 
LVL 5

Expert Comment

by:Priya Perumpilavil
Comment Utility
try this one
delete table1 where isnull(client_id_2,'')='' and client_id_1 in (select distinct client_id_1 from table1 where  isnull(client_id_2,'')<>'')
0
 

Author Comment

by:twfw123
Comment Utility
it is a little confusing, thats why im having a problem being able to do it. Basically i get monthly data - if I miss a month I do not want to report it and as roll and average forward - I only want to delete the NULLS from missing months of data - not when the NULLS are from months not reported, clear as mud? as stated above in the table above.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 5

Expert Comment

by:Priya Perumpilavil
Comment Utility
wht abt my above query...u tried?
0
 

Author Comment

by:twfw123
Comment Utility
hi priya -  i did but it deleted all the NULLS - problem is i need to retain the nulls that belong to client 501 and there will be others that are similar to this - thanks
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
Comment Utility
Hi,

This is your data


client_id_1      client_id_2      yyyymm       total1        total2        total3

500            NULL            201006       NULL        NULL        NULL
500            101784082       201007       10          20          30  
501            NULL            201006       NULL        NULL        NULL
501            NULL            201007       NULL        NULL        NULL


You just wanted to delete data marked in bold ONLY???

on which basis you wanted to???


Sory - I didnt understand from your post.
0
 

Author Comment

by:twfw123
Comment Utility
hi birchsoft - yes that correct in your example only the data in bold - its quite difficult to explain in a paragraph. I only want to delete the rows with NULL when they fall in the middle of my client data. hope thats clear
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
Comment Utility
Hi


Sory again but what do you mean by when you said

"when they fall in the middle of my client data"

Can you please explain??


Thanks for quick feedback,I appreciates....
0
 

Author Comment

by:twfw123
Comment Utility
if there there is no data for 1 reporting month i.e.

client_id_1      client_id_2      yyyymm       total1         total2          total3
500               101784082       201004       300.00        300.00        300.00
500               101784082       201005       100.00        100.00        100.00
500               NULL                201006       NULL             NULL        NULL
500              101784082       201007       10                 20               30  
500              101784082       201008       250.00          1,000.00      800.00
500              101784082       201009       350.00          200.00        900.00

in this example i have data for all months except for 201006 - and this is what i want to delete.
0
 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility
does this identify the ones to remove?


select * from yourtable
 where client_id_2 is null
   and exists (select client_id_1 from yourtable as x
        where x.client_id_1 = yourtable.client_id_1
          and x.client_id_2 is not null
          and x.yyyymm > yourtable.yyyymm)

if so use the delete...

confirm that the NULLS are actual database nulls not literals



delete from yourtable

 where client_id_2 is null

   and exists (select client_id_1 from yourtable as x

        where x.client_id_1 = yourtable.client_id_1

          and x.client_id_2 is not null

          and x.yyyymm > yourtable.yyyymm)

Open in new window

0
 

Accepted Solution

by:
twfw123 earned 0 total points
Comment Utility
thanks for the assistance - but found another awnser, cheers
0
 

Author Closing Comment

by:twfw123
Comment Utility
found another solution
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now