Re: Subselects and INSERT statements with McKoi

You should be able to do something like the following;

    INSERT INTO my_ref_table (ID1, ID2)
      SELECT max_t1, max_t2 FROM
          ( SELECT MAX(ID) + 1 AS max_t1 FROM SKILLS ),
          ( SELECT MAX(ID) + 1 AS max_t2 FROM PERFORMANCE )

However, you may find it's better to break this into several queries 
because the max_t1 and max_t2 values might be used when updating other 
tables?

Toby.


Christopher Brown wrote:

> OK, thanks.
> 
> Can this approach be adapted so that for a table with multiple foreign keys,
> I can use different subselects (one per foreign key) ?  That'd be very
> useful...
> 
>  - Chris
> 
> -----Original Message-----
> From: [email protected] [mailto:[email protected]] On Behalf Of Tobias
> Downer
> Sent: 11 February 2005 03:17
> To: [email protected]
> Subject: Re: Subselects and INSERT statements with McKoi
> 
> Hi Chris,
> 
> The following query will work as intended;
> 
>    INSERT INTO SKILLS (ID, NAME, DESCRIPTION)
>      SELECT MAX(ID)+1, 'Java', 'Java programming' FROM SKILLS
> 
> Toby.
> 
> Christopher Brown wrote:
> 
> 
>>Hi,
>>
>>I tried the following query:
>>
>>INSERT INTO SKILLS (ID, NAME, DESCRIPTION) VALUES ((SELECT MAX(ID)+1 
>>FROM SKILLS), "Java", "Java programming");
>>
>>...on a table defined as:
>>
>>CREATE TABLE IF NOT EXISTS SKILLS (
>> ID                     INTEGER       NOT NULL,
>> NAME                   VARCHAR(32)   NOT NULL,
>> DESCRIPTION            VARCHAR(4096) NOT NULL,
>> PRIMARY KEY (ID),
>> UNIQUE(NAME)
>>);
>>
>>McKoi DB's SQL parser rejects this query.  Is there any way I can 
>>perform something along those lines without triggers/sequences?
>>
>>Thanks,
>>Chris B



---------------------------------------------------------------
Mckoi SQL Database mailing list  http://www.mckoi.com/database/
To unsubscribe, send a message to [email protected]