Question Re: Update & Join in Technology Q&A

Update & Join

Former Member — Fri, 12 Feb 2016 11:01:47 GMT

Hello,

I keep receiving the error "SQL Command Not Properly Ended".

I think it has something with my attempt to do with the join.

UPDATE FGMULTI

SET NON_CONFORM_ALLOCATABLE = 'Y'

FROM fgmulti as fg LEFT OUTER JOIN arinvt as ar ON fg.arinvt_id = ar.id

WHERE IN_Date = CurrentDate AND ar.Class LIKE 'CP%' OR ar.Class LIKE 'FG%' OR ar.Class LIKE 'IN%' OR ar.Class LIKE 'LA%' OR ar.Class LIKE 'PK%'

Re: Update & Join

MarkCulp — Fri, 12 Feb 2016 11:31:04 GMT

Is this a question about MySQL? If it is then this is not the place to ask the question. This forum is for questions about SQL Anywhere.

Re: Update & Join

Breck_Carter — Fri, 12 Feb 2016 13:24:21 GMT

That query is valid in SQL Anywhere... perhaps you should switch.

For more reasons to switch from MySQL to SQL Anywhere, see the Unpublished MySQL FAQ.

Re: Update & Join

VolkerBarth — Sat, 13 Feb 2016 15:19:21 GMT

That query is valid in SQL Anywhere.

Are you sure?

I'm not that sure because of the different use of correlation names in the UPDATE clause and the FROM clause for table FGMULTI (i.e. without an alias in the former, with an alias in the latter) - cf. the docs:

If the FROM clause is present, table-name must specify the sole table to be updated, and it must qualify the name in the same way as it appears in the FROM clause. If correlation names are used in the FROM clause, the identical correlation name must be specified as table-name.

That being said, of course SQL Anywhere is way more flexible than some other DBMSs in that respect (updates on joins), for example MS SQL Server does not allow an alias in the UPDATE clause, AFAIK...

Re: Update & Join

Breck_Carter — Sun, 14 Feb 2016 20:05:57 GMT

I will amend that to read "That query is syntactically valid in SQL Anywhere" since it throws a semantic error "table not found"... and yes, I did test it, to prove that it did not throw the syntactic error "SQL Command Not Properly Ended".