-1

I have wrote a query in my php method to update the specific filed that has an empty string '' to be set to NULL..

UPDATE user SET context=NULL WHERE context=''

or

UPDATE user SET context=NULL WHERE context=""

When this method is executed successfully, it still does not update the table fields with empty string to null. What can be the issue?

Tried:

UPDATE user SET context = NULL WHERE length(trim(context)) = 0
Álvaro González
  • 142,137
  • 41
  • 261
  • 360
misafe
  • 37
  • 5
  • How do you know it didn't work? Does `SELECT * FROM user WHERE context=''` return rows? – Álvaro González Dec 01 '21 at 13:51
  • context may not be equal to `''`. context may be `' '` or `' '` or longer. You could try `update user set context = NULL where length(trim(context)) = 0`. – zedfoxus Dec 01 '21 at 13:52
  • I updated my post as I tried your solution but still no luck. '' still remains in context field. @zedfoxus – misafe Dec 01 '21 at 13:57
  • If the column does not allow NULL then it will set it to the default value. – Andrew Morton Dec 01 '21 at 14:00
  • Does this answer your question? [How to update column with null value](https://stackoverflow.com/questions/3870540/how-to-update-column-with-null-value) – Andrew Morton Dec 01 '21 at 14:00
  • Column allows null, but this command is needed if there is maybe some leftovers of empty string instead of null.. @AndrewMorton – misafe Dec 01 '21 at 14:05
  • Actually you are right SELECT * FROM user WHERE context='' does not return any row. I see it in the database, how is this possible? @AndrewMorton – misafe Dec 01 '21 at 14:11
  • «'' still remains in context field» - How do you know that? It's possible that your check is flawed. As already noted, white spaces are invisible. – Álvaro González Dec 01 '21 at 14:11
  • That means that the update probably succeeded. You are simply not looking for the right data: you don't have empty strings, you have something else. – Álvaro González Dec 01 '21 at 14:12
  • I do not have any other database. Context field has '' which I inputed myself. I have refreshed the table and they are still there after running an update. :/ It is a longtext field.. @ÁlvaroGonzález – misafe Dec 01 '21 at 14:14

1 Answers1

0

Try this. I think you were missing the sql_safe_updates = 0 part:

$this->addSql("set sql_safe_updates = 0");
$this->addSql("UPDATE user SET context = if(TRIM(context)="",NULL,context");
$this->addSql("set sql_safe_updates = 1");

solomon1994
  • 386
  • 2
  • 10