Update column value of one table based on values from another table-CodePudding

If I have two tables...

        table_x                          table_y
| location | latitude |  | location | min_latitude | max_latitude |
| -------- | -------- |  | -------- | ------------ | ------------ |
|          |   41.5   |  |  point_x |     41.0     |     42.0     |

How do I set table_x.location to table_y.location if table_x.latitude is in between table_y.min_latitude and table_y.max_latitude? I tried the code below but it cannot recognize table_y.

UPDATE table_x
SET table_x.location = table_y.location
WHERE table_x.latitude BETWEEN table_y.min_latitude AND table_y.max_latitude

CodePudding user response：

In your query, table_y comes from nowhere. Try this:

UPDATE table_x x
JOIN table_y y
ON x.latitude BETWEEN y.min_latitude AND y.max_latitude
SET x.location = y.location

CodePudding user response：

you need to join the second table

But as many rows can be selected for update, you should test this prior of running the query

UPDATE table_x,table_y
SET table_x.location = table_y.location
WHERE table_x.latitude BETWEEN table_y.min_latitude AND table_y.max_latitude