Repository: sqlfluff/sqlfluff
Pull request: Issue 855: UPDATE should support table aliasing and FROM clause
Resolves #855 

Problem statement:
[BigQuery] Found unparsable section: 'UPDATE\n
Hi there!

I am running sqlfluff on a BigQuery update query and the linter throws an `unparsable section` error.

## lint 

```sql
UPDATE
  `my_project.my_table.my_table` AS tttd
SET
  tttd.days=ttu.days,
  tttd.closure=ttu.clousre
FROM
  `{source_table_id}` AS ttu
WHERE
  tttd.po_number=ttu.po_number
;

DROP TABLE `{source_table_id}`
;
```
## parse

```
[0](1, 1, 1)        |file:
[0](1, 1, 1)        |    statement:
[0](1, 1, 1)        |        update_statement:
[0](1, 1, 1)        |            unparsable:                                       !! Expected: 'update_statement'
[0](1, 1, 1)        |                keyword:                                      'UPDATE'
[6](1, 1, 7)        |                newline:                                      '\n'
[7](1, 2, 1)        |                whitespace:                                   '  '
[9](1, 2, 3)        |                raw:                                          '`my_project.my_table.my_table`'
[39](1, 2, 33)      |                whitespace:                                   ' '
[40](1, 2, 34)      |                raw:                                          'AS'
[42](1, 2, 36)      |                whitespace:                                   ' '
[43](1, 2, 37)      |                raw:                                          'tttd'
[47](1, 2, 41)      |                newline:                                      '\n'
[48](1, 3, 1)       |                raw:                                          'SET'
[51](1, 3, 4)       |                newline:                                      '\n'
[52](1, 4, 1)       |                whitespace:                                   '  '
[54](1, 4, 3)       |                raw:                                          'tttd'
[58](1, 4, 7)       |                raw:                                          '.'
[59](1, 4, 8)       |                raw:                                          'days'
[63](1, 4, 12)      |                raw:                                          '='
[64](1, 4, 13)      |                raw:                                          'ttu'
[67](1, 4, 16)      |                raw:                                          '.'
[68](1, 4, 17)      |                raw:                                          'days'
[72](1, 4, 21)      |                comma:                                        ','
[73](1, 4, 22)      |                newline:                                      '\n'
[74](1, 5, 1)       |                whitespace:                                   '  '
[76](1, 5, 3)       |                raw:                                          'tttd'
[80](1, 5, 7)       |                raw:                                          '.'
[81](1, 5, 8)       |                raw:                                          'closure'
[88](1, 5, 15)      |                raw:                                          '='
[89](1, 5, 16)      |                raw:                                          'ttu'
[92](1, 5, 19)      |                raw:                                          '.'
[93](1, 5, 20)      |                raw:                                          'clousre'
[100](1, 5, 27)     |                newline:                                      '\n'
[101](1, 6, 1)      |                raw:                                          'FROM'
[105](1, 6, 5)      |                newline:                                      '\n'
[106](1, 7, 1)      |                whitespace:                                   '  '
[108](1, 7, 3)      |                raw:                                          '`my_table`'
[127](1, 7, 22)     |                whitespace:                                   ' '
[128](1, 7, 23)     |                raw:                                          'AS'
[130](1, 7, 25)     |                whitespace:                                   ' '
[131](1, 7, 26)     |                raw:                                          'ttu'
[134](1, 7, 29)     |                newline:                                      '\n'
[135](1, 8, 1)      |                raw:                                          'WHERE'
[140](1, 8, 6)      |                newline:                                      '\n'
[141](1, 9, 1)      |                whitespace:                                   '  '
[143](1, 9, 3)      |                raw:                                          'tttd'
[147](1, 9, 7)      |                raw:                                          '.'
[148](1, 9, 8)      |                raw:                                          'po_number'
[157](1, 9, 17)     |                raw:                                          '='
[158](1, 9, 18)     |                raw:                                          'ttu'
[161](1, 9, 21)     |                raw:                                          '.'
[162](1, 9, 22)     |                raw:                                          'po_number'
[171](1, 9, 31)     |    newline:                                                  '\n'
[172](1, 10, 1)     |    statement_terminator:                                     ';'
[173](1, 10, 2)     |    newline:                                                  '\n'
[174](1, 11, 1)     |    newline:                                                  '\n'
[175](1, 12, 1)     |    statement:
[175](1, 12, 1)     |        drop_statement:
[175](1, 12, 1)     |            keyword:                                          'DROP'
[179](1, 12, 5)     |            whitespace:                                       ' '
[180](1, 12, 6)     |            keyword:                                          'TABLE'
[185](1, 12, 11)    |            whitespace:                                       ' '
[186](1, 12, 12)    |            table_reference:
[186](1, 12, 12)    |                identifier:                                   '`my_table`'
[205](1, 12, 31)    |    newline:                                                  '\n'
[206](1, 13, 1)     |    statement_terminator:                                     ';'
[207](1, 13, 2)     |    newline:                                                  '\n'

==== parsing violations ====
L:   1 | P:   1 |  PRS | Found unparsable section: 'UPDATE\n `my_project.my_table.my_table`
                       | ...'
```

