Date |Tue, 14 Aug 2001 10:43:36 +0100
>From |"Mariusz Muszalski" <[EMAIL PROTECTED]>
Hello!
MM> Look at this MS SQL:
MM> Declare @BF char(10)
MM> Declare @BL char(10)
MM> SET ROWCOUNT 1
MM> Select @BF=Date From Results Where GameID=1 Order by Date ASC
MM> Select @BL=Date From Results Where GameID=1 Order by Date DESC
MM> Select @BF As BF, @BL As BL
MM> --------------------------
MM> Now wonna to implement above to mySQL:
MM> I used temprary table (is there any other way?)
MM> CREATE TEMPORARY TABLE tmp(BF char(10) not null, BL char(10) not null);
MM> INSERT INTO tmp (BF) SELECT Date From Results Where GameID=1 Order by Date
MM> ASC LIMIT 1;
MM> INSERT INTO tmp (BL) SELECT Date From Results Where GameID=1 Order by Date
MM> DESC LIMIT 1;
MM> SELECT * from tmp;
MM> DROP TABLE tmp;
MM> --------------------------
MM> problem is INSERT INTO creates a new record every time...I tried to use
MM> UPDATE in instead the second INSERT:
MM> UPDATE tmp SET BL = (SELECT Date From Results Where GameID=1 Order by Date
MM> DESC LIMIT 1);
MM> But there is error in the syntax....
MM> Questions: is there only TEMPORARY TABLE way for make it? What is wrong with
MM> this UPDATE?
MM> Cheers,
MM> Mariusz Muszalski
As far as i know UPDATE command can't be used with nested SELECT.
you need to add one operation to your query.
CREATE TEMPORARY TABLE tmp(BF char(10) not null, BL char(10) not null);
....
DELETE FROM tmp;
INSERT INTO tmp (BF) SELECT Date From Results Where GameID=1 Order by Date ASC LIMIT 1;
INSERT INTO tmp (BL) SELECT Date From Results Where GameID=1 Order by Date DESC LIMIT
1;
...
SELECT * from tmp;
DROP TABLE tmp;
___________________________________________________________________
For technical support contracts, visit https://order.mysql.com/
This email is sponsored by SWSoft, http://www.asplinux.ru/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Grigory Bakunov <[EMAIL PROTECTED]>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB / SWSoft
/_/ /_/\_, /___/\___\_\___/
<___/ www.mysql.com
---------------------------------------------------------------------
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
