Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Update Query Help

Author  Topic 

djpin
Starting Member

27 Posts

Posted - 2014-07-29 : 10:46:52
I'm pretty comfortable running select queries but update queries

I have a query that pulls all records that have a Status of “IM”, Member ID#, Name, a Member Type of “ANY” and a Company ID of “ANY”.

SELECT Name.STATUS, Name.ID, Name.LAST_FIRST, Name.MEMBER_TYPE, Name.CO_ID
FROM APSCU_PROD.dbo.Name
WHERE Name.STATUS='IM'

What I want to do is: Update the Status to “A”
Update the Member Type to “NM”
Update the Company ID to “NULL”

Any assistance would be great!


Thanks,

DJ

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-07-29 : 11:46:45
[code]--SELECT Name.STATUS, Name.ID, Name.LAST_FIRST, Name.MEMBER_TYPE, Name.CO_ID
--FROM APSCU_PROD.dbo.Name
UPDATE APSCU_PROD.dbo.Name SET
STATUS = 'A',
MEMBER_TYPE = 'NM',
CO_ID = NULL
WHERE Name.STATUS='IM'[/code]
Go to Top of Page

djpin
Starting Member

27 Posts

Posted - 2014-07-29 : 21:03:26
Thanks.

I tried running the query and got a error stating "the co_id can't contain a null value". Not sure why because if I manually delete it the record is fine. Not all entries are affiliated with an organization.

Thanks,

DJ
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-07-30 : 08:14:25
quote:
Originally posted by djpin

Thanks.

I tried running the query and got a error stating "the co_id can't contain a null value". Not sure why because if I manually delete it the record is fine. Not all entries are affiliated with an organization.

Thanks,

DJ

That is because the column CO_ID is defined to not allow NULL values to be inserted into it. If the business rules that you have require that some of the entries for CO_ID be null, then it is a flaw in the design of the table.

You have two alternatives:
a) change the table design to allow CO_ID to contain NULL values.
b) pick an impossible value (such as 0, or -2147483648 if CO_ID is of type int, or empty string '' if CO_ID is of character type).

If you want to go with option (a) you have to be careful to make sure that other tables/constraints/queries that depend on CO_ID being non-NULL will not break.
Go to Top of Page

djpin
Starting Member

27 Posts

Posted - 2014-08-01 : 14:58:50
Much appreciated.

Thanks!

Thanks,

DJ
Go to Top of Page
   

- Advertisement -