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?

asked 19 Jan '11, 16:44

Cesar's gravatar image

Cesar
97315
accept rate: 0%

edited 15 Mar '13, 21:40

Mark%20Culp's gravatar image

Mark Culp
22.5k9129264

1

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?

(19 Jan '11, 23:42) carolstone
1

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

(20 Jan '11, 09:07) Volker Barth
Comment Text Removed
Comment Text Removed

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

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

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%20Barth's gravatar image

Volker Barth
29.5k291441646
accept rate: 32%

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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×95
×27
×23

question asked: 19 Jan '11, 16:44

question was seen: 859 times

last updated: 15 Mar '13, 21:40