mysql

An issue came up for me today which revisits a post I made some time back: http://michaelphipps.com/2005-10-31/mysql-blob-image-versus-file-system....

If you have a query that gives you a result set that you would like to insert into a different table, try this query:

INSERT INTO table (a, b, c)
SELECT a, b, c FROM table2...

If you are trying to join 2 tables in a way that you get the full contents from one table, and the other table provides rows that match, or null rows where there is no match, try the following site

http://www.xaprb.com/blog/2006/05/26/how-to-write-full-outer-join-in-mys...

I found a bit of tweaking of this sql was exactly what I was looking for:

select * from apples as a
left outer join oranges as o on a.price = o.price


If you are looking for a MYSQL statement that essentially says 'on duplicate key do nothing' or 'insert if not exists', try:

INSERT IGNORE INTO ....

(from the manual)

If you use the IGNORE keyword, errors that occur while executing the INSERT statement are treated as warnings instead. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row still is not inserted, but no error is issued. Data conversions that would trigger errors abort the statement if IGNORE is not specified. With IGNORE, invalid values are adjusted to the closest values and inserted; warnings are produced but the statement does not abort. You can determine with the mysql_info() C API function how many rows were actually inserted into the table.

You may also find http://bogdan.org.ua/2007/10/18/mysql-insert-if-not-exists-syntax.html good reading on the subject.