Обсуждение: I often have to do "update if exist, else insert", is my database design wrong?

Hi. This is just some thoughts about database design.
I often find my self having to do this

update  table_XY set x=..., y=...  where x=... AND y=....;
if not found then
   insert into table_XY (x,y) values (...,...);
end if;

Is this normal or are there something else I could do so I don't have
to check if it exists?

Or is there some more general problem with the table design?
table_XY is in this case (and most cases) a table like this

create table table_XY (
   x  int references table_X,
   y  int references table_Y
);

I could of course add a constraint unique(x,y) to avoid duplicates,
but that would not change the the need to check if it exists before
inserting.
I could also do
delete from table_XY where x=... and y=...
insert into table_XY (x,y) values (...,...);
But that would seem to be very slow.

One idea is to put in dummy records for each x,y combination, as a
default value. But if table_XY has very few records, it seems like a
complete vaste of resources.
If I really wanted table_XY to contain a complete set of records of
all possible pairs of x,y values, how would I do that? The only way I
can think of is that when inserting into table_X, I'd do

insert into table_XY VALUES SELECT xvalue,table_Y.id FROM table_Y;

where table_Y contains a primary key called id.

How would you (you who knows this stuff far better than me) do this? :-)

For the developers: a combined  insert/update command would be nice :-)

A B wrote:
>
> Or is there some more general problem with the table design?
> table_XY is in this case (and most cases) a table like this
>
> create table table_XY (
>    x  int references table_X,
>    y  int references table_Y
> );

Without knowing what XY are and what you are using table_XY for it's
going to be difficult for anyone to offer useful advice.

--
   Richard Huxton
   Archonet Ltd

> Is this normal or are there something else I could do so I don't have
> to check if it exists?

I would say that it is normal.


> For the developers: a combined  insert/update command would be nice
> :-)

Mysql has such a beast along a some other non SQL compliant extensions.

One possible approach to what you are trying to do would be:
update existing table with new values
delete from new table all records that already exist
insert remaining new records

Which can be done all in pure sql. No need to write a program.

On Jul 25, 2008, at 11:46 AM, Francisco Reyes wrote:

>> Is this normal or are there something else I could do so I don't have
>> to check if it exists?
>
> I would say that it is normal.
>
>
>> For the developers: a combined  insert/update command would be nice
>> :-)
>
> Mysql has such a beast along a some other non SQL compliant
> extensions.

MERGE isn't trivial, but the developers are well aware of the desire
for it.

>
>
> One possible approach to what you are trying to do would be:
> update existing table with new values
> delete from new table all records that already exist
> insert remaining new records
>
> Which can be done all in pure sql. No need to write a program.

Well... the race conditions may bite you there, if you have concurrent
access.

This - http://www.postgresql.org/docs/8.3/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE
  - is the usual approach suggested for a concurrent-access safe
upsert/merge right now.

Cheers,
   Steve