Solved

Update Join Query Cannot insert Null

Posted on 2007-12-02
5
307 Views
Last Modified: 2010-05-18
I'm trying to update a particular table with contents from another table but keep getting an error.

UPDATE Cogsdale
SET Cogsdale.ADDRESS1 = Cleaned.Address1, Cogsdale.ADDRESS2 = Cleaned.Address2,
Cogsdale.umCityName = Cleaned.City, Cogsdale.State = Cleaned.State, Cogsdale.umZipCode = Cleaned.Zip
FROM UMRM0112 Cogsdale INNER JOIN RP_CleanAddresses Cleaned
ON Cogsdale.umAddressCode = Cleaned.AddressCode  

I get:
Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'STATE', table 'test.dbo.UMRM0112'; column does not allow nulls. UPDATE fails.
The statement has been terminated.

I don't see any nulls in the State field...
0
Comment
Question by:Rayzback
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 25

Expert Comment

by:imitchie
ID: 20392849
Can you recheck the Cleaned table?
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20392855
try this query

SELECT Cleaned.AddressCode, Cleaned.State
FROM UMRM0112 Cogsdale
INNER JOIN RP_CleanAddresses Cleaned ON Cogsdale.umAddressCode = Cleaned.AddressCode
ORDER BY 2

The nulls should show up at the bottom (or top)
0
 
LVL 1

Author Comment

by:Rayzback
ID: 20392945
Argggg I do... WHERE State = NULL returned nothing?

Can I replace the nulls with ' '?
0
 
LVL 25

Accepted Solution

by:
imitchie earned 500 total points
ID: 20392954
You CANNOT test for NULL using =.
Use
WHERE State IS NULL

as for your query, use

UPDATE Cogsdale
SET Cogsdale.ADDRESS1 = Cleaned.Address1, Cogsdale.ADDRESS2 = Cleaned.Address2,
Cogsdale.umCityName = Cleaned.City, Cogsdale.State = IsNull(Cleaned.State, ''), Cogsdale.umZipCode = Cleaned.Zip
FROM UMRM0112 Cogsdale INNER JOIN RP_CleanAddresses Cleaned
ON Cogsdale.umAddressCode = Cleaned.AddressCode  
0
 
LVL 10

Expert Comment

by:LennyGray
ID: 20393338
Change the property of the data element STATE to allow nulls.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

691 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