update SQL rows with multidimensional array as source-CodePudding

Goal: I want to update existing entries in my SQL table [dbo.properties]. The SQL command is executed in PHP. The PHP file again receives an array, as new data source. This array contains a unique ID, propertyName and the actual value.

Problem: How do I loop, in SQL, through an array and make sure to update the values at the correct point?

My SQL table look like:

[id] as [int] increments by DB
[property] as [varchar(50)]
[value] as [varchar(50)]

The passed array look like:

0: Object {id:'30', property:'sugar', value:'20g'}
1: Object {id:'37', property:'salt', value:'10g'}
2: Object {id:'38', property:'chocolate', value:'120g'}

I know how to do it with single data or to delete multiple values with a list. But I its tough to find anything similar for my case. Especually as I need to update all in one query and the amount of rows is dynamic. Means it could be that only one item is updated or 10.

Pseudo SQL query for better understand

BEGIN TRANSACTION [updateProperties]
  BEGIN TRY 
    UPDATE properties SET 
    // Somehow iterate through array
    property = ('array[Pos][Entry1]'),
    value = ('array[Pos][Entry2]')
    WHERE id = ('array[Pos][Entry0]')
  COMMIT TRANSACTION [updateProperties]
  END TRY
  BEGIN CATCH
    ROLLBACK TRANSACTION [updateProperties]
  END CATCH;

CodePudding user response：

If you pass this in as a proper JSON array, you can use OPENJSON to join to your table

DECLARE @json nvarchar(max) = N'[
  {"id":30, "property":"sugar", "value":"20g"},
  {"id":37, "property":"salt", "value":"10g"},
  {"id":38, "property":"chocolate", "value":"120g"}
]';

UPDATE p
SET property = j.property,
    value = j.value
FROM properties p
JOIN OPENJSON(@json)
  WITH (
    id int,
    [property] varchar(50),
    [value] varchar(50)
  ) j ON j.id = p.id;

You can use MERGE if you want to update existing and insert new rows.

On a side note, I would advise you not to store value as a varchar. Instead, split it into amount and unit.