February 25, 2006 at 7:53 am
I am trying to update a table with the following statement:
UPDATE OccRadiology
SET OccRadiology.Ipsil= (
SELECT [CodeLookUp].[CodeID]
FROM CodeLookUp
WHERE OccRadiology.Ipsil = '' + CodeLookUp.Code+''
AND CodeLookUp.CType = 93)
This works fine as long as there is a match on OccRadiology.ipsil (varchar) and CodeLookUp.Code (int)
but throws an error if Ipsil is a null string ("") or any other reason there is no match. I want it to just skip the record if there is no match. What syntax do I need?
Sam
February 25, 2006 at 9:22 am
Instead of a subselect try a join
UPDATE OccRadiology
SET OccRadiology.Ipsil= [CodeLookUp].[CodeID]
FROM CodeLookUp
WHERE OccRadiology.Ipsil = '' + CodeLookUp.Code+'' AND CodeLookUp.CType = 93)
If you really like the subselect
add after the subselect
WHERE oCCRadiology.ipsil in (select ''+codelookup.code+'' from yada where ctype = 93)
cheers
February 25, 2006 at 11:37 am
*convert the int properly with convert(varchar(length_of_field),code)
*another way of updating
UPDATE OccRadiology
SET OccRadiology.Ipsil= goodcodes.code
FROM OccRadiology
INNER JOIN /*only the ones who match*/
(select convert(varchar(30),CodeLookUp.Code) as code /*ipsil is varchar*/
FROM CodeLookUp
WHERE CodeLookUp.CType = 93
) AS goodcodes
ON OccRadiology.Ipsil=goodcodes.code
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply