Link to home
Create AccountLog in
Avatar of stephenwilde
stephenwilde

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!
Avatar of stephenwilde
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
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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 '