sql syntax error with liquibase using join tables

Question

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 1

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.