On 28 May 2002, 14:20, Benjamin Pflugmann wrote:
> That's an FAQ and explained in the tutorial:
> http://www.mysql.com/doc/e/x/example-Maximum-row.html
~~~
Thanks for the link, which I've bookmarked. Thanks also to those
that responded off-list.
Unfortunately, the approach given in the tutorial doesn't work
everywhere that a sub-select is required. For example, in one case I
have two tables:
tblmain (id, ...)
tblcount (id, counter)
where tblcount.id is a foreign key referencing tblmain.id
tblcount exists to record the number of times something happens to
each record in tblmain. At the start of operations, tblcount is empty.
When the something happens to records in tblmain that meet certain
criteria, I need to increment tblcount.counter, creating new tblcount
records where necessary.
Now, I can use the same condition to determine the result table from
tblmain and to determine the related records in tblcount. In other
DBMS's I would issue three statements in transaction:
SELECT * FROM tblmain WHERE <condition>;
UPDATE tblcount SET counter=counter+1
WHERE id IN (SELECT id FROM tblmain WHERE <condition>);
INSERT INTO tblcount (id, count) SELECT (id, 1 FROM tblmain
WHERE <condition> AND id NOT IN (SELECT id FROM tblcount);
However, thanks to off-list correspondence, I now know that I need to
do some research into temporary tables because these can provide most
(if not all) the functionality of sub-selects.
Thanks again to all,
--
Geoff Lane
Cornwall, UK
[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
