How to use UPDATE IIF

I am using SQL in MS-Access. I created some queries that analyze if a code is present in two different excel spreadsheets and generate a new table, informing “yes” or “no” for each comparison.

However, sometimes, a certain code appears in just one table, but after a while (when someone update the excel spreadsheets), that code appears.

I already have a query that makes this comparison, but it doesn’t work for those codes that are introduced in the table AFTER the comparison has already been made.

So, I would like to create a query that uses an UPDATE, checking if the given code is now in both tables, and if it is, it would update the comparison column of my table.

This is the query I created for this, but it is not working:

UPDATE 

  comparationTable

SET 

  col_comp = IIf(spreadSheet1.code = spreadSheet2.code),"Yes","******No******")

WHERE 

  code1 = code2;

Note: code1 and code2 are columns that only show the code coming from excel spreadsheet1 and excel spreadsheet2, respectively.

Edit: Here’s the images of the two spreadsheets (sp1, sp2) and the comparation table:

Answer

Too many parenthesis – must always be in pairs. Field names are not correct. The WHERE clause is not appropriate – records without value in Code2 will not update with any value.

UPDATE comparationTable SET col_comp2 = IIf(code1 = code2, "Yes", "******No******")

Advertisement

Answer