Query based insertion or updating of blob values
Unfortunately we have to refer to the columns one by one EXCEPT the unique_key, so we can't use '*' INSERT INTO shows (start_time, day, title, producer, description, page, category_id, season_id)SELECT start_time, day, title, producer, description, page, category_id, 3 FROM shows WHERE season_id=2 I need use ON DUPLICATE KEY with LIMIT (ignore duplicate keys).
So the query should look like: INSERT INTO table_2SELECT * FROM table_1LIMIT 10ON DUPLICATE KEY UPDATE table_2= table_2I hope, this will help to someone.
Take the following commands: CREATE TABLE table1 (user VARCHAR(20) PRIMARY KEY, age INT); CREATE TABLE table2 (user VARCHAR(20) PRIMARY KEY, os VARCHAR(20)); INSERT INTO table1 (user,age) VALUES ('fred',20); INSERT INTO table1 (user,age) VALUES ('mary',30); INSERT INTO table2 (user,os) VALUES ('fred',' Free BSD'); INSERT INTO table2 (user,os) VALUES ('mary',' Linux'); Now you can combine these into a single table.
CREATE TABLE newtable (user VARCHAR(20) PRIMARY KEY, age INT, os VARCHAR(20)); INSERT newtable (user,age,os) SELECT table1.user,table1.age,table2FROM table1,table2 WHERE table1.user=table2.user; If you want to insert a date field you have to do this in the format (YYYY-MM-DD).
Here's a groovy insert:mysql INSERT INTO T_SEC_DETAIL SELECT NULL, ' MSFT', TT. EXCHANGE_ID, NULL FROM T_SEC_TYPE TT, T_EXCHANGE EI WHERE TT. For example: CREATE TABLE a (name VARCHAR(20), last VARCHAR(20)); CREATE TABLE b (name VARCHAR(20), last VARCHAR(20), age TINYINT(3)); If you want to copy Joe from a to b and add his age (23) do the following INSERT INTO b SELECT name, last, 23 FROM a WHERE name="Joe"; Use REPLACE instead of INSERT if the SELECT returns more than one row with the same values and you are trying to insert on a column with a PRIMARY or an UNIQUE key.
I had to extract from a database some information related to particular records (an specific client), but based on a product purchased.
Eventually I came down to the following two syntaxes that work for one to eight BLObs (the maximum I need to insert at one time).
to allow inserting and updating exactly the desired data.
In this case my unique user_id generated from auto increment portion of another table.
(This does not occur with tables using storage engines such as PDF (US Ltr) - 38.2Mb PDF (A4) - 38.2Mb PDF (RPM) - 37.6Mb HTML Download (TGZ) - 10.2Mb HTML Download (Zip) - 10.3Mb HTML Download (RPM) - 9.0Mb Man Pages (TGZ) - 197.4Kb Man Pages (Zip) - 305.9Kb Info (Gzip) - 3.5Mb Info (Zip) - 3.5Mb My SQL Backup and Recovery My SQL Globalization My SQL Information Schema My SQL Installation Guide My SQL and Linux/Unix My SQL and OS X My SQL Partitioning My SQL Performance Schema My SQL Replication Using the My SQL Yum Repository My SQL Restrictions and Limitations Security in My SQL My SQL and Solaris Building My SQL from Source Starting and Stopping My SQL My SQL Tutorial My SQL and Windows My SQL NDB Cluster 7.5 My SQL 5.7 Secure Deployment Guide Having the same problem mentioned above (last_insert_id() returns "0"), I found this solution.
You can use the following select statement: SELECT id FROM mytable WHERE id IS NULL;"id" has to be an auto_increment column to make it work.
If you have a application that is not in this format (DD-MM-YYYY), you can use substring to correct this in your SQL statement. INSERT INTO Orders (id Contact, Contractno, Description, Startdate ) VALUES ( '2', '2040906', '', CONCAT(SUBSTRING('',7,4), SUBSTRING('',4,2), SUBSTRING('',1,2) )) Using INSERT INTO ...
SELECT you can copy data from one database to another. Enter the source database, database1:use database1; Then write to which fields in the destination database you want to copy to, database2: INSERT INTO database2.table1 (field1,field3,field9)SELECT table2.field3,table2.field1,table2.field4FROM table2; The order of the selected fields and the inserted fields must match, so you enter the correct data.