tsql : update table with case statement - whats wrong with it?

Question

I have the following update statement :

UPDATE D     
  SET D.Xtra1 = CASE 
                  when ExtendedProperties.PropertyName="Transmittal Number" then ExtendedProperties.PropertyValue 
                  else NULL 
                END, 
      D.Xtra2 = CASE
                  when (ExtendedProperties.PropertyName="File Identification") then ExtendedProperties.PropertyValue 
                  else NULL 
                END, 
      D.Xtra3 = CASE
                  when (ExtendedProperties.PropertyName="File Owner") then ExtendedProperties.PropertyValue 
                  else NULL 
                END, 
      D.Xtra4 =  case 
                  when (ExtendedProperties.PropertyName="Dept File Cabinet") then     ExtendedProperties.PropertyValue 
                  else NULL 
                END ,
      D.Xtra5 = case 
                 when (ExtendedProperties.PropertyName="Record Storage Box Number") then ExtendedProperties.PropertyValue 
                 else NULL 
                END ,
      D.Xtra6 = case 
                 when (ExtendedProperties.PropertyName="ImportCode") then extendedProperties.PropertyValue 
                 else NULL 
                END , 
      D.Xtra7 = case 
                 when (ExtendedProperties.PropertyName="ImageKey") then extendedProperties.PropertyValue 
                else NULL 
                END , 
      D.Xtra8 =  case 
                 when (ExtendedProperties.PropertyName="Boundary") then ExtendedProperties.PropertyValue 
                 else NULL 
                END ,
      D.Xtra9 = case 
                 when (ExtendedProperties.PropertyName="Offset") then ExtendedProperties.PropertyValue 
                 else NULL 
                END ,
      D.Xtra10 = case 
                 when (ExtendedProperties.PropertyName="UniqueFilename") then ExtendedProperties.PropertyValue 
                 else NULL 
                END

from ExtendedProperties inner join data D on D.ItemID = ExtendedProperties.ItemID

the updating isn't working and i have yet to figure out why.

if i do a single direct update statement, it does allow me to write to the field. if i do a select just on one of the case statements, it draw the field correctly.

any ideas?

Answer 1

You need to include the data table in your FROM clause

from  data
join ExtendedProperties on data.ItemID = ExtendedProperties.ItemID 

Answer 2

Your problem is that you are transposing a table here. The source table has N rows for an item (ItemId, PropertyName and PropertyValue), but the target table has one row with N columns for that (Xtra1 … Xtra10).

Left joins can be used to transpose the source from rows to columns and have all values available at the same time, instead of sequentially.

UPDATE
  data 
SET
  data.Xtra1  = ep01.PropertyValue, 
  data.Xtra2  = ep02.PropertyValue, 
  data.Xtra3  = ep03.PropertyValue, 
  data.Xtra4  = ep04.PropertyValue,
  data.Xtra5  = ep05.PropertyValue,
  data.Xtra6  = ep06.PropertyValue,
  data.Xtra7  = ep07.PropertyValue,
  data.Xtra8  = ep09.PropertyValue,
  data.Xtra9  = ep09.PropertyValue,
  data.Xtra10 = ep10.PropertyValue
FROM
  data
  LEFT JOIN ExtendedProperties ep01 ON data.ItemID = ep01.ItemID AND ep01.PropertyName = 'Transmittal Number'
  LEFT JOIN ExtendedProperties ep02 ON data.ItemID = ep02.ItemID AND ep02.PropertyName = 'File Identification'
  LEFT JOIN ExtendedProperties ep03 ON data.ItemID = ep03.ItemID AND ep03.PropertyName = 'File Owner'
  LEFT JOIN ExtendedProperties ep04 ON data.ItemID = ep04.ItemID AND ep04.PropertyName = 'Dept File Cabinet'
  LEFT JOIN ExtendedProperties ep05 ON data.ItemID = ep05.ItemID AND ep05.PropertyName = 'Record Storage Box Number'
  LEFT JOIN ExtendedProperties ep06 ON data.ItemID = ep06.ItemID AND ep06.PropertyName = 'ImportCode'
  LEFT JOIN ExtendedProperties ep07 ON data.ItemID = ep07.ItemID AND ep07.PropertyName = 'ImageKey'
  LEFT JOIN ExtendedProperties ep08 ON data.ItemID = ep08.ItemID AND ep08.PropertyName = 'Boundary'
  LEFT JOIN ExtendedProperties ep09 ON data.ItemID = ep09.ItemID AND ep09.PropertyName = 'Offset'
  LEFT JOIN ExtendedProperties ep10 ON data.ItemID = ep10.ItemID AND ep10.PropertyName = 'UniqueFilename'

Have a conglomerate index over (ItemID, PropertyName) for this to work smoothly if you have many records.

---

EDIT: Alternatively, you can do this:

UPDATE
  data 
SET
  data.Xtra1  = (SELECT PropertyValue FROM ExtendedProperties WHERE ItemID = data.ItemID AND PropertyName = 'Transmittal Number'), 
  data.Xtra2  = (SELECT PropertyValue FROM ExtendedProperties WHERE ItemID = data.ItemID AND PropertyName = 'File Identification'), 
  data.Xtra3  = (SELECT PropertyValue FROM ExtendedProperties WHERE ItemID = data.ItemID AND PropertyName = 'File Owner'), 
  data.Xtra4  = (SELECT PropertyValue FROM ExtendedProperties WHERE ItemID = data.ItemID AND PropertyName = 'Dept File Cabinet'),
  data.Xtra5  = (SELECT PropertyValue FROM ExtendedProperties WHERE ItemID = data.ItemID AND PropertyName = 'Record Storage Box Number'),
  data.Xtra6  = (SELECT PropertyValue FROM ExtendedProperties WHERE ItemID = data.ItemID AND PropertyName = 'ImportCode'),
  data.Xtra7  = (SELECT PropertyValue FROM ExtendedProperties WHERE ItemID = data.ItemID AND PropertyName = 'ImageKey'),
  data.Xtra8  = (SELECT PropertyValue FROM ExtendedProperties WHERE ItemID = data.ItemID AND PropertyName = 'Boundary'),
  data.Xtra9  = (SELECT PropertyValue FROM ExtendedProperties WHERE ItemID = data.ItemID AND PropertyName = 'Offset'),
  data.Xtra10 = (SELECT PropertyValue FROM ExtendedProperties WHERE ItemID = data.ItemID AND PropertyName = 'UniqueFilename')

Less code, maybe easier to understand, maybe not as fast (that's a guess).