anuragmantri commented on code in PR #57725: URL: https://github.com/apache/spark/pull/57725#discussion_r3706979529
########## docs/sql-ref-syntax-dml-update.md: ########## @@ -0,0 +1,107 @@ +--- +layout: global +title: UPDATE +displayTitle: UPDATE +license: | + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. +--- + +### Description + +The `UPDATE` statement changes the values of columns in rows that satisfy an optional condition. +When no condition is specified, every row is updated. + +`UPDATE` is supported on tables backed by +[Data Source V2](sql-v2-data-sources.html#row-level-dml) connectors that support row-level +operations. + +### Syntax + +```sql +UPDATE table_identifier [ [ AS ] table_alias ] + [ WITH ( option_key = option_value [ , ... ] ) ] + SET column = expression [ , ... ] + [ WHERE boolean_expression ] +``` + +### Parameters + +* **table_identifier** + + Specifies the table to update, which may be optionally qualified with a database name. + + **Syntax:** `[ database_name. ] table_name` + +* **table_alias** + + Specifies an optional alias for the target table. The alias may be introduced with or without + the `AS` keyword. + +* **WITH ( option_key = option_value [ , ... ] )** + + Specifies dynamic table options for this `UPDATE` operation. These options are passed to the + data source connector when writing to the table. The supported options depend on the connector. + +* **SET column = expression [ , ... ]** + + Assigns a value to one or more columns. Each value may be an expression or `DEFAULT`. A nested + field may be targeted by using a qualified column name. Review Comment: The SET column = expression entry says a value may be an expression or DEFAULT, which is accurate, but doesn't call out that the expression can be an uncorrelated subquery over a completely different table (e.g. SET salary = (SELECT max(salary) FROM other_table)). Worth adding a sentence here. ```suggestion * **SET column = value [ , ... ]** Specifies the columns to update and the values to assign to them. Each `value` is an expression, typically referencing columns of the target table, but it may also be an uncorrelated subquery over other tables. A comma separates each assignment. ``` ########## docs/sql-ref-syntax-dml-update.md: ########## @@ -0,0 +1,107 @@ +--- +layout: global +title: UPDATE +displayTitle: UPDATE +license: | + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. +--- + +### Description + +The `UPDATE` statement changes the values of columns in rows that satisfy an optional condition. +When no condition is specified, every row is updated. + +`UPDATE` is supported on tables backed by +[Data Source V2](sql-v2-data-sources.html#row-level-dml) connectors that support row-level +operations. + +### Syntax + +```sql +UPDATE table_identifier [ [ AS ] table_alias ] + [ WITH ( option_key = option_value [ , ... ] ) ] + SET column = expression [ , ... ] + [ WHERE boolean_expression ] +``` + +### Parameters + +* **table_identifier** + + Specifies the table to update, which may be optionally qualified with a database name. + + **Syntax:** `[ database_name. ] table_name` + +* **table_alias** + + Specifies an optional alias for the target table. The alias may be introduced with or without + the `AS` keyword. + +* **WITH ( option_key = option_value [ , ... ] )** + + Specifies dynamic table options for this `UPDATE` operation. These options are passed to the + data source connector when writing to the table. The supported options depend on the connector. Review Comment: Both WITH (option_key = option_value) entries (UPDATE and DELETE FROM) describe the clause generically but don't mention: (a) that these options apply to this statement only, without changing the table's persistent configuration, (b) that a key that isn't a valid identifier needs backtick-quoting, and (c) that options the connector doesn't recognize are silently ignored. All three are worth stating explicitly so users know what to expect. ```suggestion * **WITH ( key = val [ , ... ] )** An optional list of dynamic table options passed to the Data Source V2 connector for this statement only. The options are surfaced to the connector's row-level write, allowing per-statement tuning (for example a write file size or an isolation level) without changing the table configuration. Keys and values are treated as strings; a key that is not a valid identifier can be quoted with backticks. Options that the connector does not recognize are ignored. ``` ########## docs/sql-ref-syntax-dml-update.md: ########## @@ -0,0 +1,107 @@ +--- +layout: global +title: UPDATE +displayTitle: UPDATE +license: | + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. +--- + +### Description + +The `UPDATE` statement changes the values of columns in rows that satisfy an optional condition. +When no condition is specified, every row is updated. + +`UPDATE` is supported on tables backed by +[Data Source V2](sql-v2-data-sources.html#row-level-dml) connectors that support row-level +operations. + +### Syntax + +```sql +UPDATE table_identifier [ [ AS ] table_alias ] + [ WITH ( option_key = option_value [ , ... ] ) ] + SET column = expression [ , ... ] + [ WHERE boolean_expression ] +``` + +### Parameters + +* **table_identifier** + + Specifies the table to update, which may be optionally qualified with a database name. + + **Syntax:** `[ database_name. ] table_name` + +* **table_alias** + + Specifies an optional alias for the target table. The alias may be introduced with or without + the `AS` keyword. + +* **WITH ( option_key = option_value [ , ... ] )** + + Specifies dynamic table options for this `UPDATE` operation. These options are passed to the + data source connector when writing to the table. The supported options depend on the connector. + +* **SET column = expression [ , ... ]** + + Assigns a value to one or more columns. Each value may be an expression or `DEFAULT`. A nested + field may be targeted by using a qualified column name. + +* **WHERE boolean_expression** + + Specifies an optional condition that selects the rows to update. If the `WHERE` clause is + omitted, all rows are updated. + +### Examples + +The following examples assume that an `employees` table has already been created and populated. + +#### Update Rows Matching a Condition + +```sql +UPDATE employees + SET salary = salary + 1000 + WHERE department = 'Engineering'; +``` + +#### Update Multiple Columns Using an Alias + +```sql +UPDATE employees AS e + SET e.salary = e.salary * 1.05, e.status = 'reviewed' + WHERE e.department = 'Sales'; +``` + +#### Update Using Dynamic Table Options + +```sql +-- Option names and values are specific to the table's data source connector. +UPDATE employees WITH (`write.split-size` = 10) + SET status = 'inactive' + WHERE last_active_date < DATE '2025-01-01'; +``` + +#### Update All Rows + +```sql +UPDATE employees SET status = 'active'; +``` + +### Related Statements + +* [DELETE FROM statement](sql-ref-syntax-dml-delete-from.html) +* [MERGE INTO statement](sql-ref-syntax-dml-merge-into.html) +* [SELECT statement](sql-ref-syntax-qry-select.html) Review Comment: For consistency, link to INSERT statement as well? -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
