I'm trying to run a query that will update a field in one table if a field in another table is equal to test. Here is the code:
UPDATE Table1 AS t1 INNER JOIN Table2 AS t2
ON t1.Field1 = t2.F_Name
SET t1.Field4 =
(CASE
WHEN t2.PlayField = 'test' THEN 'test'
ELSE 'No test'
END);
However, I always receive a Syntax Error (missing operator) when I run it. Not sure what I'm doing wrong...
Since you only want to understand the issue..Your SQL :
UPDATE Table1 AS t1 INNER JOIN Table2 AS t2
ON t1.Field1 = t2.F_Name
SET t1.Field4 =
(CASE
WHEN t2.PlayField = 'test' THEN 'test'
ELSE 'No test'
END);
MS Access doesn't support the CASE statement. It looks like SQL Server, not MS Access.
You could try:
SET t1.Field4 =
IIf([t2].[playfield]='test','test','No test');
This basically says: Set t1.Field = If t2.PlayField = 'Test', we will use the word 'test', if it doesn't we will use 'No Test'.
CASE ... WHEN is not supported in Access SQL. Use IIf instead.
UPDATE Table1 AS t1 INNER JOIN Table2 AS t2
ON t1.Field1 = t2.F_Name
SET t1.Field4 =
IIf(t2.PlayField = 'test', 'test', 'No test');
If the question is related to MS Access database engine...
Have a look here: MS Access 2003: Update Query that updates values in one table with values from another table
Your query should look like:
UPDATE TableName
SET TableName.Field1 = (SELECT AnotherTable.Field2
FROM AnotherTable
WHERE AnotherTable.Field3 = TableName.Field2)
WHERE (<<condition>>);
CASE WHEN ... END statement is only possible to use within VBA. To achieve such as conditional result within query, use IIF function.
See: IIF function
CASE ... WHENis not supported in Access SQL.