User defined variables question

User defined variables question

am 11.07.2007 02:20:38 von bizt

Hi,

Im trying to write an SQL file which contains SQL queries which are to
be run individually one after the other. Im using user defined
variables so that I can save time on having to manually insert
constant values.

Hope this makes sense:

SET @sequence := SELECT COUNT(*) FROM tracks WHERE cd_id = 12;
INSERT INTO tracks (song, artist, sequence, cd_id) SET ('Fools Gold',
'Stone Roses', @sequence, 12);

Basically here Im trying to SET the value of $sequence based on the
result of the select statement. So when I first run the two queries
@sequence will have a zero value, the next time it will count the
tracks table and will now return a value of 1, then 2, 3,4 etc. Each
time I will replace the values of the INSERT query but the SET SELECT
query will remain. The SELECT statement runs fine on its own but
obviously Im either trying to do something that is not possible with
the user-defined variables or more likely Ive got the query wrong.
Anyway, could someone please point me in the correct direction, much
appreciated. Thanks

Burnsy

Re: User defined variables question

am 11.07.2007 03:43:26 von bizt

Its cool, figured it out:

SELECT @sequence := COUNT(track_id) FROM tracks WHERE cd_id = 12;

or for whole values:

SELECT @sequence := COUNT(track_id)+1 FROM tracks WHERE cd_id = 12;

Cheers