asked on

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!

stephenwilde

ASKER

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

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

stephenwilde

ASKER

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 '