Solved

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.

Posted on 2011-02-28
13
353 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:SharonStys
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 34995760
Try:
UPDATE Usage SET TransDate='2011-03-01 00:00:00.000' WHERE TransDate IS NULL

Open in new window

0
 
LVL 1

Author Comment

by:SharonStys
ID: 34995791
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?
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 34995801
Ok, try this:
UPDATE Usage SET TransDate='2011-03-01 00:00:00.000' WHERE TransDate IS NULL 

Open in new window

If you still can't see it, the basic jist is that you need to use "TransDate IS NULL" rather than "TransDate = NULL".

NULL is a non-value so a standard test for equality doesn't work, so you need to use the "IS" keyword instead.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 10

Expert Comment

by:Jacco
ID: 34995832
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".
0
 
LVL 1

Author Comment

by:SharonStys
ID: 34995839
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?
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 34995850
DELETE FROM YourTable

Or, if there is no key field on the table you can use:

TRUNCATE TABLE YourTable
0
 
LVL 1

Author Comment

by:SharonStys
ID: 34995854
This empties all records from the table?
0
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 500 total points
ID: 34995882
Yes. TRUNCATE is faster, but is only an option if there is no key field on the table.
0
 
LVL 1

Author Closing Comment

by:SharonStys
ID: 34995895
All comments on this question from Carl were great help and quickly offered.  Very helpful
0
 
LVL 1

Author Comment

by:SharonStys
ID: 34995900
Thank you so much for this assistance.
0
 
LVL 10

Expert Comment

by:Jacco
ID: 34995910
Note: using truncate will reset any identity field counter!
0
 
LVL 3

Expert Comment

by:LFLFM
ID: 34995937
you cannot compare NULL values with the equal sign...
you must use the keyword "IS"

example:
UPDATE mytable
SET myfield='something'
WHERE mydate is null
AND myotherfield is not null

Open in new window


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
0
 
LVL 1

Author Comment

by:SharonStys
ID: 34996006
I appreciate all the assistance, the original answers solved my immediate problem.  You all are so great.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Help with query 3 25
SQL Server tables join on parse list 6 23
MS SQL BCP Extra Lines Between Records 2 19
ms sql + get number in list out of total 7 29
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question