Fernando, On Wed, Oct 7, 2009 at 5:08 PM, Fer C. <ferk...@gmail.com> wrote: > Hello > I have a table with a compound primary key (a1,a2) and I want to > insert a record (b1,b2) in th cases where there's no a1 value matching > b1, and if there's already a b1 value in the form (b1,c2) then just > update it so that it turns into (b1,b2). >
Why not use INSERT ON DUPLICATE KEY UPDATE. http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html > So, If I want to insert-update the record "(a1,b2),b3" the two cases would be: > > a) record "(a1,a2),a3" exists and has a matching "a1" > --update-to--> "(a1,b2),b3" > b) there doesn't exist any record matching a1 > ----insert---> "(a1,b2),a3" So, INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE b=2,c=3; Ewen > > This would be trivial if the primary key was only "a1", (REPLACE would > do the job) however, I need "a2" as a primary key in my model, because > it's possible to have different records with the same "a1" if they > have different "a2". > > I could do this by doing a SELECT on the key, then doing an UPDATE if > anything comes back, and INSERT otherwise. But this seems rather > clunky, and I'm wondering if there is any other way that's preferred > for doing this operation. > > Thank you very much in advance > > -- > Fernando > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=ewen.fort...@gmail.com > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
