szehon-ho commented on code in PR #57725: URL: https://github.com/apache/spark/pull/57725#discussion_r3708060214
########## docs/sql-ref-syntax-dml-update.md: ########## @@ -0,0 +1,113 @@ +--- +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 ( key = value [ , ... ] ) ] + SET column = value [ , ... ] + [ 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 ( key = value [ , ... ] )** + + Specifies an optional list of dynamic table options passed to the Data Source V2 connector for + this statement only. The options allow per-statement tuning without changing the table's + persistent configuration. Keys and values are treated as strings; a key that is not a valid + identifier can be quoted with backticks. Spark passes options through without validating their + names, and connectors may ignore options they do not recognize. + +* **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 `DEFAULT` or an + uncorrelated scalar subquery over another table. A comma separates each assignment. 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. Review Comment: The neighbouring DML pages make their examples runnable end to end. `MERGE INTO` opens its Examples section with the initial `SELECT * FROM target` / `SELECT * FROM source` state and shows the resulting table under each example; `INSERT TABLE` creates `students` inline and shows the output after each insert. Here there is no schema for `employees` and no results, so a reader cannot tell what `status`, `department`, `last_active_date`, or `salary` are, or check that they got the expected outcome. Could you add a small `CREATE TABLE` plus initial `SELECT *` block here, and a result under each example? ########## docs/sql-ref-syntax-dml-update.md: ########## @@ -0,0 +1,113 @@ +--- +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 ( key = value [ , ... ] ) ] + SET column = value [ , ... ] + [ 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 ( key = value [ , ... ] )** + + Specifies an optional list of dynamic table options passed to the Data Source V2 connector for + this statement only. The options allow per-statement tuning without changing the table's + persistent configuration. Keys and values are treated as strings; a key that is not a valid + identifier can be quoted with backticks. Spark passes options through without validating their + names, and connectors may ignore options they do not recognize. Review Comment: Two minor things here. First, consistency: #57724 documents this same `WITH (...)` clause on the `INSERT`, `MERGE INTO`, and `SELECT` pages in two sentences ("Specifies dynamic table options for this `INSERT` operation. These options are passed to the data source connector when writing to the table. The supported options depend on the connector."). With both PRs landing around the same time, readers will hit the same clause described at two different levels of detail depending on which page they land on. Worth either syncing the wording across all five pages, or describing it once -- the Row-Level DML section of `sql-v2-data-sources.md` that this page already links to is a plausible home -- and giving each page the one-line version plus a link. Second, if the long form stays, it can lose some weight. The second sentence restates "for this statement only" from the first, and the last sentence makes the same point twice ("passes options through without validating their names" / "connectors may ignore options they do not recognize"). The backtick note is the part that really earns its place, since `write.split-size` in the example below is a parse error unquoted (`propertyKey` is `identifier (DOT identifier)* | stringLit`). One thing that might be worth having in place of "keys and values are treated as strings": keys are case-insensitive, since the options end up in a `CaseInsensitiveStringMap` (`AstBuilder.resolveOptions`). That is behavior a user cannot guess. ########## docs/sql-ref-syntax-dml-delete-from.md: ########## @@ -0,0 +1,101 @@ +--- +layout: global +title: DELETE FROM +displayTitle: DELETE FROM +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 `DELETE FROM` statement removes rows from a table that satisfy an optional condition. When no +condition is specified, every row is removed. + +`DELETE FROM` is supported on tables backed by +[Data Source V2](sql-v2-data-sources.html#row-level-dml) connectors that support delete operations. + +### Syntax + +```sql +DELETE FROM table_identifier [ [ AS ] table_alias ] + [ WITH ( key = value [ , ... ] ) ] + [ WHERE boolean_expression ] +``` + +### Parameters + +* **table_identifier** + + Specifies the table from which rows are deleted. The table name 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 ( key = value [ , ... ] )** + + Specifies an optional list of dynamic table options passed to the Data Source V2 connector for + this statement only. The options allow per-statement tuning without changing the table's + persistent configuration. Keys and values are treated as strings; a key that is not a valid + identifier can be quoted with backticks. Spark passes options through without validating their + names, and connectors may ignore options they do not recognize. Review Comment: Same bullet as on the `UPDATE` page -- whatever wording you settle on there, please keep these two in sync (and ideally with the `INSERT` / `MERGE INTO` / `SELECT` pages in #57724). ########## docs/sql-ref-syntax-dml-update.md: ########## @@ -0,0 +1,113 @@ +--- +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 ( key = value [ , ... ] ) ] + SET column = value [ , ... ] + [ 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 ( key = value [ , ... ] )** + + Specifies an optional list of dynamic table options passed to the Data Source V2 connector for + this statement only. The options allow per-statement tuning without changing the table's + persistent configuration. Keys and values are treated as strings; a key that is not a valid + identifier can be quoted with backticks. Spark passes options through without validating their + names, and connectors may ignore options they do not recognize. + +* **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 `DEFAULT` or an + uncorrelated scalar subquery over another table. A comma separates each assignment. 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'; Review Comment: Minor, and it mostly goes away once the example table has a stated schema: if `salary` is an `INT`, then `salary * 1.05` is a `DOUBLE`, and ANSI store assignment permits numeric narrowing (`Cast.canANSIStoreAssign` allows any `NumericType` -> `NumericType`), so the 5% raise is silently truncated back to an `INT`. Giving `salary` a `DECIMAL` or `DOUBLE` type in the setup, or using an increment that stays integral, avoids demonstrating truncation by accident. ########## docs/sql-ref-syntax.md: ########## @@ -48,9 +48,11 @@ Data Definition Statements are used to create or modify the structure of databas Data Manipulation Statements are used to add, change, or delete data. Spark SQL supports the following Data Manipulation Statements: + * [DELETE FROM](sql-ref-syntax-dml-delete-from.html) * [INSERT TABLE](sql-ref-syntax-dml-insert-table.html) * [INSERT OVERWRITE DIRECTORY](sql-ref-syntax-dml-insert-overwrite-directory.html) * [MERGE INTO](sql-ref-syntax-dml-merge-into.html) + * [UPDATE](sql-ref-syntax-dml-update.html) Review Comment: Nit on placement. The DDL list above is strictly alphabetical, while this DML list is not (`INSERT TABLE` precedes `INSERT OVERWRITE DIRECTORY`, and `LOAD` trails `MERGE INTO`), so putting `DELETE FROM` first and `UPDATE` between `MERGE INTO` and `LOAD` ends up arbitrary either way. I would either append `UPDATE` after `LOAD` or alphabetize the whole list. ########## docs/sql-ref-syntax-dml-delete-from.md: ########## @@ -0,0 +1,101 @@ +--- +layout: global +title: DELETE FROM +displayTitle: DELETE FROM +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 `DELETE FROM` statement removes rows from a table that satisfy an optional condition. When no +condition is specified, every row is removed. + +`DELETE FROM` is supported on tables backed by +[Data Source V2](sql-v2-data-sources.html#row-level-dml) connectors that support delete operations. + +### Syntax + +```sql +DELETE FROM table_identifier [ [ AS ] table_alias ] + [ WITH ( key = value [ , ... ] ) ] + [ WHERE boolean_expression ] +``` + +### Parameters + +* **table_identifier** + + Specifies the table from which rows are deleted. The table name 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 ( key = value [ , ... ] )** + + Specifies an optional list of dynamic table options passed to the Data Source V2 connector for + this statement only. The options allow per-statement tuning without changing the table's + persistent configuration. Keys and values are treated as strings; a key that is not a valid + identifier can be quoted with backticks. Spark passes options through without validating their + names, and connectors may ignore options they do not recognize. + +* **WHERE boolean_expression** + + Specifies an optional condition that selects the rows to delete. If the `WHERE` clause is + omitted, all rows are deleted. + +### Examples + +The following examples assume that an `employees` table has already been created and populated. Review Comment: Same as the `UPDATE` page: no schema for `employees` and no results under the examples, unlike `MERGE INTO` and `INSERT TABLE`. -- 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]
