DELETE and ABORT with JSON | Teradata Vantage - DELETE and ABORT with JSON Examples - Advanced SQL Engine - Teradata Database

JSON Data Type

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
uwa1591040057999.ditamap
dita:ditavalPath
uwa1591040057999.ditaval
dita:id
B035-1150
lifecycle
previous
Product Category
Teradata Vantageā„¢

Setting Up the DELETE Example

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":"Cameron","age":24}'); 

Example: Using the DELETE Statement

Selectively delete data that meets the criteria.

DELETE my_table WHERE CAST (edata.JSONExtractValue('$.age') AS INTEGER) = 24; 
To see the result of the DELETE, run: SELECT * FROM my_table;
*** No rows found 

Setting Up the ABORT Example

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":"Cameron","age":24}'); 

Example: Using the ABORT Statement

Abort on a portion of the JSON instance.

ABORT 'JSON Abort' 
FROM my_table 
WHERE CAST (edata.JSONExtractValue('$.age') AS INTEGER) = 24; 
Result:
*** Failure 3513 JSON Abort.