Update table two from comparison on table one, bigQuery

Question

I'm trying to update a table in bigquery, using standardSQL but get the following error: Query error: Cannot execute IN subquery with uncomparable types STRING and INT64 at [81:7]

In essence, I have two tables. Table(a) and table(b). I am trying to update a column (boolean column) in table(b) when a particular record (flight_number) is found in table(b) and not in table(a).

UPDATE `HTZ_Analysis.airport_flightrecord` as tableb
SET table(b).In_Airport = False 
WHERE table(b).flight_number NOT IN (SELECT 1 
                                   FROM table(a)
                                  )

Javier Bóbeda Javier Bóbeda · Accepted Answer · 2019-12-10T09:47:14

Check the table's schemas for the columns you are comparing. It seems as if the column types differ, one being STRING and another one INT64. I guess that probably some flight numbers would contain letters, so I would change the column type to STRING in both columns. Then they should be comparable.

Update table two from comparison on table one, bigQuery

1 Answers