Hugo Ferreira <[EMAIL PROTECTED]> wrote:
I'm trying to solve the following problem... Suppose every access to
the database is made as INSERTs (even UPDATEs). Whenever someone
tries to insert something ovver an existing row (i.e., same PK), that
insert should be treated as an update.
Example:
CREATE TABLE X ( c1 varchar, c2 varchar, c3 varchar, primary key c3)
INSERT INTO X (c1, c3) VALUES(1, 'a')
INSERT INTO X (c1, c2) VALUES(1, 'b') --> UPDATE X set c2 = 'b' WHERE
c1 = 1
The CREATE statement declares c3 as the primary key, not c1. Why do you
now treat c1 as the field that decides whether insert or update should
be performed?
Anyway, see if you can use these statements instead:
insert or replace into X(c1, c2, c3)
select key, x1.c2, 'a' from (select 1 key) left join X x1 on (x1.c1 =
key);
insert or replace into X(c1, c2, c3)
select key, 'b', x1.c3 from (select 1 key) left join X x1 on (x1.c1 =
key);
Igor Tandetnik
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