## Expected Behaviour

No errors


## Observed Behaviour
```
L:   1 | P:   1 |  PRS | Found unparsable section: 'UPDATE\n
                       | `my_project.my_table.my_table` ...'
```

## Steps to Reproduce

```
sqlfluff lint file_name
```

## Version
Include the output of `sqlfluff --version` along with your Python version

## Configuration
```
[sqlfluff]
verbose = 0
dialect = bigquery
templater = python

[sqlfluff:templater:python:context]
source_table_id=my_table
```

Hints from issue discussion:
I think this issue is the result of two missing grammar features:
* Support for aliases in `UPDATE` statements (`AS tttd`). This [section of the docs](https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax#update_examples) has an example. This syntax is also supported by [Postgres](https://www.postgresql.org/docs/9.1/sql-update.html).
* Support for [`FROM` clause in `UPDATE` statements](https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax#from_keyword). Apparently other databases support this syntax as well, e.g. [SQL Server](https://www.tutorialgateway.org/how-to-update-from-select-in-sql-server/).

Commit message: Issue 855: UPDATE should support table aliasing and FROM clause

Patch to review:
diff --git a/src/sqlfluff/core/dialects/ansi_keywords.py b/src/sqlfluff/core/dialects/ansi_keywords.py
index c3a9feadb2a..11c7882e4e1 100644
--- a/src/sqlfluff/core/dialects/ansi_keywords.py
+++ b/src/sqlfluff/core/dialects/ansi_keywords.py
@@ -19,6 +19,7 @@
 PARTITION
 ORDER
 ROWS
+SET
 """
 
 ansi_unreserved_keywords = """ABORT
@@ -667,7 +668,6 @@
 SERVER_NAME
 SESSION
 SESSION_USER
-SET
 SETOF
 SETS
 SETUSER
diff --git a/src/sqlfluff/core/dialects/dialect_ansi.py b/src/sqlfluff/core/dialects/dialect_ansi.py
index 2c1f7a62657..08f12df039f 100644
--- a/src/sqlfluff/core/dialects/dialect_ansi.py
+++ b/src/sqlfluff/core/dialects/dialect_ansi.py
@@ -2205,8 +2205,9 @@ class UpdateStatementSegment(BaseSegment):
     match_grammar = StartsWith("UPDATE")
     parse_grammar = Sequence(
         "UPDATE",
-        Ref("TableReferenceSegment"),
+        Ref("AliasedTableReferenceSegment"),
         Ref("SetClauseListSegment"),
+        Ref("FromClauseSegment", optional=True),
         Ref("WhereClauseSegment", optional=True),
     )
 
diff --git a/test/fixtures/parser/ansi/update.yml b/test/fixtures/parser/ansi/update.yml
index b857e122bb9..b61da2b16dc 100644
--- a/test/fixtures/parser/ansi/update.yml
+++ b/test/fixtures/parser/ansi/update.yml
@@ -3,7 +3,8 @@ file:
     update_statement:
       keyword: UPDATE
       table_reference:
-        identifier: table_name
+        table_reference:
+          identifier: table_name
       set_clause_list:
       - keyword: SET
       - set_clause:
diff --git a/test/fixtures/parser/ansi/update_with_from_clause.sql b/test/fixtures/parser/ansi/update_with_from_clause.sql
new file mode 100644
index 00000000000..d1041083c3d
--- /dev/null
+++ b/test/fixtures/parser/ansi/update_with_from_clause.sql
@@ -0,0 +1,8 @@
+UPDATE
+  my_table
+SET
+  my_table.days=other_table.days
+FROM
+  other_table
+WHERE
+  my_table.po_number=other_table.po_number
diff --git a/test/fixtures/parser/ansi/update_with_from_clause.yml b/test/fixtures/parser/ansi/update_with_from_clause.yml
new file mode 100644
index 00000000000..c076db85774
--- /dev/null
+++ b/test/fixtures/parser/ansi/update_with_from_clause.yml
@@ -0,0 +1,37 @@
+file:
+  statement:
+    update_statement:
+      keyword: UPDATE
+      table_reference:
+        table_reference:
+          identifier: my_table
+      set_clause_list:
+        keyword: SET
+        set_clause:
+        - column_reference:
+          - identifier: my_table
+          - dot: .
+          - identifier: days
+        - comparison_operator: '='
+        - column_reference:
+          - identifier: other_table
+          - dot: .
+          - identifier: days
+      from_clause:
+        keyword: FROM
+        table_expression:
+          main_table_expression:
+            table_reference:
+              identifier: other_table
+      where_clause:
+        keyword: WHERE
+        expression:
+        - column_reference:
+          - identifier: my_table
+          - dot: .
+          - identifier: po_number
+        - comparison_operator: '='
+        - column_reference:
+          - identifier: other_table
+          - dot: .
+          - identifier: po_number
diff --git a/test/fixtures/parser/ansi/update_with_table_alias.sql b/test/fixtures/parser/ansi/update_with_table_alias.sql
new file mode 100644
index 00000000000..ff4902119e1
--- /dev/null
+++ b/test/fixtures/parser/ansi/update_with_table_alias.sql
@@ -0,0 +1,4 @@
+UPDATE
+  my_table AS tttd
+SET
+  tttd.days=ttu.days
diff --git a/test/fixtures/parser/ansi/update_with_table_alias.yml b/test/fixtures/parser/ansi/update_with_table_alias.yml
new file mode 100644
index 00000000000..6ed8de8bef2
--- /dev/null
+++ b/test/fixtures/parser/ansi/update_with_table_alias.yml
@@ -0,0 +1,22 @@
+file:
+  statement:
+    update_statement:
+      keyword: UPDATE
+      table_reference:
+        table_reference:
+          identifier: my_table
+        alias_expression:
+          keyword: AS
+          identifier: tttd
+      set_clause_list:
+        keyword: SET
+        set_clause:
+        - column_reference:
+          - identifier: tttd
+          - dot: .
+          - identifier: days
+        - comparison_operator: '='
+        - column_reference:
+          - identifier: ttu
+          - dot: .
+          - identifier: days


Code review comments:
diff_hunk: @@ -3,7 +3,8 @@ file:
     update_statement:
       keyword: UPDATE
       table_reference:
-        identifier: table_name
+        table_reference:
path: test/fixtures/parser/ansi/update.yml
text: @user1:
Nested table_reference? This doesn't look quite right 🤔

diff_hunk: @@ -2205,8 +2205,9 @@ class UpdateStatementSegment(BaseSegment):
     match_grammar = StartsWith("UPDATE")
     parse_grammar = Sequence(
         "UPDATE",
-        Ref("TableReferenceSegment"),
+        Ref("AliasedTableReferenceSegment"),
path: src/sqlfluff/core/dialects/dialect_ansi.py
text: @user1:
What's the outcome of changing this line to:
```suggestion
        OneOf(Ref("TableReferenceSegment"), Ref("AliasedTableReferenceSegment"))
```

I think in its current state even un-aliased table reference will be parsed as one. This should solve my comment here https://github.com/sqlfluff/sqlfluff/pull/865/files#r597557373

@author:
I'll make that change. I think I was so focused on getting a successful parse, I didn't think about cleanliness. I wonder if we should consider (later, not this PR) changing `AliasedTableReferenceSegment` so the alias is required. The naming is a bit odd, calling it "aliased" when the alias is optional. 🤔

@user1:
Ah yes I definitely think so. Feel free to do it here or we can tackle separately

@user1:
I hadn't realised the alias was optional in that segment