A Null check in a single record

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

A Null check in a single record

Author

Topic

yossibaram
Yak Posting Veteran

82 Posts

Posted - 2002-12-16 : 04:28:27

Hi,
I have a table that may contains Null filled columns.
I have a SP that do Update and insert.
How can i do a null check to avoid trying to update null fields?
Let say Type_1 column is null (not empty string), I need a condition that will do a quick check (single record is sufficient) to see that it has null value and to skip the update command:

CREATE PROCEDURE Lan_Insert_Data_Type
as
DECLARE @Single_Rec int
Begin

-- ????? HERE comes the condition ----

UPDATE Data_type
SET Product_Num = LanTable.ProductNum,
Data =LanTable.Type_1
FROM Data_Type p
JOIN LanTable
ON Lantable.ProductNum = p.Product_num where p.Data_Type =1

Thanks

Yossi

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2002-12-16 : 04:45:55

CREATE PROCEDURE Lan_Insert_Data_Type
as
DECLARE @Single_Rec int
Begin

if is not null (select type_1 from table_name)
begin
UPDATE Data_type
SET Product_Num = LanTable.ProductNum,
Data =LanTable.Type_1
FROM Data_Type p
JOIN LanTable
ON Lantable.ProductNum = p.Product_num where p.Data_Type =1
end

harshal

Edited by - harshal_in on 12/16/2002 04:50:42

yossibaram
Yak Posting Veteran

82 Posts

Posted - 2002-12-16 : 05:33:38

quote:

CREATE PROCEDURE Lan_Insert_Data_Type
as
DECLARE @Single_Rec int
Begin

if is not null (select type_1 from table_name)
begin
UPDATE Data_type
SET Product_Num = LanTable.ProductNum,
Data =LanTable.Type_1
FROM Data_Type p
JOIN LanTable
ON Lantable.ProductNum = p.Product_num where p.Data_Type =1
end

harshal

Edited by - harshal_in on 12/16/2002 04:50:42

Thanks for the reply,

I tried the following:

DECLARE @Single_Rec varchar(10)
BEGIN
set @Single_Rec = (select type_1 from LanTable)
if @Single_Rec is not null
begin
UPDATE Data_type
SET Product_Num = LanTable.ProductNum,
Data =LanTable.Type_1
FROM Data_Type p
JOIN LanTable
ON Lantable.ProductNum = p.Product_num where p.Data_Type=1
end

i got the following error:
"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
Can you help on that?

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2002-12-16 : 07:02:41

or can u use this:
UPDATE Data_type
SET Product_Num = LanTable.ProductNum,
Data =LanTable.Type_1
FROM Data_Type p
JOIN LanTable
ON Lantable.ProductNum = p.Product_num where p.Data_Type =1
where lantabel.type_1 is not null

harhsal.

yossibaram
Yak Posting Veteran

82 Posts

Posted - 2002-12-16 : 07:09:01

quote:

or can u use this:
UPDATE Data_type
SET Product_Num = LanTable.ProductNum,
Data =LanTable.Type_1
FROM Data_Type p
JOIN LanTable
ON Lantable.ProductNum = p.Product_num where p.Data_Type =1
where lantabel.type_1 is not null

harhsal.

will try that,
Thanks mate..

nr
SQLTeam MVY

12543 Posts

Posted - 2002-12-16 : 07:14:18

well
or can u use this:
UPDATE Data_type
SET Product_Num = LanTable.ProductNum,
Data =LanTable.Type_1
FROM Data_Type p
JOIN LanTable
ON Lantable.ProductNum = p.Product_num
where p.Data_Type =1
and Lantabel.type_1 is not null

or

UPDATE Data_type
SET Product_Num = LanTable.ProductNum,
Data =LanTable.Type_1
FROM Data_Type p
JOIN LanTable
ON Lantable.ProductNum = p.Product_num
and Lantabel.type_1 is not null
where p.Data_Type =1

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

yossibaram
Yak Posting Veteran

82 Posts

Posted - 2002-12-16 : 08:01:00

quote:

well
or can u use this:
UPDATE Data_type
SET Product_Num = LanTable.ProductNum,
Data =LanTable.Type_1
FROM Data_Type p
JOIN LanTable
ON Lantable.ProductNum = p.Product_num
where p.Data_Type =1
and Lantabel.type_1 is not null

or

UPDATE Data_type
SET Product_Num = LanTable.ProductNum,
Data =LanTable.Type_1
FROM Data_Type p
JOIN LanTable
ON Lantable.ProductNum = p.Product_num
and Lantabel.type_1 is not null
where p.Data_Type =1

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Hi,
I used your second option and its working very slowely.
for 10,000 records every update takes 3 seconds.
It takes this amount of time even if column is null and it should be disregard.
help

nr
SQLTeam MVY

12543 Posts

Posted - 2002-12-16 : 08:14:38

