CODEWITHSUNDEEP
sqlsql-servertransactions
Alexander Prokofyev
Alexander Prokofyev

Reputation: 34515

Partial UPDATE command

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?

Upvotes: 2

Views: 2721

Answers (4)

Evan
Evan

Reputation: 755

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.

Upvotes: 0

TFD
TFD

Reputation: 24524

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

Upvotes: 6

Matthew Farwell
Matthew Farwell

Reputation: 61705

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.

Upvotes: 3

Miserable Variable
Miserable Variable

Reputation: 28752

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.

Upvotes: 5

Related Questions