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
97●3●1●5
accept rate:
0%
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?
@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.
@Carol: A related topic w.r.t. UPDATE NO-OPs and replication: http://sqlanywhere-forum.sap.com/questions/1056.