SAP SQL Anywhere

Question

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?

Accepted Answer

4

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.

permanent link

answered 19 Jan '11, 17:09

Volker Barth
39.7k●357●545●815
accept rate: 34%

Good to know about this issue of atomic operation!

(19 Jan '11, 20:23) Ismael

4

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.

(19 Jan '11, 21:22) Breck Carter

@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.

(20 Jan '11, 09:19) Volker Barth

flat view

SAP SQL Anywhere

What is the best solution for this UPDATE

Follow this question

Related questions