IS NULL not working

Hi I am trying to do this on a temp table
DELETE FROM #output where [tst1] is NULL
this for some reason doesnt seem to work.
on the other hand when i do the below
insert into db_comp
select tst1, tst2,tst3 from #output
where tst1 is not null
seems to work. does anyone know why this is happening.
regards,
njPlease elaborate on "doesn't seem to work". Are there errors? Are you certai
n
there are rows in the table that correspond to the criteria in the WHERE
clause?
ML
http://milambda.blogspot.com/|||NJ skrev:

> Hi I am trying to do this on a temp table
> DELETE FROM #output where [tst1] is NULL
> this for some reason doesnt seem to work.
> on the other hand when i do the below
> insert into db_comp
> select tst1, tst2,tst3 from #output
> where tst1 is not null
> seems to work. does anyone know why this is happening.
> regards,
> nj
As ML wrote, you need to explain how the above does not work...
To me it looks consistent with #output having no rows where tst1 is
NULL (if by 'not working' you mean the first statement didn't delete
anything)...
/impslayer, aka Birger Johansson|||Here you are using IS NULL
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"NJ" <npaulus@.hotmail.com> wrote in message
news:1147766869.501289.38100@.g10g2000cwb.googlegroups.com...
> Hi I am trying to do this on a temp table
> DELETE FROM #output where [tst1] is NULL
> this for some reason doesnt seem to work.
> on the other hand when i do the below
> insert into db_comp
> select tst1, tst2,tst3 from #output
> where tst1 is not null
> seems to work. does anyone know why this is happening.
> regards,
> nj
>|||Here you are using IS NULL

> DELETE FROM #output where [tst1] is NULL
Here you are using IS NOT NULL

> select tst1, tst2,tst3 from #output
> where tst1 is not null
They are mutually exclusive.
If SELECT * FROM #output WHERE [tst1] IS NULL returns no rows then it won't
delete any.
Sorry about the part post on the other answer, I hit enter and something
else which so happens to be the short cut to posting the post!
Tony/.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"NJ" <npaulus@.hotmail.com> wrote in message
news:1147766869.501289.38100@.g10g2000cwb.googlegroups.com...
> Hi I am trying to do this on a temp table
> DELETE FROM #output where [tst1] is NULL
> this for some reason doesnt seem to work.
> on the other hand when i do the below
> insert into db_comp
> select tst1, tst2,tst3 from #output
> where tst1 is not null
> seems to work. does anyone know why this is happening.
> regards,
> nj
>|||Tony Rogerson (tonyrogerson@.sqlserverfaq.com) writes:
> Sorry about the part post on the other answer, I hit enter and something
> else which so happens to be the short cut to posting the post!
CTRL-Enter is a shortcut for Send in Outlook, and I believe in Outlook
Express (that I see that you are using).
Now, is that a really stupid keyboard shortcut or what!
Myself, I'm go to press F8 now. :-)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I know Erland, dam stupid.
I seem to do it every now and again, but after doing it once i always do it
again - so, expect some half posts from me today...
tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97C57FAA43A96Yazorman@.127.0.0.1...
> Tony Rogerson (tonyrogerson@.sqlserverfaq.com) writes:
> CTRL-Enter is a shortcut for Send in Outlook, and I believe in Outlook
> Express (that I see that you are using).
> Now, is that a really stupid keyboard shortcut or what!
> Myself, I'm go to press F8 now. :-)
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||The first statement the delete statement doesnt delete anything. let me
put this more into perspective
part 1:
DELETE FROM #output where [tst1] is NULL
Insert into db_comp
select * from #output
part 2:
insert into db_comp
select tst1, tst2,tst3 from #output
where tst1 is not null|||NJ skrev:

> The first statement the delete statement doesnt delete anything. let me
> put this more into perspective
> part 1:
> DELETE FROM #output where [tst1] is NULL
> Insert into db_comp
> select * from #output
> part 2:
> insert into db_comp
> select tst1, tst2,tst3 from #output
> where tst1 is not null
/me scratches his head.
You use "is null" for the delete statement, but "is not null" for that
second part... If there are no rows in #output with tst1 being NULL,
then it shouldn't delete anything.
In part 2, OTOH, you select (and insert those) rows where tst1 is NOT
NULL, that is, the rows that you weren't trying to delete in the first
statement.
/impslayer, aka Birger Johansson|||Hi NJ,
How many rows does this return?
SELECT * FROM #output where [tst1] is NULL
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"NJ" <npaulus@.hotmail.com> wrote in message
news:1147780233.453202.150570@.v46g2000cwv.googlegroups.com...
> The first statement the delete statement doesnt delete anything. let me
> put this more into perspective
> part 1:
> DELETE FROM #output where [tst1] is NULL
> Insert into db_comp
> select * from #output
> part 2:
> insert into db_comp
> select tst1, tst2,tst3 from #output
> where tst1 is not null
>