How many entries are you expecting to update?
Look at the query plen
Is it using the index ProductNum?
Maybe you need to give a hint to use a p.Data_Type index.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

yossibaram
Yak Posting Veteran

82 Posts

Posted - 2002-12-16 : 08:14:44

quote:

well
or can u use this:
UPDATE Data_type
SET Product_Num = LanTable.ProductNum,
Data =LanTable.Type_1
FROM Data_Type p
JOIN LanTable
ON Lantable.ProductNum = p.Product_num
where p.Data_Type =1
and Lantabel.type_1 is not null

or

UPDATE Data_type
SET Product_Num = LanTable.ProductNum,
Data =LanTable.Type_1
FROM Data_Type p
JOIN LanTable
ON Lantable.ProductNum = p.Product_num
and Lantabel.type_1 is not null
where p.Data_Type =1

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

I tried also the following:

if not exists(select Type_1 FROM LanTable where Type_1 is null)
begin
UPDATE Data_type
SET Product_Num = LanTable.ProductNum,
Data =LanTable.Type_1
FROM Data_Type p
JOIN LanTable
ON Lantable.ProductNum = p.Product_num and LanTable.Type_1 is not null where p.Data_Type =1
print 'in 1'
end

Here, even if the field won't contain Null it will avoid getting into the 'if' and wont update.
Why?

yossibaram
Yak Posting Veteran

82 Posts

Posted - 2002-12-16 : 08:19:21

quote:

quote:

well
or can u use this:
UPDATE Data_type
SET Product_Num = LanTable.ProductNum,
Data =LanTable.Type_1
FROM Data_Type p
JOIN LanTable
ON Lantable.ProductNum = p.Product_num
where p.Data_Type =1
and Lantabel.type_1 is not null

or

UPDATE Data_type
SET Product_Num = LanTable.ProductNum,
Data =LanTable.Type_1
FROM Data_Type p
JOIN LanTable
ON Lantable.ProductNum = p.Product_num
and Lantabel.type_1 is not null
where p.Data_Type =1

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

I tried also the following:

if not exists(select Type_1 FROM LanTable where Type_1 is null)
begin
UPDATE Data_type
SET Product_Num = LanTable.ProductNum,
Data =LanTable.Type_1
FROM Data_Type p
JOIN LanTable
ON Lantable.ProductNum = p.Product_num and LanTable.Type_1 is not null where p.Data_Type =1
print 'in 1'
end

Here, even if the field won't contain Null it will avoid getting into the 'if' and wont update.
Why?

I need to go.
Please advise on the matter and I will try to reach you tommorow.
Thanks again for the help.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-12-16 : 10:04:43

That will not do the update if any record exists where Type_1 is null.
Very different from updating those where it is not null.
If that's what you want then you can drop the null check from the query as you already know it's not null (as long as nothing changes between the two queries).
The performance of the query will mainly be affected by use of indexes

UPDATE Data_type
SET Product_Num = LanTable.ProductNum,
Data =LanTable.Type_1
FROM Data_Type p
JOIN LanTable
ON Lantable.ProductNum = p.Product_num
where p.Data_Type =1

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

yossibaram
Yak Posting Veteran

82 Posts

Posted - 2002-12-17 : 04:44:49

quote:

That will not do the update if any record exists where Type_1 is null.
Very different from updating those where it is not null.
If that's what you want then you can drop the null check from the query as you already know it's not null (as long as nothing changes between the two queries).
The performance of the query will mainly be affected by use of indexes

UPDATE Data_type
SET Product_Num = LanTable.ProductNum,
Data =LanTable.Type_1
FROM Data_Type p
JOIN LanTable
ON Lantable.ProductNum = p.Product_num
where p.Data_Type =1

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

hi,
i use the fllowing to avoid entering the loop if null:

if not exists(select Type_1 FROM LanTable where Type_1 is null)
begin
...........
end

The query takes time if many records involve.
How can i do a check of one record (if its null or not) and that will count if i enter the loop or not?

Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2002-12-17 : 05:48:47

Just include it in the where clause

if not exists(select * FROM LanTable where tblPK = @fld and Type_1 is null)

or

if (select Type_1 FROM LanTable where tblPK = @fld) is null

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

yossibaram
Yak Posting Veteran

82 Posts

Posted - 2002-12-17 : 05:55:57

quote:

Just include it in the where clause

if not exists(select * FROM LanTable where tblPK = @fld and Type_1 is null)

or

if (select Type_1 FROM LanTable where tblPK = @fld) is null

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Hi nr,
It will work if I know the value of the pk or any other field but i don't.
How can i do a check of a single record (can be the first for example) so the query wont need to run all records?
Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2002-12-17 : 06:09:03

if (select top 1 Type_1 FROM LanTable) is null

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

yossibaram
Yak Posting Veteran

82 Posts

Posted - 2002-12-17 : 06:23:38

quote:

if (select top 1 Type_1 FROM LanTable) is null

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

You have been a great help for me
Thanks a lot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources