I'm trying to make this query work. The aim is to get the minimum date between a list of minimum dates.

But I'm getting: *Unknown column 'di.device\_id' in 'where clause'*

How can I expose the device\_id field to be visible inside the second subquery?

```sql
update device_information di
set first_seen=(
  select min(date) from (
    select min(insert_date) date from product_state_change_event where device_id=di.device_id
    union select min(insert_date) date from sensor_range_switch_change_event where device_id=di.device_id
  ) as dates
);

```

**Edited**

In fact, I have 19 tables to *union* them.