SQL Update with Subquery Null Conditional

Question

I'm trying to run a query akin to the following. I'm basically trying to change the format of the updated text conditionally. I need some help as I've tried this a few different ways without success. Thanks in advance.

UPDATE bL SET 
bL.[QUANTITY] = bL.[QUANTITY] + 2,
bL.[SERIAL_NUMBERS] = 
    (
      CASE WHEN bL.[SERIAL_NUMBERS] IS NOT NULL 
      THEN ISNULL(bL.[SERIAL_NUMBERS], '') + 'EOSB7U310007; EOSB7U310022' 
      ELSE bL.[SERIAL_NUMBERS] + '; EOSB7U310007; EOSB7U310022'
    ) 
 FROM [ROBOTICS_OPTICS_MECHUAT].[dbo].[AOF_BOXES_LINES] AS bL 
 LEFT JOIN [ROBOTICS_OPTICS_MECHUAT].[dbo].[AOF_BOXES] AS bO 
 ON bL.[AOF_BOXES_ID] = bO.[ID] 
 WHERE bO.[SELECTED] = 'True'

Also, concatenating a `null` to a string will result in `null`. — Zohar Peled, Apr 18 '17 at 18:37
@ZoharPeled Sorry, I forgot to add that I ISNULL() given that the cell value is null. — addohm, Apr 18 '17 at 18:38
Checking the `bO.[SELECTED] = 'True'` will change your `Left Join` to an `Inner Join`. — SS_DBA, Apr 18 '17 at 18:38
@GSerg The consistent problem that I run in to when testing is `Incorrect syntax near ')'.` I'm still running into this with your change. — addohm, Apr 18 '17 at 18:39
@Jaberwocky. Since you aren't checking ISNull(bO.[SELECTED],'') = 'True'. You are implicitly looking for bO.[SELECTED] = 'True'. Where null values won't get evaluated. — SS_DBA, Apr 18 '17 at 18:41
@WEI_DBA Wow, how did I miss that... This goes to show ya, a second set of eyes when you're tired of looking at something is ALWAYS beneficial :D — addohm, Apr 18 '17 at 18:41
Zohar's shortened version will probably work best for you. Give that a shot. — SS_DBA, Apr 18 '17 at 18:46

score 2 · Answer 1 · edited May 23 '17 at 12:17

So, putting all the comments I found correct together in a single answer, your code should probably look like this:

UPDATE bL SET 
bL.[QUANTITY] = bL.[QUANTITY] + 2,
bL.[SERIAL_NUMBERS] = 
    (
      CASE WHEN bL.[SERIAL_NUMBERS] IS NULL 
      THEN 'EOSB7U310007; EOSB7U310022' 
      ELSE bL.[SERIAL_NUMBERS] + '; EOSB7U310007; EOSB7U310022'
      END
    ) 
 FROM [ROBOTICS_OPTICS_MECHUAT].[dbo].[AOF_BOXES_LINES] AS bL 
 LEFT JOIN [ROBOTICS_OPTICS_MECHUAT].[dbo].[AOF_BOXES] AS bO 
 ON bL.[AOF_BOXES_ID] = bO.[ID] 
 AND bO.[SELECTED] = 'True'

However it can be shorten - like this:

UPDATE bL SET 
bL.[QUANTITY] = bL.[QUANTITY] + 2,
bL.[SERIAL_NUMBERS] = ISNULL(bL.[SERIAL_NUMBERS] + '; ', '') + 'EOSB7U310007; EOSB7U310022'
 FROM [ROBOTICS_OPTICS_MECHUAT].[dbo].[AOF_BOXES_LINES] AS bL 
 LEFT JOIN [ROBOTICS_OPTICS_MECHUAT].[dbo].[AOF_BOXES] AS bO 
 ON bL.[AOF_BOXES_ID] = bO.[ID] 
 AND bO.[SELECTED] = 'True'

This is because it the value of bL.[SERIAL_NUMBERS] is null then the concatenation of it with ; will also be null.

Notes:

I've moved bO.[SELECTED] = 'True' from the WHERE clause to the ON clause. This is because when using a left join, all the conditions on the right table should be on the ON clause, otherwise the join is turned into an inner join.
Seems like you are using the bL.[SERIAL_NUMBERS] column to store multiple values. This is a big mistake in 99.9999% of the cases.
Read Is storing a delimited list in a database column really that bad?, where you will see a lot of reasons why.

Noted. The customer designed their system this way, I am just adapting and integrating with them :) — addohm, Apr 18 '17 at 19:26

score 1 · Answer 2 · answered Apr 18 '17 at 18:40

You don't need the SELECT and you have to END your CASE statement.

UPDATE  bL
SET     bL.[QUANTITY] = bL.[QUANTITY] + 2
      , bL.[SERIAL_NUMBERS] = ( CASE WHEN bL.[SERIAL_NUMBERS] IS NOT NULL 
                                     THEN ISNULL(bL.[SERIAL_NUMBERS] , '') + 'EOSB7U310007; EOSB7U310022'
                                     ELSE bL.[SERIAL_NUMBERS] + '; EOSB7U310007; EOSB7U310022'
                                END )
FROM    [ROBOTICS_OPTICS_MECHUAT].[dbo].[AOF_BOXES_LINES] AS bL
LEFT JOIN [ROBOTICS_OPTICS_MECHUAT].[dbo].[AOF_BOXES] AS bO ON bL.[AOF_BOXES_ID] = bO.[ID]
WHERE   bO.[SELECTED] = 'True';

SQL Update with Subquery Null Conditional

2 Answers2