Setting Up the Update Examples
Create and populate table(s) to use in subsequent example(s).
CREATE TABLE my_table (eno INTEGER, edata JSON(100)); INSERT INTO my_table (1, NEW JSON('{"name" : "Cameron", "age" : 24}'));
Example: UPDATE and SET
The following statement sets the edata column value and updates the table.
UPDATE my_table SET edata = NEW JSON('{"name" : "Justin"}') WHERE edata.JSONExtractValue('$.name') = 'Cameron';
Result: To see the result, run: SELECT edata FROM my_table;
edata ----- {"name" : "Justin"}
Example: UPDATE Column Value
The following statement sets the edata column value and updates the table.
UPDATE my_table SET edata = '{"name" : "George"}';
Result: To see the name has been updated, run: SELECT edata FROM my_table;
edata ----- {"name" : "George"}
Setting Up the Merge Examples
Create and populate table(s) to use in subsequent example(s).
CREATE TABLE my_table (eno INTEGER, edata JSON(100)); INSERT INTO my_table(1,'{"name" : "Justin", "phoneNumber" : 9595552611}'); INSERT INTO my_table(2,'{"name" : "Cameron", "phoneNumber" : 9595552612}'); CREATE TABLE my_table2 (eno INTEGER, edata JSON(100)); INSERT INTO my_table2(1,'{"age":24}'); INSERT INTO my_table2(3,'{"age":34}');
Example: MERGE When Matched Then Update
The example shows how to use the MERGE WHEN MATCHED THEN UPDATE clause.
MERGE INTO my_table USING (SELECT eno, edata FROM my_table2) AS S(a,b) ON eno = S.a AND CAST (edata.JSONExtractValue('$.name') AS VARCHAR(20)) = 'Justin' WHEN MATCHED THEN UPDATE SET edata = '{"name" : "Kevin", "phoneNumber" : 9595552613}';
To see the result of the query, run: SELECT * FROM my_table ORDER BY eno;
eno edata --------- 1 {"name" : "Kevin", "phoneNumber" : 9595552613} 2 {"name" : "Cameron", "phoneNumber" : 9595552612}
Example: MERGE When Not Matched Then Insert
The example shows how to use the MERGE WHEN NOT MATCHED THEN INSERT clause.
MERGE INTO my_table USING (SELECT eno, edata FROM my_table2) AS S(a,b) ON eno = S.a AND CAST (edata.JSONExtractValue('$.name') AS VARCHAR(20)) = 'Mike' WHEN NOT MATCHED THEN INSERT (eno, edata) VALUES (S.a, NEW JSON('{"name" : "Mike", "phoneNumber" : 9595552614}'));
To see the result of the query, run: SELECT * FROM my_table ORDER BY eno;
eno edata ----------- 1 {"name" : "Kevin", "phoneNumber" : 9595552613} 2 {"name" : "Cameron", "phoneNumber" : 9595552612} 2 {"name" : "Mike", "phoneNumber" : 9595552614}