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

I think there's no DBMS where this would work, you generally don't put joins in alone under ```update``` statements in this way. Consider moving it to regular ```select```, like in this example: https://stackoverflow.com/a/1293347/11610186 — Grzegorz Skibinski, Apr 08 '20 at 21:04

score 1 · Accepted Answer · answered Apr 08 '20 at 21:02

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.

answered Apr 08 '20 at 21:02

The Impaler

45,731
9
39
76

ok, but what I did is a pure sql, normally it should work on hsqldb and mysql,no ? – Aymen Ragoubi Apr 08 '20 at 21:18
@AymenRagoubi Nope, the SQL Standard definition of `UPDATE` does not include joins in it. That's a non-standard [albeit useful] extension available in DB2, PostgreSQL, SQL Server, MariaDB, and MySQL (at least). Not Oracle, Not HyperSQL. – The Impaler Apr 08 '20 at 21:55

sql syntax error with liquibase using join tables

1 Answers1