Mysql updating a table from another table

For example, if we have two tables, t1 and t2: CREATE TABLE t1 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b VARCHAR(10)) TYPE=Inno DB; INSERT INTO t1 (b) VALUES (' Spike'), (' Chip'), (' John'); CREATE TABLE t2 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT NOT NULL, FOREIGN KEY (b) REFERENCES t1 (a), c VARCHAR(15)) TYPE=Inno DB; We can INSERT rows into t2 that populate the foreign key column based on a SELECT statement on t1: INSERT INTO t2 (b, c) VALUES ((SELECT a FROM t1 WHERE b=' Chip'), 'shoulder'), ((SELECT a FROM t1 WHERE b=' Chip'), 'old block'), ((SELECT a FROM t1 WHERE b=' John'), 'toilet'), ((SELECT a FROM t1 WHERE b=' John'), 'long,silver'), ((SELECT a FROM t1 WHERE b=' John'), 'li''l'); Then we get:mysql5 rows in set (0.00 sec)This is especially useful if you don't want to specify the ids for your rows (because they may differ from database to database, due to their being based on AUTO_INCREMENTs), but you want to refer to the values of other tables.I haven't tested this to determine the version of My SQL this was introduced into, or whether it is necessary that the tables be Inno DB, but it works on my boxes (My SQL 4.1.12) If you want to add to a table A a column existing in a table B:1) Create an empty column in the table A: ALTER TABLE table A ADD color CHAR(20);2) If you don't have an auto-incrementation in the two tables (table B for exemple): ALTER TABLE table B ADD (id INT AUTO_INCREMENT NOT NULL, PRIMARY KEY(id));3) Fill the columns with the values: UPDATE table A,table B SET table A.color=table B.color WHERE table A.id=table B.id; If you want to combine insert..select with setting an explicit value for a column - you can use join: INSERT INTO Target Table (col1, col2, col3)SELECT col1,col2,col3FROM Source Table JOIN (SELECT ' Explicit Value' AS col3) AS Any Alias This looks quite simple but it took me several hours to understand that there's no need for a special statement to handle such cases. JOIN (SELECT ' Explicit Value' AS col3) AS Any Alias You could easily do the same thing just by using: INSERT INTO Target Table (col1, col2, col3)SELECT col1,col2, ' Explicit Value' FROM Source Tablehth, Lokar I've just discovered that the UPDATE part of the INSERT ...

mysql updating a table from another table-56

Should note that this warning about inserts and nulls "For multiple-row INSERT statements or INSERT INTO ...

SELECT statements, the column is set to the implicit default value for the column data type.

ON DUPLICATE KEY UPDATE syntax doesn't replicate to my slave servers. This is with server version 5.0.36sp1-enterprise-gpl-log. I wouldn't recommend anyone use the INSERT ... If you want to INSERT multiple records using single statement in MS SQL Server, then the syntax for My SQL wont work.

But you can use this insert command to accomplish the same: INSERT INTO tbl_test (First Name) SELECT ' Aleem' UNION ALL SELECT ' Latif' UNION ALL SELECT ' Mughal' With PHP, I use affected_rows to detect the success of an INSERT IGNORE.

Perhaps it's good to add a reference in the part on the ON DUPLICATE KEY feature to the older REPLACE function, which does a DELETE INSERT instead of an UPDATE in case of existing key/unique values. ON DUPLICATE KEY UPDATE ..." and neither an insert is possible (because of duplicate keys) nor an update is necessary (because of identical values) you get "2 affected rows" anyway. on duplicate key id = LAST_INSERT_ID(id), ...;'' If you do this then SELECT LAST_INSERT_ID() will return either the inserted id or the updated id.

Last modified 27-Feb-2018 12:22