On Sat 2002-12-28 at 05:05:45 -0500, [EMAIL PROTECTED] wrote: > ...and then Benjamin Pflugmann said... [...] > % If you choose to go the SQL route, have a look at TEMPORARY TABLES, > % CREATE TABLE ... SELECT and INSERT ... SELECT. Don't forget that you > > You mean a CREATE TABLE command that has SELECT as part of it, and an > INSERT command that has SELECT as part of it?
Yes and no. Yes, it is a INSERT with SELECT. No, if I consider what you write below. I did not mean sub-selects or derived tables. > Actually I wanted to know how to do the latter but didn't think it > could be done! Briefly: > > create table ccards > ( > # ID number > id smallint not null default 0 auto_increment primary key , > ... > hash tinyblob # hash of the card: have we seen this one before? > ) ; > create table clientcards > ( > # ID number > id smallint not null default 0 auto_increment primary key , > client smallint , # references client.id > card smallint , # references ccards.id (but must be disconnected) > type smallint , # references ccardtypes.id ### need this here? > hash tinyblob # references ccards.hash (but must be disconnected) > ) ; > ... > insert into ccards (type,name,number,expdate) values > ( '1' , 'david thorburn-gundlach' , '1234 5678 9abc def0' , '2003-06-00') ; > update ccards set hash = md5(number) where id = last_insert_id() ; > insert into clientcards values > ( '' , '1' , '1' , '1' , > select ccards.hash where ccards.id = last_insert_id() ) ; That would be something like a derived table, which are only supported since v4.1 (not considering the fact that the FROM clause is missing :-). The INSERT ... SELECT which I referred to is a special syntax which is supported since quite a while (in v3.23 for sure): http://www.mysql.com/doc/en/INSERT_SELECT.html http://www.mysql.com/doc/en/ANSI_diff_SELECT_INTO_TABLE.html In your case it would be something like INSERT INTO clientcards SELECT '', 1, 1, 1, ccards.hash FROM ccard WHERE ccards.id = LAST_INSERT_ID() And you have variables. If the above wouldn't work you could write: SELECT @card_hash := hash FROM ccard WHERE ccards.id = LAST_INSERT_ID(); INSERT INTO clientcards VALUES ( '' , 1, 1, 1, @card_hash ); Of course, that's only possible if you can live with being MySQL-specific. HTH, Benjamin. -- [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 <mysql-unsubscribe-##L=##[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
