Solved

Update Join Query Cannot insert Null

Posted on 2007-12-02
5
277 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
  • 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2008 R2 - Execution Plan 3 57
Oracle Subquery bad Join 11 60
data lookup in Oracle - need suggestions 55 104
Oracle query output question 4 36
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

861 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now