May 24, 2006 at 11:56 am
I have a basic query
SELECT SerialNumber, ID FROM MySerialTable WHERE Order = 'MyOrderNumber'
This query will return the ID and SerialNumbers related to an order.
I want to return the Count of Rows with a NULL value in the SerialNumber field.
I then have a list of Serial Numbers to Insert Into the Rows with NULL values (there might be exisitng numbers). The insert of the Serial number does not have to relate to a specific ID but only by the Order Number (from the where statement).
The serial numbers will come from a textbox from an application (They can be separated by a comma)
What would be the best way to accomplish this?
May 25, 2006 at 5:31 am
Hello,
to get count of rows where Serial number is NULL, use COUNT - with * it counts all rows, with a column name only rows where value of this column is not null. Just subtract that from the sum and you have number of NULLs :
SELECT COUNT(*) as count_all, COUNT(SerialNumber) as count_has_Snumber,
(COUNT(*) - COUNT(SerialNumber)) as count_of_nulls
FROM MySerialTable WHERE Order = 'MyOrderNumber'
Of course, if you only need the one thing, you can always do
SELECT COUNT(*)
FROM MySerialTable WHERE Order = 'MyOrderNumber' AND SerialNumber IS NULL
Unfortunately I'm not sure I understand the second step. You mention INSERT INTO, but probably you need to UPDATE existing rows that have NULL value? Or do you really want to insert new rows? Also, if you have a list of 3 serial numbers for certain order, and there are 3 rows with NULLs, which row should get which of the serial numbers? Is ID an identity column for the serial table (are values unique)?
Generally, if you get the values as delimited text, parse the text and insert values into table variable. Then you can do the update or insert, whatever is necessary. Please supply more info if you want to recieve more precise answer - I'm not sure how to handle some situations.
HTH, Vladan
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply