Re: (How) are subqueries optimized involving the same table?

[Jakobus Dornier]

Bob,

thank you for your quick answer.

Yes, but what if I have an INSERT and I'd like to retrieve data from another table:

INSERT INTO
t1
SET
t1.field1 = (SELECT field1 FROM t2 WHERE t2.id = t1.id),
t1.field2 = (SELECT field2 FROM t2 WHERE t2.id = t1.id)
t1.field3 = (SELECT field3 FROM t2 WHERE t2.id = t1.id)


As JOINs are not possible here, would you recommend doing one SELECT first on the application level and passing the values to the INSERT, or can this be done in mysql also? Is the SELECT INTO syntax better than the Subquery Syntax above?

Thanks again.