September 9, 2008 at 8:01 am
Hi every one, here iam comparing two tables and want to delete the records from first table if there are matchnig records in both the tables, but the problem is there are null values in both the tables so can any one suggest how to deal with the null values in this situation, here iam providing the code for the query which iam using can some one please help me out
DELETE FROM Table_3
WHERE EXISTS
( select *
from Table_1
where Table_3 .Reqnr = Table_1.Reqnr
and Table_3 .Description = Table_1.Description and
Table_3.name=Table_1.name
September 9, 2008 at 8:37 am
Do you want to delete matching NULLs? Use an OR clause -OR (a is null and b is null)
September 9, 2008 at 9:13 am
Hello,
When you have two values you want to compare and either one may be a null, then you use the IsNull function with a default value that's meaningful.
For example:
select count(a.*)
from tableA a,
tableB b
where IsNull(a.field1,0) = IsNull(b.field1,0)
The assumption is that the value 0 may be a default value for an integer.
This is also useful on date comparisons in the where clause.
where IsNull(a.order_date, '2008-01-01' ) = IsNull(b.order_date, '2008-01-01')
Hope this helps.
Regards,
Terry
September 9, 2008 at 4:18 pm
thank you very much i tried the
isnull(table1.column1)=isnull(table2.column1)
it worked
thanks again
September 9, 2008 at 6:46 pm
Hmmm... I would have written it this way.
DELETE t3
FROM dbo.Table_3 t3
JOIN dbo.Table_1 t1
ON t3.Reqnr = t1.Reqnr
AND t3.Description = t1.Description
AND t3.name = t1.name
Note that I'm not worrying about NULLS as the joins will treat them the way the DB is set to deal with nulls.
If you want to treat a NULL = NULL then you will want to use ISNULL(field,'') = ISNULL(field,'').
To me doing the join this way is easier to read... Plus I can test it ahead of time by remarking out the delete line and putting in a select statement... 🙂
Gary Johnson
Sr Database Engineer
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply