Partial UPDATE command

Question

I want to execute UPDATE command over a group of records, but it fails, because some records after the update would violate an table constraint. Is it possible to update only suitable records somehow?

Answer 1

The update statement is itself atomic, it will either all succeed or all fail. You need to modify the SQL to update only the rows where the constraint will not be violated.

Answer 2

You need to add the constraints to the WHERE clause.

Either that or run the update seperately for each line, but this doesn't seem like a good idea at all.

Answer 3

Behaviour of Update as all or nothing is by design

You can add Where clauses to your Update command to check all the constraints but this results in code duplication, not a good thing

How about doing the update one row at a time using a Cursor or CLR loop?

If this is part of a multilayer application, then this is where your business objects need to know about your SQL constraints via meta data or schema interrogation so that data validation can be done before it hits the database

The CSLA framework has some goodies for this

Answer 4

To add to some of the answers already posted: you may need to use a subquery in the where clause. Like (in psuedo-code)

update mytable
set myfield = myval
where not exists (select from some table that would tell you if a constraint is violated for this row)

If you post code, I might be able to help you more.