Understanding SQL Query to Alteryx

[nitin_l]

Hi, I am automating a bunch of SQL queries using Alteryx tool. I am trying to replicate the below query using Join and other tools. But I am not aware of  how to replicate "!" symbol in the below query into Alteryx. I have no experience in SQL

UPDATE [tbl_1] INNER JOIN [tbl_2] ON [tbl_1].[ID Number] = [tbl_2].[ID Number]

SET [tbl_1].[Min Amount] = [tbl_2]![Min Amount];

[Samanthaj_hughes]

Hi @nitin_l 

You will need to learn SQL to make your life so much easier I would recommend w3 schools for that. 

https://www.w3schools.com/sql/sql_update.asp

Here is a link to an update statement example. Its not about knowing what ! Is from your old location more along what you are trying to achieve. I.e  updating min amount from one table to the next. 

[apathetichell · Accepted solution]

what @Samanthaj_hughes said is true - but I think in this case - it's just a typo and is meant to be "." - this is looking at schema/db/.table syntax - standard syntax is "db/schema"."table" (or in BQ "table"."dataset" or in Snowflake "database"."schema"."table" - but you should get the idea) "." is the divider between objects.

having said that - there are some DBs (and some versions of some DBs) which do not support UPDATE - and Alteryx has different support for different databases...

[Samanthaj_hughes]

Also something to support more advanced SQL users especially if you have snowflake. That is writing up a temp table and using a MERGE statement to update those values which have changed. I love these get everything done in a fraction of the time. You need a primary key that doesn't change then all columns can be updated from your temp table of data you push up. This takes a bit of writing into your postSQL statements on an output tool. Happy to blog an example if needed.

[Samanthaj_hughes]

I'm curious to know if the statements are from MS Access. 😉

[nitin_l]

Yes @Samanthaj_hughes  . I just found out that "!" is not a typo but to refer a column of an Excel file added into the MS Access. Thanks for the inputs