1

I am writing an update query with IN clause in which I am passing List

update table set postcode={#postcode} where id in
<foreach collection="addresses" index="index" item="item" open="(" 
separator="," close=")">#{item}</foreach>

This runs fine when I have less than 1000 records in addresses.If there are more than 1000 records I get below error:

ORA-01795: maximum number of expressions in a list is 1000. How can this be handled?I came across some threads where they have handled in hibernate.How can I handle it with myibatis with more than 1000 records.

user9193174
  • 363
  • 1
  • 4
  • 12
  • Use a collection [[1](https://stackoverflow.com/a/34699771/1509264), [2](https://stackoverflow.com/a/41161057/1509264)] since they can handle more than 1000 items. Can't help with using collections in ibatis though but its built in Java and the first link shows how to pass a collection as a bind parameter it in Java. – MT0 Feb 04 '18 at 21:32
  • @MT0 Can this be sorted at Java end..like checking first 1000 recordss and processing and then next 1000 records.Will it impact performance? – user9193174 Feb 05 '18 at 08:46
  • Why check for 1000 items and loop? Just pass all 948,732 items (or however many items you have) in a single collection and use a bind parameter to include it in the query. – MT0 Feb 05 '18 at 21:38

2 Answers2

0

Store all those values into a separate table, and then use it in a subquery as

update table set
  postcode = some_value
where id in (select values from a_separate_table)
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • this syntax dosent work with myibatis update table set postcode = some_value where id in (select values from a_separate_table) Only foreach loop works – user9193174 Feb 04 '18 at 14:53
  • What a shame; unfortunately, I don't know *myibatis* so - someone else will, hopefully, assist. – Littlefoot Feb 04 '18 at 14:57
0

ORA-01795: maximum number of expressions in a list is 1000

This can't be handled until, you strict total number of expression in IN clause to less than 1000.

Alternatively, you can split the query in multiple and your List as well and perform UNION to achieve same.

Ravi
  • 30,829
  • 42
  • 119
  • 173