SQL Server : update table with join condition using JSON input

Question

I have a table with columns position_id and column_id. I am getting a JSON response from an API call, and I want to update my table using the value from the JSON response using the position_id.

Here is an example

JSON Table:

Table in database(I am calling it #updateTable):

I want to update my #updateTable using the JSON input as below:

How can I achieve that? Is it possible without looping through table. Set based operation would be better I think.

(There are equal number of rows in JSON table and #updateTable for a position_id).

Here is the code for table creation that I have used in my example.

DROP TABLE IF EXISTS #inputJSONTable
DROP TABLE IF EXISTS #updateTable

DECLARE @inputJSON  nvarchar(max) =
    '[  
       {  
          "column_id":"7",
          "position_id":"787",
          "column1":"GoodValue",
          "column2":"ReplacedValue"
       },
       {  
          "column_id":"8",
          "position_id":"787",
          "column1":"ReplacedValue",
          "column2":"GoodValue"
       }
    ]'

DECLARE @inJSON NVARCHAR(MAX);
SET @inJSON = RTRIM(LTRIM(@inputJSON));

SELECT
    *
INTO 
    #inputJSONTable
FROM 
    OPENJSON(@inJSON)
    WITH (
             [column_id] VARCHAR(50) '$.column_id',
             [position_id] VARCHAR(50) '$.position_id',
             [column1] VARCHAR(50) '$.column1',
             [column2] VARCHAR(50) '$.column2'
         );

-- SELECT * FROM #inputJSONTable

CREATE TABLE #updateTable
(
      Id INT IDENTITY(1, 1) PRIMARY KEY,
      column_id INT, 
      position_id INT, 
      column1 VARCHAR(50), 
      column2 VARCHAR(50)
)

INSERT INTO #updateTable
VALUES (7, 787, 'GoodValue', 'ReplaceME'),
       (8 , 787,  'ReplaceME', 'GoodValue')

SELECT * FROM #inputJSONTable
SELECT * FROM #updateTable

Thanks for helping out.

UPDATE:

I used this join to update the table :

UPDATE up
SET up.column1 = ip.column1,
    up.column2 = ip.column2
FROM #updateTable up 
INNER JOIN #inputJSONTable ip ON up.column_id = ip.column_id

Your JSON doesn't appear to be passing an ID; how do you know what row(s) each entry relates to? Luck that the order the JSON is the same as that in the table? That could likely (will) not be true one day. — Thom A, Feb 19 '19 at 19:52
Since both rows have the same `position_id` what column can you use to link the first row of the JSON table with `id` 1 in the update table? — Tab Alleman, Feb 19 '19 at 19:53
@Larnu and Tab Allerman ,I see your point. position_id is not enough for this. I will have to alter JSON response or table structure. — ProgSky, Feb 19 '19 at 19:56
@ProgSky, do you have only 2 value columns, could you try using position_id and column1/column2 to find rows to update? — rs., Feb 19 '19 at 20:02
@rs. actually its simpler than that, my input has a column_id column that can be used to differentiate which row to update. I have update the question. — ProgSky, Feb 19 '19 at 20:08
@ProgSky, how do you decide which value needs to be replaced? I know your example says ReplaceMe but it is not clear when to update that, if they are different? — rs., Feb 19 '19 at 20:12
Yes replace if they are different. I have posted my update with join, does it look good ? — ProgSky, Feb 19 '19 at 20:17
@ProgSky, that will work ok if you want your table to match json input. — rs., Feb 19 '19 at 20:26
@rs. yes. payload must match the table columns. I am enforcing that. — ProgSky, Feb 19 '19 at 20:28

score 0 · Accepted Answer · answered Feb 21 '19 at 15:24

0

This query worked.

UPDATE up
SET up.column1 = ip.column1,
    up.column2 = ip.column2
FROM #updateTable up 
INNER JOIN #inputJSONTable ip ON up.column_id = ip.column_id

answered Feb 21 '19 at 15:24

ProgSky

2,530
8
39
65

SQL Server : update table with join condition using JSON input

1 Answers1