List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:August 13 1999 6:16pm
Subject:Re: UPDATE
View as plain text  
On Fr, 1999-08-13 12:33:41 -0500, "tbonenyc" <tbone@stripped> wrote:
> I am trying to increment a value(samplenum) in table1 where the the
> unique ID of table1 has a matching ID in table2.

Unfortunately UPDATE (and DELETE) only do work with a single table,
yet.  So as far as I know the best you can do is

1st, retrieve the ids of all rows to be updated, i.e.
     SELECT table1.samplenum
     FROM table1, table2
     WHERE table1.id=table2.id;
     Store the result list in your application.
2nd, using this stored list, compile the update statement:
     UPDATE table1 SET samplenum=samplenum+1 WHERE id IN (stored list);


There is another work-around using a temporary table:
a) Create a temporary table with the same fields as table1.
   (V3.23.x does support this directly.)
b) Copy only the wanted records from table1 to tmp,
     INSERT INTO tmp SELECT * FROM t1, t2 WHERE t1.id=t2.id;
c) Copy these records back to a doing the update,
     REPLACE INTO a SELECT id, samplenum+1, ... FROM tmp;

Regards,
  Martin
-- 
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
Thread
UPDATEGeocrawler.com13 Aug
  • Re: UPDATEMartin Ramsch13 Aug