Convert multiple SQL columns in a row to JSON and update to a column in the same row

I’m trying to serialize multiple columns in a row to JSON and update it to a column in the same row:

UPDATE  dbo.ItemSpecification
SET ItemCameraData = (SELECT CameraFocalLengthMin, CameraFocalLengthMax, CameraHousingType, CameraMountType, CameraResolutionHorizontal, CameraResolutionVertical, CameraType FROM dbo.ItemSpecification
WHERE Id = Id
FOR JSON AUTO)

The select statment is ignoring the “Where Id = Id” and trying to serialize the whole table into each row instead of just the columns from the single row it’s updating.

How do I specify in the select statement to only serialize the columns in the single row it’s updating and not the entire table?

UPDATE:

here it is with the answer applied. Azure SQL wants to alias the table in the FROM statement

UPDATE  dbo.ItemSpecification
SET ItemCameraData = (SELECT CameraFocalLengthMin, CameraFocalLengthMax, CameraHousingType, CameraMountType, CameraResolutionHorizontal, CameraResolutionVertical, CameraType FROM dbo.ItemSpecification
WHERE Id = outer_stmt.Id
FOR JSON AUTO)
FROM dbo.ItemSpecification outer_stmt

Answer

You haven’t mentioned what database you’re using, so my syntax may be slightly off, but the concept should match.

It’s unlikely your inner statement knows what you’re trying to do with Id = Id. You need to reference the outer table, or you’re going to get the equivalent of 1 = 1. Since this is the same table, I’ve aliased the outer table so it can then be referenced in the inner select statement.

UPDATE  dbo.ItemSpecification as outer_stmt
SET ItemCameraData = (
  SELECT 
    CameraFocalLengthMin, CameraFocalLengthMax, CameraHousingType
    , CameraMountType, CameraResolutionHorizontal, CameraResolutionVertical
    , CameraType 
  FROM dbo.ItemSpecification
  WHERE 
    Id = outer_stmt.Id
  FOR JSON AUTO
)