Update column in CartoDB with value from another table

Question

I am trying to update the column 'lt_dispatch_level' in my table "inland" with the values from another table "current_obs" that has a corresponding column 'lt_dispatch_level'. However when I run this SQL query:

INSERT INTO inland (lt_dispatch_level, timestamp) SELECT lt_dispatch_level, timestamp FROM current_obs;

I get the values from current_obs added in as a second row, with null values being aded in the first row. I attached an image of what this looks like.

What do I need to change in my SQL query to get my values in the first row?

Answer 1

You have to use Update query, something like below,

UPDATE inland
SET lt_dispatch_level = current_obs.lt_dispatch_level,
  timestamp = current_obs.timestamp
FROM current_obs
WHERE current_obs.{common column} = inland.{common column}
AND inland.cartodb_id= 1