-1
timestamp  column_a column_b
2020       4        alpha
2019       5        ''
2018       ''       beta
2017       8        -1
2016       -1       theta

I'm trying to replace values using NULLIF() in frequently updating mysql database. In order to reduce number of records I process I want find out the last empty or -1 timestamp. In this case I want to replace any empty strings and -1 to None. And the answer would be 2016. What's the best way to find the last timestamp which would be -1 in this case? Are there any better alternatives to replace value to None in Mysql?

Scapedee
  • 47
  • 7
  • Your request probably didn't get attention, because it lacked the `sql` tag, so we didn't see you were asking a SQL question. Moreover, your question is not really clear. Why can the column contain strings and numbers? The column type is probably a text type (e.g. `VARCHAR`) and you want to replace certain strings (namely the empty string and '-1' with null? And you want to do this very often and you fear this may take too long, because you'll have to read the whole table every time? I'll think of an answer... – Thorsten Kettner Apr 15 '20 at 06:14
  • I added another row to show the need of string datatype. Your interpretation is correct. I don't have control over the api that sends this information. – Scapedee Apr 16 '20 at 04:28
  • The answer stays the same: Either you forbid invalid data with a check constraint or you change the data on insert/update with a trigger. – Thorsten Kettner Apr 16 '20 at 06:38

1 Answers1

0

Your question is not really clear, which is probably caused by your sample data, where the column contains both numbers and an empty string.

As I understand it, you want to replace certain strings (namely the empty string and '-1') with null. You want to do this very often, because data is added to the table all the time and you want to care about those "invalid" entries. You are worried about performance and wish you could somehow have the DBMS only look at new rows and thus don't read the whole table every time you update.

As you are showing an empty string in the column, I suppose the column's type is a string type (e.g. VARCHAR). The first thing to consider is in my opinion hence, whether you want this column to be a string at all. If it were a number column (e.g. INT), there would be no empty strings to start with. Also, -1 would always be just this same number, while a string can have leading or trailing space or special characters, you don't see.

Then, you seem to consider -1 undesired or invalid. So, why allow it to be entered? There are two ways to avoid such entries:

  1. A check constraint throwing an exception on inserts with invalid data.
  2. A trigger that changes the invalid value automatically to something valid.

With any of the two methods you won't have invalid data in the table anymore and there will just be no need to run repair updates.

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73