MySQL Forums
Forum List  »  General

Re: REPLACE without overwriting non-NULL or non-specified columns
Posted by: Björn Steinbrink
Date: March 31, 2006 10:19AM

This probably also looks somewhat scary, but if you wrap it into a stored procedure, it
should get somewhat nicer to look at.

mysql> create table tom1 (id int, value int, unique (id));
Query OK, 0 rows affected (0.01 sec)

mysql> create table tom2 (id int, value int, unique (id));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tom1 values(1,1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into tom1 values(2,NULL);
Query OK, 1 row affected (0.00 sec)

mysql> insert into tom2 values(1,5);
Query OK, 1 row affected (0.00 sec)

mysql> insert into tom2 values(2,5);
Query OK, 1 row affected (0.00 sec)

mysql> SET @query = CONCAT("INSERT INTO tom1 SELECT * FROM tom2 ON DUPLICATE KEY UPDATE ", (SELECT GROUP_CONCAT(CONCAT(COLUMN_NAME, '= IFNULL(tom1.',COLUMN_NAME,',tom2.',COLUMN_NAME,')')) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME='tom1'));
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE test_stmt FROM @query;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE test_stmt;
Query OK, 4 rows affected (0.00 sec)
Records: 2 Duplicates: 2 Warnings: 0

mysql> select * from tom1;
+------+-------+
| id | value |
+------+-------+
| 1 | 1 |
| 2 | 5 |
+------+-------+
2 rows in set (0.00 sec)


Retrieving the column names from INFORMATION_SCHEMA, an UPDATE is constructed that
should do what you want. The resulting string is then used to prepare a statement
which is executed afterwards. Replacing the static table names with variable and
maybe putting some work into dynamic column selection (if you need that) the
final stored procedure should be pretty generic.

The statement that creates the dynamic SQL in readable form:
SET @query = CONCAT(
"INSERT INTO tom1 SELECT * FROM tom2 ON DUPLICATE KEY UPDATE ",
(
SELECT
GROUP_CONCAT(CONCAT(COLUMN_NAME, '= IFNULL(tom1.',COLUMN_NAME,',tom2.',COLUMN_NAME,')'))
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_SCHEMA = 'test' AND TABLE_NAME='tom1')
);

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.