MySQL/Python - how can I update a table by adding new values to the first NULL cell poceeding by columns

Question

I am new to MySQL. I am making a table with athletes (name as primary key) and their running times.

In the last training session Tom record was 32 m 46 s and Joe record was 34 m 33 s.

I need a statement (I am using Python) to update the table in order to add the new times in the first available cell the schematic of the table is the following:

Name    G1        G2        G3
---------------------------------
Tom     33m21s    31m54s    null
Joe     35m03s    null      null

Therefore for Tom the time should go in column G3, and for Joe in column G2.

The table should look like this:

Name    G1        G2        G3
----------------------------------
Tom     33m21s    31m54s    32m46s
Joe     35m03s    34m33s    null

I am trying to make this task as automated as possible so I do not want to specify each time in which column the new time should go.

I have tried this code but I have to specify the column each time: I do not know what to insert after "SET"

sqlMP = "UPDATE time SET ...  WHERE player = %s AND G1 IS NULL"

Thanks for the help

Which column should be updated if G3 column already have some value? — Slava Rozhnev
– Slava Rozhnev, Commented Feb 8, 2020 at 21:01
Maybe is note clear. In the first table the time Tom-G3 is null. The value 32m46s should go there and the results is presented in the second table. Hope is clearer now. — Andrea Mariano
– Andrea Mariano, Commented Feb 8, 2020 at 21:03

GMB · Accepted Answer · 2020-02-09 10:30:39Z

3

You could use coalesce() as follows:

update time_table set 
    g1 = coalesce(%s, g1),
    g2 = case when g1 is not null and g2 is null then %s else g2 end,
    g3 = case when g1 is not null and g2 is not null and g3 is null then %s else g3 end
where player = %s

Note that this requires you to pass the target value three times.

However, let me advise that this is not the right way to store your data. You have a one-to-many relationship between players and times; you should be storing each time/player tuple on a different row. This would greatly simplify the logic (an new time is just an insert in table), and save you from many more problems in the future (like: what if you want to store 4 times per player instead of 3?).

Gordon L’inoffensif explained in his answer that in MySQL, changes performed in a SET are visible from the followings - so the above query will not work as intended.

I think that we can work around this by reverting the assignment sequence:

update time_table set 
    g3 = case when g1 is not null and g2 is not null and g3 is null then %s else g3 end,
    g2 = case when g1 is not null and g2 is null then %s else g2 end,
    g1 = coalesce(%s, g1)        
where player = %s

edited Feb 9, 2020 at 10:30

answered Feb 8, 2020 at 21:01

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Gordon Linoff Over a year ago

. . You may want to read my answer. This is definitely a case where I understand the answer, but not the upvotes.

GMB Over a year ago

@GordonLinoff: ah yes, that’s right... I missed this MySQL caveat. Thank you. I added a new version of the query, by reverting the order of assignments: unless I am mistaking, this could do it.

Andrea Mariano Over a year ago

@GMB do you mean switching columns and rows so you have the name of the players in the column and the session in the row?

GMB Over a year ago

@AndreaMariano: I just mean that Gordon Linoff's comment is 100% valid, and that the second query in my answer should do what you expect.

Gordon Linoff · Accepted Answer · 2020-02-08 22:58:13Z

MySQL differs from other databases -- and the standard -- because when you update a column, the new value is visible on the right side of the set after that. See reference after the code. Argggh!

That makes this rather challenging. Assuming that you have one row per name, you can use a self-join:

update time_table tt join
       time_table ttold
       on tt.name = ttold.name cross join
       (select %s as new_value  -- just a convenience
       ) v
    set tt.g1 = (case when ttold.g1 is null
                      then v.new_value
                      else ttold.g1
                 end),
        tt.g2 = (case when ttold.g1 is null and ttold.g2 is null
                      then v.new_value
                      else ttold.g2
                 end)
        tt.g3 = (case when ttold.g1 is null and ttold.g2 is null and ttold.g3 is null
                      then v.new_value
                      else ttold.g3
                 end);

As GMB points out, this is a very bad data structure. You should be storing the values in separate rows rather than separate values.

The documentation on this is here:

The second assignment in the following statement sets col2 to the current (updated) col1 value, not the original col1 value. The result is that col1 and col2 have the same value. This behavior differs from standard SQL.
UPDATE t1 SET col1 = col1 + 1, col2 = col1;

Collectives™ on Stack Overflow

MySQL/Python - how can I update a table by adding new values to the first NULL cell poceeding by columns

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related