i got hibernate SQL Syntax error using native query in Query Annotation (but this syntax is worked i-CodePudding

first of all, i'm using (mysql 5.7, jdk1,8, spring boot)

@Transactional
@Modifying
@Query(value= "set @COUNT = 0; \n" 
        "UPDATE faq f SET f.id= @COUNT\\:=@COUNT 1 WHERE f.id LIKE '%';", nativeQuery = true)
void update();

console message (hibernate error message) is

Hibernate: set @COUNT = 0; 
UPDATE faq f SET f.id= @COUNT:=@COUNT 1 WHERE f.id LIKE '%'; 
(error maessage)
java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE faq f SET f.id= @COUNT:=@COUNT 1 WHERE f.id LIKE '%'' at line 2

but this query is well worked in mysql console

set @COUNT = 0; UPDATE faq SET id = @COUNT:=@COUNT 1 WHERE f.id like '%';

plz help me... I've been searching and trying many solutions, but nothing worked

    ... UPDATE faq f SET f.id= @COUNT\\:=@COUNT 1 WHERE f.id LIKE '%';" ...)
    ... UPDATE faq f SET f.id= @COUNT|=@COUNT 1 WHERE f.id LIKE '%';" ...)
    ... UPDATE faq f SET f.id= @COUNT\\:=@COUNT 1 ;" ...)
    ... UPDATE faq f SET f.id= @COUNT\\:=(@COUNT 1) ;" ...)
    ... (UPDATE faq f SET f.id= @COUNT\\:=@COUNT 1 ;)" ...)

CodePudding user response：

You are trying to execute 2 queries at once. That does not work. You can combine these queries by initializing the @count in your update query like this:

UPDATE faq f 
JOIN (SELECT @count := 0) cnt
SET f.id = @COUNT := @COUNT   1 
WHERE f.id LIKE '%'

SQLFiddle demo