CODEWITHSUNDEEP
sqlpervasive
seb
seb

Reputation: 161

is there a way to limit the amount of rows that get affected by an UPDATE statement?

just wondering if there is any way for me to limit the amount of rows which will be affected by an UPDATE statement....I was thinking of using something like a LIMIT statement but there's no such thing in PERVASIVE, any tips?

thank you

Upvotes: 0

Views: 189

Answers (3)

unpythonic
unpythonic

Reputation: 4070

If you're just being careful, why not just count the rows you would update?

select count(*) from MYTABLE WHERE ...some condition...;

If the count is sufficiently low, this is practically cut & paste to the update:

update MYTABLE set col1 = val1, ... WHERE ...some condition...;

Put into your scripting language, pl/sql, or stored procedure as is appropriate.

Upvotes: 0

Andrew Lazarus
Andrew Lazarus

Reputation: 19362

The TOP 100 idea can work but depending on your data, it may skew either testing the correctness or performance. It can also be hard to write. I think adding a RANDOM-based clause works a little better.

UPDATE t SET c = blah 
  WHERE (/* whatever bunch of stuff */) 
  AND Random()<0.01 /* 1 percent */

This works if your DB's random gives a value between zero and one, and can be modified for different random functions and densities.

Upvotes: 1

Narnian
Narnian

Reputation: 3908

It seems that this would work...

UPDATE TableName SET ColumnName = value WHERE ID IN (SELECT TOP 100 ID FROM TableName WHERE CONDITION)

Make TOP 100 whatever your "limit" is. Then, just update the WHERE clause appropriately.

Upvotes: 1

Related Questions