sql syntax error with liquibase using join tables

I am using liquibase, and I have a sql script:

   <changeSet id="fileSteps-updateUserIdFromMessage" author="aymen">
    <sql>
        update edi_file_steps fs INNER JOIN GU_User u
        on u.login = SUBSTRING(fs.message,
        locate('[',fs.message)+1,
        LENGTH(fs.message)-locate('[',fs.message)-1)
        set user_id= u.id,
        message= SUBSTRING(fs.message, 0, locate('[',fs.message)-1)
        where message LIKE '%Downloaded%' ;
    </sql>
</changeSet>

My script works fine with mysql. But my java program run on a hsql database. For me this script should work also for hsqldb because it is a pure sql syntax. But I am getting this error:

liquibase.exception.DatabaseException: unexpected token: INNER required: SET

Answer

HyperSQL database does not implement joins in the UPDATE statement. See HSQLDB Update Statement.

You’ll need to rephrase that query.

The equivalent valid query in HyperSQL would probably something like:

update edi_file_steps fs
  set user_id = (
    select u-id 
    from GU_User u
    where u.login = SUBSTRING(fs.message, locate('[',fs.message)+1, 
                              LENGTH(fs.message)-locate('[',fs.message)-1)
  ),
  message= SUBSTRING(fs.message, 0, locate('[',fs.message)-1)
where message LIKE '%Downloaded%';

Assumming the subquery is a “scalar subquery” it should work. Scalar means that the query returns at most one row. Otherwise, you’ll need to aggregate it.