sqlwhere-in

SQL statement to update values based on records in a different table

I am trying to update a column in a table, but only for the records found in a separate table based on a specific value.

update revenue_data
set gtm_type = type 
from emea.product
where revenue_data.pl in (select pl from emea.product 
                          where emea.product.type = 'value1')

This is not working as in the table product I have multiple values in column type and the column is getting updated with the first record found for a given pl regardless for what I put in the where criteria.

I cannot figure out what is wrong so I hope you can help. How can I do this?

Solution

  • It looks like you are trying to do an update join in Postgres, but you are getting bogged down by the syntax. Use this version:

    update revenue_data rd
set gtm_type = ep.type
from emea.product ep
where rd.pl = ep.pl and ep.type = 'value1';