Friday, October 21, 2011

Set UPDATE With COMMIT EVERY n ROWS

Just for fun, have a look at the endless Oracle arguments about how to update millions of rows in a table.

Then ask yourself, "Am I lucky or what?" to have the UPDATE COMMIT EVERY clause in SQL Anywhere.


UPDATE statement

Modifies existing rows in database tables.

Syntax 1

UPDATE [ row-limitation ] table-list ] 
   SET set-item, ...
   [ FROM table-expression [,...] ]
   [ WHERE search-condition ]
   [ ORDER BY expression [ ASC | DESC ] , ... ]
   [ OPTION( query-hint, ... ) ]
   [ COMMIT EVERY rowcount-expression ROWS ]

table-list : 
   table-name [,...]  

table-name : 
   [ owner.]table-name [ [ AS ] correlation-name ]
   | [ owner.]view-name [ [ AS ] correlation-name ]
   | derived-table

derived-table : 
   ( select-statement ) 
   [ AS ] correlation-name [ ( column-name [,... ] ) ]

table-expression : 
   A full table expression that can include joins. See FROM clause.

row-limitation :
   FIRST
   | TOP { ALL | limit-expression } [ START AT startat-expression ]

limit-expression : simple-expression

startat-expression : simple-expression

simple-expression :
   integer
   | variable
   | ( simple-expression )
   | ( simple-expression { + | - | * } simple-expression )

set-item :
   [ correlation-name.]column-name = { expression | DEFAULT }
   | [owner-name.]table-name.column-name = { expression | DEFAULT }
   | @variable-name = expression

query-hint :
   MATERIALIZED VIEW OPTIMIZATION option-value
   | FORCE OPTIMIZATION
   | FORCE NO OPTIMIZATION
   | option-name = option-value

table-name : 
   [ owner.]base-table-name
   | temporary-table-name
   | derived-table-name
   | [ owner.]view-name

option-name : identifier

option-value : hostvar (indicator allowed), string, identifier, or number

rowcount-expression : simple-expression


Yeah, dreams are great, aren't they?


Dilbert.com


3 comments:

r-pods said...

After first looking for that syntax on DCX 12.0.1 to no avail it took some time until I realized I had to scroll down to the dilbert strip to "get the picture". ´

Kinda april fools day experience ;-)

Breck Carter said...

Who knows? ...maybe it will be in Nagano, er, SQL Anywhere 16 :)

amrootha said...

Thank you for the info. It sounds pretty user friendly. I guess I’ll pick one up for fun. thank u


ASC Coding