On Wed, 1999-09-22 16:14:51 -0700, Scott Hess wrote:
> Martin Ramsch <m.ramsch@stripped> wrote:
> > My suggested syntax for such a beast is to use the existing REPLACE
> > syntax, with a new special symbol for "the existing value"!
> > This special symbol only would be allowed for non primary key fields!
> It would be sufficient to specify a unique key [...]
uh, right, I didn't think of other key candidates. So the rule could
be extended (and put another way): there have to be values for at
least the primary key fields, or one unique key, mutually exclusiv.
(As you wrote, values in serveral unique fields might be ambiguous
and checking for this ambiguity maybe is too expensive ...)
> Another option to get this behaviour would be something like:
> REPLACE INTO mytable SELECT <primary-key>, <new-value>, col1, col2 FROM
> mytable WHERE primary-key=<primary-key>
Definitely the most versatile syntax! :)
> OTOH, I suspect this is annoying to implement right.
Hmm, MySQL already has all of this besides one thing: it can't neither
insert nor replace into the same table it selects from. But I guess
this limitation will be easily overcome not later than when subselects
are possible, because then it should be easy to hold the temporary
result of the select and then do the replace/insert.
Using an explicit temporary table it's possible already now:
CREATE TEMPORARY TABLE tmp type=heap
SELECT <primary-key>, <new-value>, col1, col2
REPLACE INTO mytable SELECT * FROM tmp;
DROP TABLE tmp;
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26 E4 EC 80 58 7B 31 3A D7