In MySQL can you use JOIN for INSERT queries?
Moderator: General Moderators
- JAB Creations
- DevNet Resident
- Posts: 2341
- Joined: Thu Jan 13, 2005 6:44 pm
- Location: Sarasota Florida
- Contact:
In MySQL can you use JOIN for INSERT queries?
In MySQL can you use JOIN for INSERT queries? This is simply a yes or no question; if yes then I'll post my attempt a little later after I get a reply. 
- jaoudestudios
- DevNet Resident
- Posts: 1483
- Joined: Wed Jun 18, 2008 8:32 am
- Location: Surrey
Re: In MySQL can you use JOIN for INSERT queries?
I dont think so. But someone correct me if I am wrong!
What engine are you using?
If you are thinking of performance and using the InnoDB engine then transactions inserts will be much faster.
What engine are you using?
If you are thinking of performance and using the InnoDB engine then transactions inserts will be much faster.
- JAB Creations
- DevNet Resident
- Posts: 2341
- Joined: Thu Jan 13, 2005 6:44 pm
- Location: Sarasota Florida
- Contact:
Re: In MySQL can you use JOIN for INSERT queries?
I'm not sure what engine it is...though all the table "types" in phpMyAdmin are reported as MyISAM. I was not aware that there were numerous "engines" for MySQL?
Would using JOIN reduce or increase server load overall? My head is getting dizzy with all the things I'll need to update while I write out even very modest features in to the forum software.
Would using JOIN reduce or increase server load overall? My head is getting dizzy with all the things I'll need to update while I write out even very modest features in to the forum software.
- jaoudestudios
- DevNet Resident
- Posts: 1483
- Joined: Wed Jun 18, 2008 8:32 am
- Location: Surrey
Re: In MySQL can you use JOIN for INSERT queries?
There are many types of Mysql engines. MyISAM is the default, but not necessarily the best! It really depends on what you are trying to acheive.
JOINS do increase server load, but there is no simple way around it, it is required for normalised databases - that is why some developers un-normalise part of their database that is going to be search alot. Doing multiple queries in php is worse, because you will send 1 query to the MySql server then back to php and then back to the MySql server again.
I use InnoDB engine because it has row locking rather than MyIsam table locking, plus lots more (transactions - which are kind of like undos). On the other hand MyIsam has full text search.
JOINS do increase server load, but there is no simple way around it, it is required for normalised databases - that is why some developers un-normalise part of their database that is going to be search alot. Doing multiple queries in php is worse, because you will send 1 query to the MySql server then back to php and then back to the MySql server again.
I use InnoDB engine because it has row locking rather than MyIsam table locking, plus lots more (transactions - which are kind of like undos). On the other hand MyIsam has full text search.
Re: In MySQL can you use JOIN for INSERT queries?
A join can be performed for a select statement only, I can't imagine what you'd want to use it in an insert.
Personally, I use Innodb as the storage engine for all my tables. Transactions, row-level locking and other factors make it preferable to MyISAM for me.
Personally, I use Innodb as the storage engine for all my tables. Transactions, row-level locking and other factors make it preferable to MyISAM for me.
Re: In MySQL can you use JOIN for INSERT queries?
You can perform joins on select, update and delete queries at a bare minimum.
Re: In MySQL can you use JOIN for INSERT queries?
Maybe in prepared statements but not in regular queries... as far as I know. Can you give an example of an update / delete statement with a join that works on MySQL?
Re: In MySQL can you use JOIN for INSERT queries?
Here is an example update query joining tables
Code: Select all
UPDATE
admin_users au,
event_log el
SET
au.userid = 1,
el.userid = 1
WHERE
au.userid = 1
AND au.userid = el.userid
Re: In MySQL can you use JOIN for INSERT queries?
I think the MySQL manual is so hard to understand for the average developer that there are quite a few things that are very obscure and unknown.
Re: In MySQL can you use JOIN for INSERT queries?
I actually found the manual to be quite informative for the most part. For some reason I thought the join would appear in the syntax part, and it does but not in an obvious way:
Where table_references can be multiple tables. I haven't used this format in a join in a long time.FROM table_references ...
Re: In MySQL can you use JOIN for INSERT queries?
Well the way I understand it, you're specifying what (fields) & tables to update after the UPDATE part of the query and specifying how to join them in the WHERE clause. I can't remember offhand the exact syntax for deletes, it might be a little bit counter intuitive but it is possible as I have done it before.
- JAB Creations
- DevNet Resident
- Posts: 2341
- Joined: Thu Jan 13, 2005 6:44 pm
- Location: Sarasota Florida
- Contact:
Re: In MySQL can you use JOIN for INSERT queries?
The reason I wanted to know if JOIN's could be used in INSERT queries was to reduce the number of INSERT queries overall. If you can SELECT from multiple tables using JOIN why the heck wouldn't you be able to write to multiple tables?
Then again I don't know about the performance difference of (if you could) JOIN in an INSERT versus multiple INSERT queries.
At what point did everyone learn about MySQL engines? At what general point did any change in performance begin to justify changing from the default engine?
Then again I don't know about the performance difference of (if you could) JOIN in an INSERT versus multiple INSERT queries.
At what point did everyone learn about MySQL engines? At what general point did any change in performance begin to justify changing from the default engine?
- jaoudestudios
- DevNet Resident
- Posts: 1483
- Joined: Wed Jun 18, 2008 8:32 am
- Location: Surrey
Re: In MySQL can you use JOIN for INSERT queries?
Its not just about performance, each has different pros & cons.
If you are thinking of performance on INSERTs then use InnoDB with transactions or use MySQLi with multiplie queries.
If you are thinking of performance on INSERTs then use InnoDB with transactions or use MySQLi with multiplie queries.
Re: In MySQL can you use JOIN for INSERT queries?
It looks like you need logging, right?
If so, you may find using triggers useful.
Just to clarify the "INSERT-JOIN" question - imagine everything in RDB as a table. Tables which have a physical instance (i.e. a created TABLE) are "writable", all of the others (SELECT results, VIEWs, JOINs, etc. ) are "read-only".
By performing a JOIN you get a new, "virtual" table (i.e. "read-only" one), so you can't INSERT into a "JOIN-table".
If so, you may find using triggers useful.
Just to clarify the "INSERT-JOIN" question - imagine everything in RDB as a table. Tables which have a physical instance (i.e. a created TABLE) are "writable", all of the others (SELECT results, VIEWs, JOINs, etc. ) are "read-only".
By performing a JOIN you get a new, "virtual" table (i.e. "read-only" one), so you can't INSERT into a "JOIN-table".
There are 10 types of people in this world, those who understand binary and those who don't