0

I have a database table for users, and it has a column gcm_id for push notifications to android. I set this column to be unique since a user might logout and login on the same device so they will have the same gcm_id.

I want the behaviour to be as followed:

When an update for a user's gcm_id column is request, if the specific gcm_id already exists, change it to null and continue to update as usuall on the request row.

I'll give an example. Say I have the following entries in my table:

index | user_name | gcm_id
------+-----------+-------
1     | Tom       | 123
2     | John      | NULL

If Tom logs out and logs in as John, the client will want to update the gcm_id for John to be 123. But gcm_id column should be unique. So in that case, I would like that the mysql server will change Tom's gcm_id to NULL and set John's gcm_id to be 123.

All I've seen so far in relation to ON DUPLICATE syntax is to make mysql update only the existing row instead of creating a new one. I want to do both.

Is that possible in a single mysql command?

Tom Klino
  • 2,358
  • 5
  • 35
  • 60

2 Answers2

1

You can kind of do it, see this question about upsert (don't know if applicable to MySQL though).

But I would still use a separate table for device ids, with only gcm_id and user_id fields.

With your current design you don't have an ability to have the same user associated with two different devices. Using an extra table you would be able to trivially remedy that by making user_id non-unique. That alone makes the change worthwhile.

Community
  • 1
  • 1
ilya n.
  • 18,398
  • 15
  • 71
  • 89
0

I am not sure if it is possible. An easier thing to do however may be to just set Tom's gcm_id to NULL when he logs out rather than having to change both rows in one mysql command.

MaxLascombe
  • 96
  • 3