How to "REPLACE" updating when it"s a subset of the primary key what

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).

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"

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=gcdmg-mysql-2@m.gmane.org

Re: How to "REPLACE" updating when it"s a subset of the primary key

Fernando,

On Wed, Oct 7, 2009 at 5:08 PM, Fer C. 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.h tml

> So, If I want to insert-update the record "(a1,b2),b3" the two cases woul=
d be:
>
> a) record "(a1,a2),a3" =A0exists and has a matching "a1"
> --update-to--> =A0 =A0"(a1,b2),b3"
> b) there doesn't exist any record matching a1
> ----insert---> =A0 =A0 =A0"(a1,b2),a3"

So,

INSERT INTO table (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE b=3D2,c=3D3;

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. =A0But 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: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dewen.fortune@=
gmail.com
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: How to "REPLACE" updating when it"s a subset of the primary key

Thank you for your fast reply!

On Wed, Oct 7, 2009 at 6:25 PM, ewen fortune wrote=
:
> Why not use INSERT ON DUPLICATE KEY UPDATE.
> http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.h tml
>
>> So, If I want to insert-update the record "(a1,b2),b3" the two cases wou=
ld 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=3D2,c=3D3;

That has the same effect as REPLACE for me.
The primary key is "a,b" so, it's possible that there's a (1,4,5)
record already in the table that I want updated but INSERT ON
DUPLICATE KEY UPDATE will instead insert a (1,2,3) record (the "a"
field is the same but it still has a different "b" and thus it's a
different primary key, so it's not considered a duplicate).

--
Fernando

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg