MySQL update row or insert new row
Moderator: General Moderators
MySQL update row or insert new row
I'm searching the net about 2 hours now and can't find solution how to update record or insert new row in single sql. I found in mysql manual "ON DUPLICATE KEY UPDATE" but this not suits for me because in table i don't have unique indexes or primary keys.
i want write single sql for this algorythm:
sql1: update tbl set val=val+1 where date = today and userid = X
sql2: insert into tbl values(userid, val, date)
if sql1: affected rows = 0 do sql2
i can not use INSERT ... ON DUPLICATE KEY UPDATE because userid, val, date are not unique records.
I hope you understand me
P.S. sorry for my english
sql1: update tbl set val=val+1 where date = today and userid = X
sql2: insert into tbl values(userid, val, date)
if sql1: affected rows = 0 do sql2
i can not use INSERT ... ON DUPLICATE KEY UPDATE because userid, val, date are not unique records.
I hope you understand me
P.S. sorry for my english
I do not know any solution to do this in one query except for using a procedure.
Consider creating a UNIQUE KEY on fields userid and date. I guess these 2 fields should be unique. I don't think you want to have 2 the same counters for a given user and date. Unless you want to have 2 different counters for a given user and date assigned to event type. In this situation you add eventtype field to the table and to the UNIQUE KEY together with userid and date fields. Then use INSERT... ON DUPLICATE KEY UPDATE... .
Hope this will help.
Michal
Consider creating a UNIQUE KEY on fields userid and date. I guess these 2 fields should be unique. I don't think you want to have 2 the same counters for a given user and date. Unless you want to have 2 different counters for a given user and date assigned to event type. In this situation you add eventtype field to the table and to the UNIQUE KEY together with userid and date fields. Then use INSERT... ON DUPLICATE KEY UPDATE... .
Hope this will help.
Michal
If your tables have no unique keys, you're on dangerous ground when you start updating and replacing data! The theory of relational databases absolutely requires a unique key for every table. You can make simple databases work without such keys, but it is nearly always a very bad idea. My advice would be to add an autonumber field (or perhaps a compound primary key, as someone else suggested), then you can use the built-in features of MySQL. I don't see any downside at all.