0

i'm writing a simple query in mysql, with no results... I don't understand why. Can you help me to understand?

Query:

UPDATE mytable set myfield = NULL where myfield = "BAR";

Results: 0 row affected

Similar Query:

UPDATE mytable set myfield = "FOO" where myfield = "BAR";

Results: 600 row affected

myfield is a text field, default value NULL (nullable).

Where i'm wrong?

More detail: i attach a screenshot of my real query simulation on phpmyadmin. Sorry for italian language on screenshot, i hope you understand.

enter image description here

Community
  • 1
  • 1
Simone Giusti
  • 313
  • 5
  • 16
  • Possible duplicate of [How to update column with null value](https://stackoverflow.com/questions/3870540/how-to-update-column-with-null-value) – treyBake Jul 13 '17 at 11:20
  • This cannot be happening; the new value in the `set` clause never influences the filtering done by the `where` clause. Double-check the real queries you ran (which clearly are not these pieces of pseudocode). I'm flagging this as lacking complete information to replicate/diagnose the (claimed) problem. – underscore_d Jul 13 '17 at 11:27
  • You must have mistyped something in the first query. – Barmar Jul 13 '17 at 11:41
  • Show the row count of `select * from harddisk where sessione_fase1 = 'test'` before applying the updates shown. By the way, as that shows, you should be using single quotes for strings, as that is the ANSI standard delimiter; double-quotes are more often assumed to delimit identifiers (though I doubt that can be the problem here). – underscore_d Jul 13 '17 at 11:53
  • 1
    @underscore_d, row count of select * from harddisk where sessione_fase1 = 'test' is 641. Main problem seems to be that sessione_fase1 do not accept NULL value. I tried query suggest to Chris (UPDATE mytable set myfield = NULL) and results is 0 row affected. – Simone Giusti Jul 13 '17 at 11:58
  • @SimoneGiusti so then the only problem is that the GUI you are using fails to report e.g. a non-`null` constraint violation, and instead just returns a rowcount as if everything is OK. Or maybe the `null` update is prevented using an `instead of` trigger rather than a constraint... hard to say without knowing anything about your schema. – underscore_d Jul 13 '17 at 11:59
  • Does a trigger exist for the table that sets the column to its old value if you try to set it to NULL? That would explain the rows affected being 0, because rows affected in an UPDATE is the number of rows *changed*. If they don't change, they aren't counted. – Bill Karwin Jul 13 '17 at 16:53
  • @BillKarwin If, as I said, it was an `instead of` trigger that elected not to perform _any_ updated instead of the requested one, I would understand the report that 0 rows were affected - but if it were an `after` trigger that followed the requested update with another update back to the old value, I would expect that the full count would be reported, since the change the OP requested _did_ occur; the fact that it was then negated should not matter to the reported row count. Or if not, what is my reasoning missing? – underscore_d Jul 13 '17 at 18:36

1 Answers1

1

You aren't getting an error, but instead Results: 0 row affected. This probably means nothing matched myfield = "BAR". Double-check that there is a row with that value.

If you did just:

UPDATE mytable set myfield = NULL

you'd see that you don't get any errors.

Chris
  • 57,622
  • 19
  • 111
  • 137
  • 2
    But you would also set it to null in every row of the table, which is probably not what you want. – user207421 Jul 13 '17 at 11:28
  • @EJP, correct. My point was that the problem isn't setting `myfield` to `NULL`, but rather that the condition fails. – Chris Jul 13 '17 at 11:30
  • 1
    @Chris Why does the condition succeed when he sets it to `"FOO"`, but not when he sets it to `NULL`? It's the same condition. – Barmar Jul 13 '17 at 11:40
  • @Barmar, not sure. OP might have ran the second query followed by the first, in which case all fields are `"FOO"` and not `"BAR"` anymore. Or something similar... – Chris Jul 13 '17 at 11:42
  • 1
    @Chris, your query return 0 rows affected. Seem that is impossibile to set NULL to that column. – Simone Giusti Jul 13 '17 at 11:55
  • 1
    @SimoneGiusti Check what the constraints are on that column. But I would expect your environment to report a violation rather than just silently failing to update. – underscore_d Jul 13 '17 at 11:56