Hi

I've been banging my head against this for a couple of days, and I
suspect it's not possible, but if it is I'd love to know how.

I have three tables. One holds short strings and an auto-increment id for
each string, one holds long strings and an auto-increment id for each
string, the third holds a timestamp for an event, and, if there is an
associated short or long string or both, the id(s) of the strings (or
null if there isn't an associated string).

Is there a way I can write a single insert statement that inserts into,
for example, the event table and the short string table, such that the
short string id in the event table becomes the auto-incremented id of the
string entered in the short string table?

At the moment the only way I can see to do this is to add the string,
query the string table to get the id, then use the id in the event, or
forget about auto-incrementing the ids, start at 0, and before I start
take the count of id in the string table(s), then use that/those as the
next id(s) when entering the string(s) and the event.

I'm thinking something like, perhaps:

insert into note inner join event on note.id = event.note (note.the_note)
values ('some words');

but

ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right syntax
to use near 'inner join event on note.id = event.note (note.the_note)
values ('some words')' at line 1

Which I guess is telling me that the reserved word inner can't be used
after the table name in an insert statement .....

Rgds

Denis McMahon

In article <4f06da8a$0$30186$a8266bb1 (AT) newsreader (DOT) readnews.com>,
Denis McMahon <denismfmcmahon (AT) gmail (DOT) com> wrote:
You can only insert into one table per SQL statement. The only way you
can do what you are asking is procedurally, either in a MySQL stored
procedure (which I won't give, as I'm not familiar with them), or by
your application code making multiple mysql requests. The general logic
would be as follows:

SET @sid=NULL;
SET @lid=NULL;
if (you have a short string) {
INSERT INTO shorts (data) VALUES ('short string');
if it succeeds {
SET @sid=LAST_INSERT_ID();
}
}
if (you have a long string) {
INSERT INTO longs (data) VALUES ('long string');
if it succeeds {
SET @lid=LAST_INSERT_ID();
}
}
INSERT INTO note (eventtime, short_id, long_id) VALUES (NOW(), @sid, @lid);

If you're doing the logic at the application layer, you could use a
function such as mysql_insert_id() instead of SQL queries for
LAST_INSERT_ID(), and remember the sid and lid values in the application
instead of in MySQL variables.

Hope this helps!

Tony

--
Tony Mountifield
Work: tony (AT) softins (DOT) co.uk - http://www.softins.co.uk
Play: tony (AT) mountifield (DOT) org - http://tony.mountifield.org