Filemaker SQL Update with WHERE condition on another joint table

Using Filemaker Pro with ODBC driver with SQL update

It returns error with "syntax error no extra clue"

Problem is the "Job" table is Parent to "JobItem" table ( Job.Jobid = JobItem.fkJobId)
Trying to update a field in "JobItem" table ( JobItem.fkProductId = 37)
But condition is based on Parent Job table (Job.WebDatabase = 'Client 32' )
and "JobIem" table (JobItem.WebProductId = 61)

The SQL that fails is ;

Update JobItem SET JobItem.fkProductId = 37 WHERE Job.Jobid = JobItem.fkJobId and Job.WebDatabase = 'Client 32' and JobItem.WebProductId = 61

Open in new window


Hoping you can solve the syntax and way of working with update SQL in Filemaker!
stephenwildeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

stephenwildeAuthor Commented:
Have also tried this SQL Update but same error "Syntax error"

Update JobItem 
SET JobItem.fkProductId = 37 
WHERE JobItem.WebProductId = 61 
and JobItem.fkJobId IN (Select Job.Jobid as fkJobId FROM Job WHERE Job.WebDatabase = 'client 32 ')

Open in new window

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I will agree on that suggestion, as in the reference of FileMaker SQL:
https://fmhelp.filemaker.com/docs/13/en/fm13_sql_reference.pdf
UPDATE statement
Use the 
UPDATE
 statement to change records in a database table. The format of the 
UPDATE
statement is:
UPDATE table_name SET column_name = expr, ... [ WHERE { conditions } ]

Open in new window

and there seem to be no alternatives
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
stephenwildeAuthor Commented:
Thanks, solved now

The syntax problem was on the set you should not use the prefix of the table name as you do on the where clause

SET fkProductId = 37 not SET JobItem.fkProductId = 37

Update JobItem
SET fkProductId = 37
WHERE JobItem.WebProductId = 61
and JobItem.fkJobId IN (Select Job.Jobid as fkJobId FROM Job WHERE Job.WebDatabase = 'client 32 '
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FileMaker Pro

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.