JPQL: set a column to null in an update

Question

The following JPQL:

UPDATE SignIn signIn SET signIn.cookieUUID = null WHERE signIn.user.id = :userID

Gives me the following error because of the "= null":

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'IS NULL WHERE `B0`.`ID` = 9' at line 1
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)

But it works when I set the field to an empty string (= '').

So how do you set a column to null using JQPL?

There is a [similar post](https://stackoverflow.com/questions/1257736/how-to-put-null-into-column-using-hql) here (for HQL). It should help you to solve it. Hope it helps. — rvcoutinho, Oct 23 '12 at 22:46
While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - [From Review](/review/low-quality-posts/23708896) — Tomerikoo, Aug 04 '19 at 22:04

Yogendra Singh · Accepted Answer · 2012-10-24T00:30:42.317

5

I think its NULL (in caps) ::Reference JPQL and Eclipselink

 UPDATE SignIn signIn SET signIn.cookieUUID = NULL WHERE signIn.user.id = :userID

Or use param query as :

     String sQuery = "UPDATE SignIn signIn SET signIn.cookieUUID = :cookieUUID "+
                 "WHERE signIn.user.id = :userID";
     Query query= entityManager.createQuery(sQuery );
     query.setParameter("cookieUUID", null);
     query.setParameter("userID", userID);
     query.executeUpdate();

edited Oct 24 '12 at 00:30

answered Oct 23 '12 at 21:08

Yogendra Singh

33,927
6
63
73

This still gives me the very same error (and I also tried it before asking) ;) – AndrewBourgeois Oct 23 '12 at 21:11
The field is just annotated @Column, which implicitly means that the column is nullable. – AndrewBourgeois Oct 23 '12 at 21:21
@AndrewBourgeois: Is it possible for you to use param query? I tried with EclipseLink and seems to be working. – Yogendra Singh Oct 24 '12 at 00:31
I know that it works that way, but I find it really awkward to do that when it really is static. Thanks for all the research, though! – AndrewBourgeois Oct 24 '12 at 16:58

JPQL: set a column to null in an update

1 Answers1