[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 326
  • Last Modified:

remove NULL values from table

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
twfw123
Asked:
twfw123
  • 8
  • 3
  • 2
  • +4
1 Solution
 
Easwaran ParamasivamCommented:
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
 
twfw123Author Commented:
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
 
Praveen_VenkatCommented:
Use isnull(clientId,'') in your database to remove the null values. You can also replace the database name
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Priya PerumpilavilSoftware EngineerCommented:
delete table1 where isnull(client_id_2,'')='' or  isnull(total1,'')='' or isnull(total2,'')='' or  isnull(total3,'')=''

try this
0
 
twfw123Author Commented:
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
 
svalekarCommented:
can you please exact scenario, what actually you want. Getting confused about your requirement
0
 
Priya PerumpilavilSoftware EngineerCommented:
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
 
twfw123Author Commented:
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
 
Priya PerumpilavilSoftware EngineerCommented:
wht abt my above query...u tried?
0
 
twfw123Author Commented:
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
 
Bhavesh ShahLead AnalysistCommented:
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
 
twfw123Author Commented:
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
 
Bhavesh ShahLead AnalysistCommented:
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
 
twfw123Author Commented:
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
 
LowfatspreadCommented:
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
 
twfw123Author Commented:
thanks for the assistance - but found another awnser, cheers
0
 
twfw123Author Commented:
found another solution
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 8
  • 3
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now