Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Comment: | Documentation for UPDATE FROM. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA3-256: |
ba3810c5159c7da050477ebf3816d57f |
User & Date: | drh 2020-07-18 16:35:00.452 |
2020-07-23
| ||
09:32 | Add documentation for partial integrity_check. (check-in: 95ab7854dd user: drh tags: trunk) | |
2020-07-18
| ||
16:35 | Documentation for UPDATE FROM. (check-in: ba3810c515 user: drh tags: trunk) | |
2020-07-17
| ||
17:35 | Revise syntax diagrams to include the FROM clause on UPDATE statements. (Closed-Leaf check-in: bd9cdee968 user: drh tags: update-from) | |
2020-07-15
| ||
15:03 | Tweaks to the floatingpoint.html page. (check-in: dcd828be81 user: drh tags: trunk) | |
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
︙ | ︙ | |||
220 221 222 223 224 225 226 | ( /initial-select {or UNION {line UNION ALL}} /recursive-select ) } common-table-expression { line /table-name {optx ( {loop /column-name ,} )} AS ( select-stmt ) } delete-stmt { stack | > > | > | | 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 | ( /initial-select {or UNION {line UNION ALL}} /recursive-select ) } common-table-expression { line /table-name {optx ( {loop /column-name ,} )} AS ( select-stmt ) } delete-stmt { stack {optx {line WITH {opt RECURSIVE} {loop common-table-expression ,}}} {line DELETE FROM qualified-table-name} {optx WHERE expr} } delete-stmt-limited { stack {optx {line WITH {opt RECURSIVE} {loop common-table-expression ,}}} {line DELETE FROM qualified-table-name} {optx WHERE expr} {optx {stack {optx ORDER BY {loop ordering-term ,}} {line LIMIT expr {optx {or OFFSET ,} expr}} } } |
︙ | ︙ | |||
308 309 310 311 312 313 314 | {line /decimal-point {loop /digit nil}} } {opt E {or nil + -} {loop /digit nil}}} {line \"0x\" {loop /hexdigit nil}} } insert-stmt { stack | > | | 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 | {line /decimal-point {loop /digit nil}} } {opt E {or nil + -} {loop /digit nil}}} {line \"0x\" {loop /hexdigit nil}} } insert-stmt { stack {optx {line WITH {opt RECURSIVE} {loop common-table-expression ,}}} {line {or INSERT REPLACE {line INSERT OR REPLACE} {line INSERT OR ROLLBACK} {line INSERT OR ABORT} {line INSERT OR FAIL} |
︙ | ︙ | |||
476 477 478 479 480 481 482 | line expr {opt COLLATE /collation-name} {or nil ASC DESC} {or nil {line NULLS FIRST} {line NULLS LAST}} } compound-operator { or UNION {line UNION ALL} INTERSECT EXCEPT } update-stmt { | | > | | | | | | > | > | | | | | | > | | 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 | line expr {opt COLLATE /collation-name} {or nil ASC DESC} {or nil {line NULLS FIRST} {line NULLS LAST}} } compound-operator { or UNION {line UNION ALL} INTERSECT EXCEPT } update-stmt { stack {optx {line WITH {opt RECURSIVE} {loop common-table-expression ,}}} {line UPDATE {or {} {line OR ROLLBACK} {line OR ABORT} {line OR REPLACE} {line OR FAIL} {line OR IGNORE}} qualified-table-name} {line SET {loop {line {or /column-name column-name-list} = expr} ,}} {optx FROM {or {loop table-or-subquery ,} join-clause}} {optx WHERE expr} } column-name-list {line ( {loop /column-name ,} )} update-stmt-limited { stack {opt {line WITH {opt RECURSIVE} {loop common-table-expression ,}}} {line UPDATE {or {} {line OR ROLLBACK} {line OR ABORT} {line OR REPLACE} {line OR FAIL} {line OR IGNORE}} qualified-table-name} {line SET {loop {line {or /column-name column-name-list} = expr} ,}} {optx FROM {or {loop table-or-subquery ,} join-clause}} {optx WHERE expr} {optx {stack {optx ORDER BY {loop ordering-term ,}} {line LIMIT expr {optx {or OFFSET ,} expr}} } } } |
︙ | ︙ |
︙ | ︙ | |||
146 147 148 149 150 151 152 | } set id1 [.c create text 0 0 -anchor c -text $txt -font $font -tags $tag] foreach {x0 y0 x1 y1} [.c bbox $id1] break set h [expr {$y1-$y0+2}] set rad [expr {($h+1)/2}] set top [expr {$y0-2}] set btm [expr {$y1}] | | | 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 | } set id1 [.c create text 0 0 -anchor c -text $txt -font $font -tags $tag] foreach {x0 y0 x1 y1} [.c bbox $id1] break set h [expr {$y1-$y0+2}] set rad [expr {($h+1)/2}] set top [expr {$y0-2}] set btm [expr {$y1}] set fudge [expr {int(3*$istoken + [string length $txt]*1.0)}] #puts "fudge($txt)=$fudge" set left [expr {$x0+$fudge}] set right [expr {$x1-$fudge}] if {$left>$right} { set left [expr {($x0+$x1)/2}] set right $left } |
︙ | ︙ | |||
264 265 266 267 268 269 270 | incr tagcnt set tag x$tagcnt set sep [expr {$VSEP*2}] set btm 0 set n [llength $lx] set i 0 | | | | > > > > > | > > > > > > | 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 | incr tagcnt set tag x$tagcnt set sep [expr {$VSEP*2}] set btm 0 set n [llength $lx] set i 0 set next_bypass_y -1 foreach term $lx { set bypass_y $next_bypass_y if {[llength $term]>1 && $indent>=0 && ([lindex $term 0]=="opt" || [lindex $term 0]=="optx")} { set bypass 1 set term "line [lrange $term 1 end]" } else { set bypass 0 set next_bypass_y -1 } set m [draw_diagram $term] foreach {t exx exy} $m break foreach {tx0 ty0 tx1 ty1} [.c bbox $t] break if {$i==0} { set btm $ty1 set exit_y $exy set exit_x $exx set dx [expr {$sep*2}] .c move $t $dx 0 set exit_x [expr {$exx+$dx}] .c create line 0 0 $dx 0 -width 2 -tags $tag if {$bypass} {set next_bypass_y 0; set bypass_x $RADIUS} } else { set enter_y [expr {$btm - $ty0 + $sep*2 + 2}] if {$bypass} {set next_bypass_y [expr {$enter_y - $RADIUS}]} if {$indent<0} { set w [expr {$tx1 - $tx0}] set enter_x [expr {$exit_x - $w + $sep*$indent}] set ex2 [expr {$sep*2 - $indent}] if {$ex2>$enter_x} {set enter_x $ex2} } else { set enter_x [expr {$sep*2 + $indent}] } set back_y [expr {$btm + $sep + 1}] if {$bypass_y>=0} { if {$bypass_y==0} { .c create arc [expr {$bypass_x-2*$RADIUS}] 0 \ $bypass_x [expr {2*$RADIUS}] \ -width 2 -start 0 -extent 90 -style arc -tags $tag set bypass_y [expr {$bypass_y+$RADIUS}] } set mid_y [expr {($bypass_y+$RADIUS+$back_y)/2}] .c create line $bypass_x $bypass_y $bypass_x $mid_y \ -width 2 -tags $tag -arrow last .c create line $bypass_x $mid_y $bypass_x [expr {$back_y+$RADIUS}] \ -tags $tag -width 2 } .c move $t $enter_x $enter_y |
︙ | ︙ |
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
1 2 3 4 5 6 7 8 9 10 | set syntax_linkage(aggregate-function-invocation) {{expr filter-clause} {}} set syntax_linkage(alter-table-stmt) {column-def sql-stmt} set syntax_linkage(analyze-stmt) {{} sql-stmt} set syntax_linkage(attach-stmt) {expr sql-stmt} set syntax_linkage(begin-stmt) {{} sql-stmt} set syntax_linkage(column-constraint) {{conflict-clause expr foreign-key-clause literal-value signed-number} column-def} set syntax_linkage(column-def) {{column-constraint type-name} {alter-table-stmt create-table-stmt}} set syntax_linkage(column-name-list) {{} {update-stmt update-stmt-limited upsert-clause}} set syntax_linkage(comment-syntax) {{} {}} set syntax_linkage(commit-stmt) {{} sql-stmt} | | | | | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 | set syntax_linkage(aggregate-function-invocation) {{expr filter-clause} {}} set syntax_linkage(alter-table-stmt) {column-def sql-stmt} set syntax_linkage(analyze-stmt) {{} sql-stmt} set syntax_linkage(attach-stmt) {expr sql-stmt} set syntax_linkage(begin-stmt) {{} sql-stmt} set syntax_linkage(column-constraint) {{conflict-clause expr foreign-key-clause literal-value signed-number} column-def} set syntax_linkage(column-def) {{column-constraint type-name} {alter-table-stmt create-table-stmt}} set syntax_linkage(column-name-list) {{} {update-stmt update-stmt-limited upsert-clause}} set syntax_linkage(comment-syntax) {{} {}} set syntax_linkage(commit-stmt) {{} sql-stmt} set syntax_linkage(common-table-expression) {select-stmt {compound-select-stmt delete-stmt delete-stmt-limited factored-select-stmt insert-stmt select-stmt simple-select-stmt update-stmt update-stmt-limited}} set syntax_linkage(compound-operator) {{} {factored-select-stmt select-stmt}} set syntax_linkage(compound-select-stmt) {{common-table-expression expr ordering-term select-core} {}} set syntax_linkage(conflict-clause) {{} {column-constraint table-constraint}} set syntax_linkage(create-index-stmt) {{expr indexed-column} sql-stmt} set syntax_linkage(create-table-stmt) {{column-def select-stmt table-constraint} sql-stmt} set syntax_linkage(create-trigger-stmt) {{delete-stmt expr insert-stmt select-stmt update-stmt} sql-stmt} set syntax_linkage(create-view-stmt) {select-stmt sql-stmt} set syntax_linkage(create-virtual-table-stmt) {{} sql-stmt} set syntax_linkage(cte-table-name) {{} {recursive-cte with-clause}} set syntax_linkage(delete-stmt) {{common-table-expression expr qualified-table-name} {create-trigger-stmt sql-stmt}} set syntax_linkage(delete-stmt-limited) {{common-table-expression expr ordering-term qualified-table-name} sql-stmt} set syntax_linkage(detach-stmt) {{} sql-stmt} set syntax_linkage(drop-index-stmt) {{} sql-stmt} set syntax_linkage(drop-table-stmt) {{} sql-stmt} set syntax_linkage(drop-trigger-stmt) {{} sql-stmt} set syntax_linkage(drop-view-stmt) {{} sql-stmt} set syntax_linkage(expr) {{filter-clause literal-value over-clause raise-function select-stmt type-name} {aggregate-function-invocation attach-stmt column-constraint compound-select-stmt create-index-stmt create-trigger-stmt delete-stmt delete-stmt-limited factored-select-stmt filter-clause frame-spec indexed-column insert-stmt join-constraint ordering-term over-clause result-column select-core select-stmt simple-function-invocation simple-select-stmt table-constraint table-or-subquery update-stmt update-stmt-limited upsert-clause window-defn window-function-invocation}} set syntax_linkage(factored-select-stmt) {{common-table-expression compound-operator expr ordering-term select-core} {}} set syntax_linkage(filter-clause) {expr {aggregate-function-invocation expr window-function-invocation}} set syntax_linkage(foreign-key-clause) {{} {column-constraint table-constraint}} set syntax_linkage(frame-spec) {expr {over-clause window-defn}} set syntax_linkage(indexed-column) {expr {create-index-stmt table-constraint upsert-clause}} set syntax_linkage(insert-stmt) {{common-table-expression expr select-stmt upsert-clause} {create-trigger-stmt sql-stmt}} set syntax_linkage(join-clause) {{join-constraint join-operator table-or-subquery} {select-core select-stmt table-or-subquery update-stmt update-stmt-limited}} set syntax_linkage(join-constraint) {expr join-clause} set syntax_linkage(join-operator) {{} join-clause} set syntax_linkage(literal-value) {{} {column-constraint expr}} set syntax_linkage(numeric-literal) {{} {}} set syntax_linkage(ordering-term) {expr {compound-select-stmt delete-stmt-limited factored-select-stmt over-clause select-stmt simple-select-stmt update-stmt-limited window-defn}} set syntax_linkage(over-clause) {{expr frame-spec ordering-term} expr} set syntax_linkage(pragma-stmt) {pragma-value sql-stmt} |
︙ | ︙ | |||
53 54 55 56 57 58 59 | set syntax_linkage(select-stmt) {{common-table-expression compound-operator expr join-clause ordering-term result-column table-or-subquery window-defn} {common-table-expression create-table-stmt create-trigger-stmt create-view-stmt expr insert-stmt sql-stmt table-or-subquery with-clause}} set syntax_linkage(signed-number) {{} {column-constraint pragma-value type-name}} set syntax_linkage(simple-function-invocation) {expr {}} set syntax_linkage(simple-select-stmt) {{common-table-expression expr ordering-term select-core} {}} set syntax_linkage(sql-stmt) {{alter-table-stmt analyze-stmt attach-stmt begin-stmt commit-stmt create-index-stmt create-table-stmt create-trigger-stmt create-view-stmt create-virtual-table-stmt delete-stmt delete-stmt-limited detach-stmt drop-index-stmt drop-table-stmt drop-trigger-stmt drop-view-stmt insert-stmt pragma-stmt reindex-stmt release-stmt rollback-stmt savepoint-stmt select-stmt update-stmt update-stmt-limited vacuum-stmt} sql-stmt-list} set syntax_linkage(sql-stmt-list) {sql-stmt {}} set syntax_linkage(table-constraint) {{conflict-clause expr foreign-key-clause indexed-column} create-table-stmt} | | | | | | 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 | set syntax_linkage(select-stmt) {{common-table-expression compound-operator expr join-clause ordering-term result-column table-or-subquery window-defn} {common-table-expression create-table-stmt create-trigger-stmt create-view-stmt expr insert-stmt sql-stmt table-or-subquery with-clause}} set syntax_linkage(signed-number) {{} {column-constraint pragma-value type-name}} set syntax_linkage(simple-function-invocation) {expr {}} set syntax_linkage(simple-select-stmt) {{common-table-expression expr ordering-term select-core} {}} set syntax_linkage(sql-stmt) {{alter-table-stmt analyze-stmt attach-stmt begin-stmt commit-stmt create-index-stmt create-table-stmt create-trigger-stmt create-view-stmt create-virtual-table-stmt delete-stmt delete-stmt-limited detach-stmt drop-index-stmt drop-table-stmt drop-trigger-stmt drop-view-stmt insert-stmt pragma-stmt reindex-stmt release-stmt rollback-stmt savepoint-stmt select-stmt update-stmt update-stmt-limited vacuum-stmt} sql-stmt-list} set syntax_linkage(sql-stmt-list) {sql-stmt {}} set syntax_linkage(table-constraint) {{conflict-clause expr foreign-key-clause indexed-column} create-table-stmt} set syntax_linkage(table-or-subquery) {{expr join-clause select-stmt} {join-clause select-core select-stmt update-stmt update-stmt-limited}} set syntax_linkage(type-name) {signed-number {column-def expr}} set syntax_linkage(update-stmt) {{column-name-list common-table-expression expr join-clause qualified-table-name table-or-subquery} {create-trigger-stmt sql-stmt}} set syntax_linkage(update-stmt-limited) {{column-name-list common-table-expression expr join-clause ordering-term qualified-table-name table-or-subquery} sql-stmt} set syntax_linkage(upsert-clause) {{column-name-list expr indexed-column} insert-stmt} set syntax_linkage(vacuum-stmt) {{} sql-stmt} set syntax_linkage(window-defn) {{expr frame-spec ordering-term} {select-core select-stmt window-function-invocation}} set syntax_linkage(window-function-invocation) {{expr filter-clause window-defn} {}} set syntax_linkage(with-clause) {{cte-table-name select-stmt} {}} set syntax_order {sql-stmt-list sql-stmt alter-table-stmt analyze-stmt attach-stmt begin-stmt commit-stmt rollback-stmt savepoint-stmt release-stmt create-index-stmt indexed-column create-table-stmt column-def type-name column-constraint signed-number table-constraint foreign-key-clause conflict-clause create-trigger-stmt create-view-stmt create-virtual-table-stmt with-clause cte-table-name recursive-cte common-table-expression delete-stmt delete-stmt-limited detach-stmt drop-index-stmt drop-table-stmt drop-trigger-stmt drop-view-stmt expr raise-function literal-value numeric-literal insert-stmt upsert-clause pragma-stmt pragma-value reindex-stmt select-stmt join-clause select-core factored-select-stmt simple-select-stmt compound-select-stmt table-or-subquery result-column join-operator join-constraint ordering-term compound-operator update-stmt column-name-list update-stmt-limited qualified-table-name vacuum-stmt comment-syntax filter-clause window-defn over-clause frame-spec simple-function-invocation aggregate-function-invocation window-function-invocation} |
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
︙ | ︙ | |||
21 22 23 24 25 26 27 28 29 30 31 32 33 34 | if {[regexp {\(([0-9.]+)\)} $date all vers]} { set xrefChng($vers) $nChng } incr nChng } chng {2020-08-22 (3.33.0)} { <li> Added the [decimal extension] for doing arbitrary-precision decimal arithmetic. <li> Enhancments to the [ieee754 extension] for working with IEEE 754 binary64 numbers. <li> [CLI] enhancements: <ol type="a"> <li> Added four new [.mode|output modes]: "box", "json", "markdown", and "table". <li> The "column" output mode automatically expands columns to | > | 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | if {[regexp {\(([0-9.]+)\)} $date all vers]} { set xrefChng($vers) $nChng } incr nChng } chng {2020-08-22 (3.33.0)} { <li> Support for [UPDATE FROM] following the PostgreSQL syntax. <li> Added the [decimal extension] for doing arbitrary-precision decimal arithmetic. <li> Enhancments to the [ieee754 extension] for working with IEEE 754 binary64 numbers. <li> [CLI] enhancements: <ol type="a"> <li> Added four new [.mode|output modes]: "box", "json", "markdown", and "table". <li> The "column" output mode automatically expands columns to |
︙ | ︙ |
︙ | ︙ | |||
67 68 69 70 71 72 73 74 75 76 77 78 79 80 | <li><p>^The INDEXED BY and NOT INDEXED clauses are not allowed on UPDATE statements within triggers.</p> <li><p>^The LIMIT and ORDER BY clauses for UPDATE are unsupported within triggers, regardless of the compilation options used to build SQLite. </ul> <h2>Optional LIMIT and ORDER BY Clauses</h2> <p>^(If SQLite is built with the [SQLITE_ENABLE_UPDATE_DELETE_LIMIT] compile-time option then the syntax of the UPDATE statement is extended with optional ORDER BY and LIMIT clauses)^ as follows:</p> | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 | <li><p>^The INDEXED BY and NOT INDEXED clauses are not allowed on UPDATE statements within triggers.</p> <li><p>^The LIMIT and ORDER BY clauses for UPDATE are unsupported within triggers, regardless of the compilation options used to build SQLite. </ul> <tcl>hd_fragment upfrom {UPDATE FROM}</tcl> <h2>UPDATE FROM</h2> <p>The UPDATE-FROM idea is an extension to SQL that allows an UPDATE statement to be driven by other tables in the database. The "target" table is the specific table that is being updated. With UPDATE-FROM you can join the target table against other tables in the database in order to help compute which rows need updating and what the new values should be on those rows. UPDATE-FROM is supported beginning in SQLite version 3.33.0 ([dateof:3.33.0]). <p>Other relation database engines also implement UPDATE-FROM, but because the construct is not part of the SQL standards, each product implements UPDATE-FROM differently. The SQLite implementation strives to be compatible with PostgreSQL. The SQL Server and MySQL implementations of the same idea work a little differently. <p>As an example of how UPDATE-FROM can be useful, suppose you have a point-of-sale application that accumulates purchases in the SALES table. At the end of the day, you want to adjust the INVENTORY table according to the daily sales. To do this, you can run an UPDATE against the INVENTORY table that adjusts the quantity by the aggregated sales for the day. The statement would look like this: <codeblock> UPDATE inventory SET quantity = quantity - daily.amt FROM (SELECT sum(quantity) AS amt, itemId FROM sales GROUP BY 2) AS daily WHERE inventory.itemId = daily.itemId; </codeblock> <p> The subquery in the FROM clause computes the amount by which the inventory should be reduced for each itemId. That subquery is joined against the inventory table and the quantity of each affected inventory row is reduced by the appropriate amount. <p> The target table is not included in the FROM clause, unless the intent is to do a self-join against the target table. In the event of a self-join, the table in the FROM clause must be aliased to a different name than the target table. <p> If the join between the target table and the FROM clause results in multiple output rows for the same target table row, then only one of those output rows is used for updating the target table. The output row selected is arbitrary and might change from one release of SQLite to the next, or from one run to the next. <h3>UPDATE FROM in other SQL database engines</h3> <p>SQL Server also supports UPDATE FROM, but in SQL Server the target table must be included in the FROM clause. In other words, the target table is named twice in the statement. With SQL Server, the inventory adjustment statement demonstrated above would be written like this: <codeblock> UPDATE inventory SET quantity = quantity - daily.amt FROM inventory, (SELECT sum(quantity) AS amt, itemId FROM sales GROUP BY 2) AS daily WHERE inventory.itemId = daily.itemId; </codeblock> <p>MySQL supports the UPDATE FROM idea, but it does so without using a FROM clause. Instead, the complete join specification is given in between the UPDATE and SET keywords. The equivalent MySQL statement would be like this: <codeblock> UPDATE inventory JOIN (SELECT sum(quantity) AS amt, itemId FROM sales GROUP BY 2) AS daily USING( itemId ) SET inventory.quantity = inventory.quantity - daily.amt; </codeblock> <p>The MySQL UPDATE statement does not have just one target table like other systems. Any of the tables that participate in the join can be modified in the SET clause. The MySQL UPDATE syntax allows you to update multiple tables at once! <h2>Optional LIMIT and ORDER BY Clauses</h2> <p>^(If SQLite is built with the [SQLITE_ENABLE_UPDATE_DELETE_LIMIT] compile-time option then the syntax of the UPDATE statement is extended with optional ORDER BY and LIMIT clauses)^ as follows:</p> |
︙ | ︙ |