Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Update with join seems to be unsupported #3387

Closed
MikeWallis42 opened this issue May 1, 2024 · 0 comments · Fixed by #3389
Closed

Update with join seems to be unsupported #3387

MikeWallis42 opened this issue May 1, 2024 · 0 comments · Fixed by #3389
Assignees

Comments

@MikeWallis42
Copy link

I've checked against the latest version of SQLGlot (23.12.2) and can confirm that what I'm trying to do still doesn't work.
I'm trying to create an update dynamically with a join and I'm starting to wonder if update is missing a join argument.
I've tried to construct the expression that I need directly but it's getting a bit complex.
It could be that I'm just missing some documentation on how to handle this.
My specific example is using the TSQL dialect, I do not know if the update syntax differs across SQL Engines, I've tried to keep the example as agnostic as possible.

It seems that SQLGlot is capable of understanding such queries e.g.

from sqlglot import parse_one

raw_sql = """
UPDATE
    Table_A
SET
    Table_A.col1 = Table_B.col1,
    Table_A.col2 = Table_B.col2
FROM
    Some_Table AS Table_A
    INNER JOIN Other_Table AS Table_B
        ON Table_A.id = Table_B.id
WHERE
    Table_A.col3 = 'cool'
"""
parsed_sql = parse_one(raw_sql)

But I'm not sure how to get the same output when building the same statement programatically.
This was my first attempt, expecting I might be able to put it in to the from_ argument.

from sqlglot.expressions import update

update_properties = {
    "Table_A.col1": "Table_B.col1",
    "Table_A.col2": "Table_B.col1",
}
update(
    "Table_A",
    update_properties,
    "Table_A.col3 = 'cool'",
    "Some_Table AS Table_A INNER JOIN Other_Table AS Table_B ON Table_A.id = Table_B.id"
)

this gives me a ParseError as I guess it can't handle the join.

I then tried to use parse_one with just the from and join parts to get the expression I needed but realised it comes out as a select statement and the AST for the Table expression is different.
The Table expression has joins when parsing the update statement in full but not when trying to parse_one.

parse_one for FROM and JOIN

frozenset({('expressions', (Star(),)), ('from', From(
  this=Table(
    this=Identifier(this=Some_Table, quoted=False),
    alias=TableAlias(
      this=Identifier(this=Table_A, quoted=False))))), ('joins', (Join(
  this=Table(
    this=Identifier(this=Other_Table, quoted=False),
    alias=TableAlias(
      this=Identifier(this=Table_B, quoted=False))),
  kind=INNER,
  on=EQ(
    this=Column(
      this=Identifier(this=id, quoted=False),
      table=Identifier(this=Table_A, quoted=False)),
    expression=Column(
      this=Identifier(this=id, quoted=False),
      table=Identifier(this=Table_B, quoted=False)))),))})

from part of parse_one for full update statement

From(
  this=Table(
    this=Identifier(this=Some_Table, quoted=False),
    alias=TableAlias(
      this=Identifier(this=Table_A, quoted=False)),
    joins=[
      Join(
        this=Table(
          this=Identifier(this=Other_Table, quoted=False),
          alias=TableAlias(
            this=Identifier(this=Table_B, quoted=False))),
        kind=INNER,
        on=EQ(
          this=Column(
            this=Identifier(this=id, quoted=False),
            table=Identifier(this=Table_A, quoted=False)),
          expression=Column(
            this=Identifier(this=id, quoted=False),
            table=Identifier(this=Table_B, quoted=False))))]))

Many thanks for your help in advance!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

Successfully merging a pull request may close this issue.

2 participants