Page 1 of 1
MySQL update row or insert new row
Posted: Tue Sep 18, 2007 5:39 am
by kreoton
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.
Posted: Tue Sep 18, 2007 6:38 am
by mezise
Hi,
I don't get your problem, please be more specific. Maybe post an example.
Michal
Posted: Tue Sep 18, 2007 7:39 am
by kreoton
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
Posted: Tue Sep 18, 2007 10:02 am
by mezise
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
Posted: Tue Sep 18, 2007 10:37 am
by pickle
Look at the REPLACE query.
Posted: Tue Sep 18, 2007 11:37 am
by mezise
Unfortunately REPLACE query requires the same UNIQUE KEY condition and has less functionality than INSERT... ON DUPLICATE KEY UPDATE... . Additionaly on duplication REPLACE carries out deletion of an old row before a new one is inserted. Unnecessary operation.
Michal
Posted: Tue Sep 18, 2007 7:45 pm
by califdon
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.
Posted: Wed Sep 19, 2007 12:10 am
by kreoton
thanks for your answers and sugestions. i think update my tables by adding unique keys.