What is the best solution for this UPDATE

[Former Member]

Hi, what is the best way for this SQL?

A)

update tableName set
FieldA = (if FieldA = 1301 then null else FieldA endif), 
FieldB = (if FieldB = 1301 then null else FieldB endif)
where Id = 707;

or

B)

update tableName set FieldA = null where Id= 707 and FieldA = 1301;
update tableName set FieldB = null where Id= 707 and FieldB = 1301;

In Model "A" I only have one SQL that works and solves the problem, and model "B" I have two SQL's, that do the same thing as the "A" model but more readable.

What would be the most appropriate model to use?

[Former Member]

Additional question for the experts here - would syntax A end up touching every row in the table with an Id of 707, updating FieldA to either null or itself and FieldB to null or itself. Therefore, wouldn't this have an impact on triggers and replication and calculated fields and ... important stuff I'm not thinking of?

[VolkerBarth]

@Carol: A great question on its own:) - From the docs on the UPDATE statement: "If the new value does not differ from the old value, no change is made to the data. However, BEFORE UPDATE triggers fire any time an UPDATE occurs on a row, whether the new value differs from the old value. AFTER UPDATE triggers fire only if the new value is different from the old value." - AFAIK a NO-OP UPDATE does not impact replication as it is simply not logged in the transaction log. But I don't know the impact on calculated fields.

[VolkerBarth]

@Carol: A related topic w.r.t. UPDATE NO-OPs and replication: http://sqlanywhere-forum.sap.com/questions/1056.

[VolkerBarth · Accepted solution]

Of course that's a subjective choice but I'd suggest B) because it's more comprehensible.

Note however, that both models are not truly semantically equivalent because the contents of tableName could change between the first and second statement of B) - e.g. by different connections. In contrast, model A) uses one atomic statement.

[Former Member · Accepted solution]

Good to know about this issue of atomic operation!

[Breck_Carter · Accepted solution]

If the first UPDATE in option B) actually changes the row, then the resulting row lock prevents any other connection from changing the row until the first connection does a COMMIT or ROLLBACK. The argument still holds if Id is not actually unique, the words simply become plural: rows and row locks.

[VolkerBarth · Accepted solution]

@Breck: Thanks for pointing that out - I tend to forget that data changes lead to row locks independent of the chosen isolation level:( So my point would only apply if each statement is done within its own transaction. But then again, it should be stated that most APIs (except ESQL) do use autocommit mode by default, which does run each statement in its own transaction. As a consequence, then the "not-atomic operation problem" would exist.