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.
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 = NULLWHERE Name.STATUS='IM'[/code] |
|
|
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 |
|
|
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. |
|
|
djpin
Starting Member
27 Posts |
Posted - 2014-08-01 : 14:58:50
|
Much appreciated.Thanks!Thanks, DJ |
|
|
|
|
|
|
|