MySQL update row or insert new row

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
kreoton
Forum Commoner
Posts: 42
Joined: Fri Mar 03, 2006 7:27 pm

MySQL update row or insert new row

Post 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.
mezise
Forum Newbie
Posts: 17
Joined: Tue Sep 18, 2007 4:38 am

Post by mezise »

Hi,
I don't get your problem, please be more specific. Maybe post an example.

Michal
User avatar
kreoton
Forum Commoner
Posts: 42
Joined: Fri Mar 03, 2006 7:27 pm

Post 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
mezise
Forum Newbie
Posts: 17
Joined: Tue Sep 18, 2007 4:38 am

Post 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
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

Look at the REPLACE query.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
mezise
Forum Newbie
Posts: 17
Joined: Tue Sep 18, 2007 4:38 am

Post 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
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post 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.
User avatar
kreoton
Forum Commoner
Posts: 42
Joined: Fri Mar 03, 2006 7:27 pm

Post by kreoton »

thanks for your answers and sugestions. i think update my tables by adding unique keys.
Post Reply