CODEWITHSUNDEEP
sqlsql-server
Araf
Araf

Reputation: 273

How to add if/else condition in SQL Server query?

I have to parse a json like this:

[
    { "id": 2, "name": "John", "age": 25 },
    { "id": 5, "name": "Jane", "age": 18 }
]

I have to parse it and check if the name already exists in userInfo table. If the name is found in that table, then I only update the age.

Else, I have to insert the data into the userInfo table.

Here's what I did so far:

DROP TABLE IF EXISTS #tmp

DECLARE @json NVARCHAR(MAX);

SET @json = N'[
  {"id": 2, "name": "John", "age":25},
  {"id": 5, "name": "Jane", "age": 18} 
]';

SELECT [id], [info] 
INTO #tmp
FROM OPENJSON(@json)
WITH
    ([id] NVARCHAR(100), 
     [name] NVARCHAR(100),
     [age] Int);

SELECT * FROM #tmp

Now I want to insert or update these data in table userInfo.

If the name from #tmp table already exists in userInfo table, then update the age, else insert the data into the userInfo table.

I don't know what the syntax for this operation is, or if it is possible to do.

Upvotes: 0

Views: 180

Answers (1)

DhruvJoshi
DhruvJoshi

Reputation: 17126

You can use MERGE keyword for this

MERGE userInfo t 
    USING #tmp s
ON (s.name = t.name)
WHEN MATCHED
    THEN UPDATE SET 
        t.age = s.age
WHEN NOT MATCHED THEN 
         INSERT (name, age)
         VALUES (s.name, s.age);

Just to be more succinct I'd do the same this way

MERGE userInfo t 
        USING (SELECT *
FROM OPENJSON(@json)
WITH
(
[id] NVARCHAR(100), 
[name] NVARCHAR(100),
[age] Int
)) s
    ON (s.name = t.name)
    WHEN MATCHED
        THEN UPDATE SET 
            t.age = s.age
    WHEN NOT MATCHED THEN 
             INSERT (name, age)
             VALUES (s.name, s.age);

Upvotes: 2

Related Questions