question

Vinay S avatar image
0 Likes"
Vinay S asked Vinay S commented

Need SQL query for to match and find values.

Hi, I've 2 Global tables, PODetails and SAP_Import. I need to match PO number in the PODetails table with SAP_Import table and find the corresponding Section no from SAP_Import table, and update the same in the PODetails table. I used this query,

"Table.query("UPDATE PODetails SET PODetails.Section = SAP_Import.Section FROM PODetails INNER JOIN SAP_Import ON PODetails.PO = SAP_Import.PO"); "


But I'm getting this below error, The columns are same in both table.

syntax error, unexpected identifier PODetails, expecting end of code. Make sure commas, parentheses, etc. are placed correctly

Reference to SAP_Import.Section could not be resolved to a valid column. Parsing halted.

Could not resolve column references in query: UPDATE PODetails SET PODetails.Section = SAP_Import.Section FROM PODetails INNER JOIN SAP_Import ON PODetails.PO = SAP_Import.PO


FlexSim 23.0.4
table.query error
5 |100000
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

2 Answers

Felix Möhlmann avatar image
2 Likes"
Felix Möhlmann answered Vinay S commented

It doesn't appear in the list on its own but SET is supported (probably bundled with UPDATE in the list since those are used together).

I found that the query works if you switch the position of the JOIN and omit the FROM clause. (I used other column names to test it)

Table.query("UPDATE Tab1 INNER JOIN Tab2 ON Tab1.ID = Tab2.ID SET Tab1.Value = Tab2.Value");

See MySQL syntax in the top answer here:

https://stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql-server

· 1
5 |100000
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

Vinay S avatar image Vinay S commented ·
Wow, its working. thank you.

Even ChatGPT didn't give this answer.

0 Likes 0 ·
Joerg Vogel avatar image
0 Likes"
Joerg Vogel answered Jason Lightfoot converted comment to answer

FlexSim supports a limit number of clauses of Structured Query Language.

https://docs.flexsim.com/en/23.1/Reference/DeveloperAdvancedUser/SQLQueries/SQLQueries.html#language

SET is currently not a valid clause.

· 1
5 |100000
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

Vinay S avatar image Vinay S commented ·

I already read this Flexsim SQL doc,

Can you suggest alternative solution for this problem ?, excluding for loop
0 Likes 0 ·