delete multiple records using array in postgresql

Question

I need a procedure for delete multiple rows in multiple tables by passing array set of values in parameters

Consider 3 tables:

student_master(id,student_name);
subject_master(id,subject_name);
marks(id,student_id,student_id,subject_id,marks)

here in parameter for e.g student id will be: a['1','3','7','15']

What is the procedure for this criteria?

From which tables do you want to delete the rows? To which IDs do those values relate to? And why are you passing numbers as strings? — user330315
– user330315, Commented Mar 27, 2017 at 7:22
by passing student id i want to delete all the data related to that student id. from other tables(subject and marks) i need just pass a id's in array. — dinesh danny
– dinesh danny, Commented Mar 27, 2017 at 7:30
Do you use foreign keys? If so, how did you define them? ON DELETE CASCADE can help you a lot in your case. — pozs
– pozs, Commented Mar 27, 2017 at 8:01

light souls · Accepted Answer · 2017-03-27 07:41:16Z

20

If your student_id s are integer or bigint then use without quote:

delete from marks
where student_id = ANY(Array [1,3,7,15])
returning student_id

answered Mar 27, 2017 at 7:41

light souls

7381 gold badge9 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1