Query Builder for Update Statements

Toad for SQL Server
1

I've looked through your documentation and don't see how to use Query Builder to update one table using data from another table. Does this involve joining the two tables in a subquery?
I can write update queries that run in an editor tab, but when I copy them back to the Query Builder SQL tab, Toad says they cannot be modeled. The dropdown for Statement Type includes Update Statement, but what CAN you do with Update Statements in Query Builder.

I don't think I even see how to update one column setting the values to be equal to another column in the same table (a single table Update Query).

To understand this I created a small test table. To update Column2 to "hello" where Column1 = 3 you enable binding variables from the SQL query --- the (x)= icon on the tool bar. Query builder creates the following and it works.

UPDATE TEST_TOAD
SET COL2 = :COL2
WHERE (TEST_TOAD.COL1 = '3')

But that is for manual entry.
What about something simple, like setting column3 in a table equal to columnn1 based on a where statement.

Do you have any examples in your documentation of using Query Builder and Update Statements?
I have looked at the training videos, but they concentrate on select statements.

I'm aware that these Update queries can only have one table in the top diagram.

Thank you.

2

Welcome back!

You're right, most/all the vids concentrate on SELECTs.
Couple comments...

  • Turns out, it's WAY harder to create a graphical query definition from query syntax, than it is to generate syntax from a query diagram. So, in many cases, Toad will let you know it can't create a graphical diagram from an arbitrary SQL to start with.

  • The Query Builder was never meant to be able to generate all possible variants of each SQL type. However, if it doesn't get you quickly to the SQL you need, it can still generate SQL that gets you pretty close to the desired end result. In these cases, you may have to send the generated SQL to the Editor to tweak it manually for your intended purposes.

You mention a couple of use cases, and I don't see a way for the Query Builder currently to accommodate either of them currently. That is...

  1. There seems to be no option to change the target of the Update's SET to anything other than a bind variable. We could introduce an enhancement suggestion here for Toad to handle this... e.g. either constant values, or other column designators.

  2. As for updating values in one table from another table or source, you're not going to be able to get the Query Builder to do it, but one other possibility comes to mind. You might be able to use the Automation Designer to...

  • Loop through a source data set or table
  • For each record in that source,
    • Assign variable/s to the value/s you want to update your target table for
    • Execute an UPDATE statement to change the target table with the variable values

Hope this gives you some ideas.

3

Thank you for that complete answer. I assumed I was just missing something in the user interface.