I'm a bit nervous about running an update-inner join type query with postgres since it's different from MySQL and SQL Server that I"m used to (which is much easier). Last time I attempted this with Postgres, I ended up updating over a million rows, not the intended few hundred. Please help me with this query.
The following queries retrieves exactly the rows I need to update:
select * from finst_t f
inner join store_t s on f.store_id=s.id
inner join loc_t l on s.loc_id=l.id
where l.id=201327 and file_vgroup ilike 'sarp%'
In MySQL or SQL Server, I would do the following for the update-inner join:
update finst_t f
inner join store_t s on f.store_id=s.id
inner join loc_t l on s.loc_id=l.id
set l.id=123456
where l.id=201327 and file_vgroup ilike 'sarp%'
I know this is not the style used for postgres but last time I tried to build up a query with Postgres after reading the documentation, it was a disaster. Please help me build a query to do this in Postgres.
2nd question, I'm using Postgres 7.3. In later versions, do they have the same style of update-inner join queries as MySQL and SQL server?
Start Free Trial
