SQL Express query question. I have a field in a table that says NULL, I need that to be changed to a date. My query process but doesn't make the change.
I have a problem with a sql table. It has date field in about 200 of 2000 records. I don't know how it happened yet, but to correct the problem so work can conntinue I need to do two things.
1. Update the 200 records to have the correct date.
2. Empty another table so all 2000 records can be processed through without duplication.
My query to change the field from NULL to the date is "UPDATE Usage SET TransDate='2011-03-01 00:00:00.000' WHERE TransDate=NULL" This query processes but says no records processed. I need assistance with this query.
I also need assistance correctly emptying a table. All data in my arimporttable needs to be emptied.
I do not usually write the queries, this is an urgent fix needed and I need some assistance.
Thank you, Sharon
1. Update the 200 records to have the correct date.
2. Empty another table so all 2000 records can be processed through without duplication.
My query to change the field from NULL to the date is "UPDATE Usage SET TransDate='2011-03-01 00:00:00.000' WHERE TransDate=NULL" This query processes but says no records processed. I need assistance with this query.
I also need assistance correctly emptying a table. All data in my arimporttable needs to be emptied.
I do not usually write the queries, this is an urgent fix needed and I need some assistance.
Thank you, Sharon
ASKER
Carl, I cannot see the completed response. This is all I see on the answer you sent
1:
UPDATE Usage SET TransDate='2011-03-01 00:00:00.000' WHERE TransDate
Can you send again?
1:
UPDATE Usage SET TransDate='2011-03-01 00:00:00.000' WHERE TransDate
Can you send again?
Ok, try this:
NULL is a non-value so a standard test for equality doesn't work, so you need to use the "IS" keyword instead.
UPDATE Usage SET TransDate='2011-03-01 00:00:00.000' WHERE TransDate IS NULL NULL is a non-value so a standard test for equality doesn't work, so you need to use the "IS" keyword instead.
Any comparison against NULL will result in false.
So:
NULL = NULL
NULL = field
field = NULL
Will always render false. (Also with other comparison operators <= < > >= <>)
The only way to check for NULL values is use the keyword "IS".
So:
NULL = NULL
NULL = field
field = NULL
Will always render false. (Also with other comparison operators <= < > >= <>)
The only way to check for NULL values is use the keyword "IS".
ASKER
I still could not not see your response in the comment box, but I did see it where you wrote it. That did the trick for me Thanks.
You did not answer my second issue about how to empty a table. I have an arimport table that only holds things to be imported. 1500 of the 2000 records posted to the import table. I need to empty that so all 2000 post at the same time. What is the standard format for a query to empty a table
Empty arimport?
You did not answer my second issue about how to empty a table. I have an arimport table that only holds things to be imported. 1500 of the 2000 records posted to the import table. I need to empty that so all 2000 post at the same time. What is the standard format for a query to empty a table
Empty arimport?
DELETE FROM YourTable
Or, if there is no key field on the table you can use:
TRUNCATE TABLE YourTable
Or, if there is no key field on the table you can use:
TRUNCATE TABLE YourTable
ASKER
This empties all records from the table?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
All comments on this question from Carl were great help and quickly offered. Very helpful
ASKER
Thank you so much for this assistance.
Note: using truncate will reset any identity field counter!
you cannot compare NULL values with the equal sign...
you must use the keyword "IS"
example:
To completelly empty a table you should use truncate because
a. it is MUCH faster (MUCH, MUCH faster)... MUCH ;-)
b. it will not fill up your db's log files
In some cases TRUNCATE is not an option, when that is the case, and you need to delete manymany rows, you have to loop through the table, send DELETE statements and commit (if you are using transactions, and you should) every few hundred rows.
Just be careful, TRUNCATE cannot be rolled back
you must use the keyword "IS"
example:
UPDATE mytable
SET myfield='something'
WHERE mydate is null
AND myotherfield is not nullTo completelly empty a table you should use truncate because
a. it is MUCH faster (MUCH, MUCH faster)... MUCH ;-)
b. it will not fill up your db's log files
In some cases TRUNCATE is not an option, when that is the case, and you need to delete manymany rows, you have to loop through the table, send DELETE statements and commit (if you are using transactions, and you should) every few hundred rows.
Just be careful, TRUNCATE cannot be rolled back
ASKER
I appreciate all the assistance, the original answers solved my immediate problem. You all are so great.
Open in new window