0

A pretty simple Spring application made to run using different DataSources. In it I have a liquibase change-set which involves this sql:

<sql>
  UPDATE home_description hd
  INNER JOIN home h ON
  hd.id = h.description_id
  SET hd.home_id = h.id
</sql>

While this statement runs perfectly fine on a MySQL database, it doesn't work on PostgreSQL.

This is the exception: 

Error: org.postgresql.util.PSQLException: ERROR: syntax error at or near "INNER"
  Position: 46
liquibase.exception.DatabaseException: org.postgresql.util.PSQLException: ERROR: syntax error at or near "INNER"

Is there a way to rewrite this statement that both MySQL and PostgreSQL accept this? Thanks!

Deniss M.
  • 3,617
  • 17
  • 52
  • 100
  • [This answer on use of `UPDATE` with join](http://stackoverflow.com/questions/7869592/how-to-do-an-update-join-in-postgresql) might help. – tadman Feb 01 '17 at 19:02
  • @tadman I'm not very strong with sql. Will the solution outlined there work with both mysql and postgresql? – Deniss M. Feb 01 '17 at 19:05

1 Answers1

0
<sql>
UPDATE home_description AS hd
SET home_id = h.id
FROM home AS h
WHERE hd.id = h.description_id
</sql>

This works in PostgreSql, but not in MySql. Although it fixed my problem.

Deniss M.
  • 3,617
  • 17
  • 52
  • 100