How delete the rows where comlete record is null.

Question

Post reply

How delete the rows where comlete record is null.

Bagath

SSCrazy

Points: 2409
More actions
February 21, 2006 at 11:20 pm

#90837

Hi,
     Here is my query....
     I created a table with diff data types and inserted some values into that table as following
     create table tab_del_nulls
(int_no int,
var_name varchar(1000),
int_marks int,
var_desc varchar(1000),
dt_date datetime)
insert into tab_del_nulls
values(1,'aregaddan',240,'pass',getdate())
insert into tab_del_nulls
values(null,null,null,null,null)
insert into tab_del_nulls
values(2,'nani',140,'fail',null)
insert into tab_del_nulls
values(null,null,null,null,null)
Select * from tab_del_nulls
Int_no   var_marks    int_marks var_desc dt_date
-----    ---------     -------- ------   -------
1 aregaddan 240 pass 2006-02-22 11:25:40.667
NULL NULL  NULL NULL NULL
2 nani  140 fail NULL
NULL NULL  NULL NULL NULL
     Now i want to delete the rows where the complete row is null i.e for the above table i want to delete 2nd and 4th rows.
Can any one give me a suggestion?

Thanks,
Rao Aregaddan.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Vee Hall of Fame Points: 3028 More actions · Answer 1

delete from tab_del_nulls

where Int_no is null and

var_marks is null and

int_marks is null and

var_desc dt_date is null ;

U need to check each column for null

Bagath SSCrazy Points: 2409 More actions · Answer 2

I know this type of query.but i want to use the query for global use.In this case we have only 4 cols but i want to use this for a table which is having 400 cols.Is there any other way like by using cursor or anything...

Thanks,

Rao Aregaddan.

Bagath SSCrazy Points: 2409 More actions · Answer 3

Excuse for the type mistake,not comlete it is complete....

Vee Hall of Fame Points: 3028 More actions · Answer 4

ok. then u need to create a dynamic query

here it is ...

I HAVE TRRIED.

SHOULD WORK FOR ANY TABLE:

DECLARE @TABLE_NAME SYSNAME

declare @is_null_columns varchar(8000)

set @is_null_columns =''

SET @TABLE_NAME = 'STORES'

select @is_null_columns = @is_null_columns + [name] + ' IS NULL AND '+CHAR(13) FROM SYSCOLUMNS

WHERE ID = OBJECT_ID(@TABLE_NAME )

SET @IS_NULL_COLUMNS = LEFT(@IS_NULL_COLUMNS ,LEN(@IS_NULL_COLUMNS )-5)

PRINT 'DELETE FROM '+@TABLE_NAME +' WHERE ' + @is_null_columns

EXECUTE ('DELETE FROM '+@TABLE_NAME +' WHERE ' + @is_null_columns )

Bagath SSCrazy Points: 2409 More actions · Answer 5

Thanks Veeresh....Really Thanks allot.....

It is working....

Regards,

Rao Aregaddan.

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 6

We have had the same problem not long ago...

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=257275

Bagath SSCrazy Points: 2409 More actions · Answer 7

Thanks Jesper ,For the reply.

Regards,

Rao Aregaddan.

Ram Ram Ten Centuries Points: 1079 More actions · Answer 8

Try this

DELETE TableA

WHERE COALESCE(Col1,Col2,Col3,'') = ''

Ram Ramamoorthy

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 9

Or COALESCE(Col1, Col2, Col3) is null

It may however fail. You will get a conversion error if you run it on the following table:

create table #test(a uniqueidentifier null, b int null)

But in many cases, it's a possible solution...

raimi SSCrazy Points: 2171 More actions · Answer 10

You could try this

SELECT *

FROM #Test2

WHERE BINARY_CHECKSUM(*) = CHECKSUM(*)

this should give you all the rows in the table that have NULLs for all columns

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 11

This seems not to work. I get one row with the example below:

create table #test(a int null, b varchar(10) null)

go

insert #test select 1, ''

SELECT *

FROM #Test

WHERE BINARY_CHECKSUM(*) = CHECKSUM(*)

drop table #test

go

Bagath SSCrazy Points: 2409 More actions · Answer 12

Ok...Thanks for the reply..

But i need the query for dynomic purpose ......as per my requirement we cant expect that with how many cols the table will be created in the sp....

Regards,

Rao Aregaddan.

Bagath SSCrazy Points: 2409 More actions · Answer 13

Ohh........Finally i got the solution..Excuese me for I did not read the complete mail which is next page....

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 14

the BINARY_CHECKSUM and checksum solution is great, but know their downsides (text/image/...)

It's all in Bol

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me