I want to delete multiple records from table at the same time.
Sample input:
{"INPUT":{"ID":"2200038,2200039,2200073,2200019"}}
Input will be provided from the application i.e.,
ID can be random - it gets changed based on requirements.
delete from mytable
where id = ....?
I want to delete multiple ID's coming from the input at the same time.
To delete multiple rows at once with different IDs, one approach is to use IN:
DELETE FROM mytable
WHERE ID IN (2200038,2200039,2200073,2200019)
Here's some documentation and further examples: http://www.postgresqltutorial.com/postgresql-in/
You may extract the ids from your json string as an array and delete those using ANY operator
WITH t AS
(
SELECT '{"INPUT":{"ID":"2200038,2200039,2200073,2200019"}}' AS input
)
DELETE FROM mytable
WHERE id = ANY ( SELECT unnest(
String_to_array(input::json->'INPUT'->>'ID',',')::int[])
FROM t );
Here's a demo using a Bind variable for input in psql. Note that UNNEST was not needed here.
\set input '{"INPUT":{"ID":"2200038,2200039,2200073,2200019"}}'
knayak=# DELETE FROM mytable WHERE
id = ANY( String_to_array(:'input'::json->'INPUT'->>'ID',',')::int[] )
DELETE 2
Maybe some dynamic sql will help
EXEC SQL BEGIN DECLARE SECTION;
const char *stmt = "DELETE FROM tablename WHERE ID IN(?);";
EXEC SQL END DECLARE SECTION;
EXEC SQL PREPARE mystmt FROM :stmt;
inputdata varchar; -- remove unwanted parts of the string
EXEC SQL EXECUTE mystmt USING inputdata;
