1

I want to insert records from table2 to table1. I need to replace the records if a duplicate in the primary key is found. The primary key of a type string.

So my statement look like (name is primary key and it is a string. Duplicate rows can arise from table2, which I want to update):

INSERT INTO `myscheme1`.`table1`
(`table1`.`name`,
    `table1`.`id`,
    `table1`.`history`)

SELECT `table2`.`name`,
    `table2`.`id`,
    `table2`.`history`
FROM `myscheme2`.`table2`;

Where to add UPDATE? If I can not use UPDATE, can you clarify how to use ON DUPLICATE KEY UPDATE?

Because I do not get how to apply it? I do not know the new string of the primary key that I should update the old one with (it is being read by the command and I have many duplicate cases). So this example: below assumes I know what is the new value that I should input if a duplicate happens.

I prefer to use UPDATE so it replaces the old record with the new automatically. 

INSERT INTO 
   devices(name) 
VALUES 
   ('Printer') 
ON DUPLICATE KEY UPDATE name = 'Printer';

Can you please show me how to edit my command syntax if I want to use UPDATE or ON DUPLICATE KEY UPDATE?

user9371654
  • 2,160
  • 16
  • 45
  • 78
  • That `ON DUPLICATE` makes no sense. You're setting it to what it's already set to. Why not `INSERT IGNORE INTO` to just ignore the problem? – tadman Apr 24 '19 at 18:42
  • Possible solution... https://stackoverflow.com/questions/42663074/is-merge-statement-available-in-mysql – SS_DBA Apr 24 '19 at 18:44
  • 1
    @tadman IGNORE has caused me problems before. It ignores other errors not only the duplicate in the primary key. Which is something I do not want. – user9371654 Apr 24 '19 at 18:46
  • @WEI_DBA The link does not answer my question. `ON DUPLICATE KEY UPDATE c=c+1;` Can you explain to me how can I do this for a string type primary key? I do not want to add +1 or change anything. Just replace the key with the new record's key. – user9371654 Apr 24 '19 at 18:48
  • If the new record's key is not the same, then it's not a duplicate key, so `ON DUPLICATE KEY` will not be triggered. – Barmar Apr 24 '19 at 18:50
  • @Barmar I am confused. If you know the syntax to do this for my command can you write it? – user9371654 Apr 24 '19 at 18:53
  • Whatever problems it caused, what you're doing here is not going to make any changes. If you can update your example to include a change, that would help communicate your intent. This says "Update the name from X to X" which is a non-change. – tadman Apr 24 '19 at 18:53
  • I don't know the syntax because I don't understand what you're trying to do. Can you show a before-and-after example of what you're trying to achieve? – Barmar Apr 24 '19 at 18:54
  • @Barmar plz have a look at my first command (the second one is an example from a tutorial, which turned to not make sense). I want to insert records from another table. I just need to add syntax to tell MySQL: If it founds a duplicate (from table2, then update the table1 record). The primary key in my tables is `name`. – user9371654 Apr 24 '19 at 18:56

1 Answers1

1

You use ON DUPLICATE KEY UPDATE the same way whether the new values are coming from SELECT or VALUES.

INSERT INTO `myscheme1`.`table1`
(`table1`.`name`, `table1`.`id`,  `table1`.`history`)
SELECT `table2`.`name`, `table2`.`id`, `table2`.`history`
FROM `myscheme2`.`table2`
ON DUPLICATE KEY UPDATE id = VALUES(id), history = VALUES(history);

If table1 has the same name as a row in table2, the other columns will be copied into that row.

You don't need to include the name column in ON DUPLICATE KEY. It already has the value from the other table, since that's what makes it a duplicate.

Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Thanks. I have so many fields in each row. Isn't there a command like `UPDATE` that will automatically update the record? – user9371654 Apr 24 '19 at 19:04
  • Unfortunately, there isn't. You could create dynamic SQL in a stored procedure that gets all the columns from `INFORMATION_SCHEMA.COLUMNS`. – Barmar Apr 24 '19 at 19:05
  • Just to be sure, using `UPDATE` when loading data from a file, will update the whole record (not only the duplicated field or key?) Am I right? – user9371654 Apr 24 '19 at 19:06
  • You mean `LOAD DATA INFILE`? It's like `INSERT`, not `UPDATE`. It doesn't have a way to update records directly. If you want to do that, you load into a temporary table, then use `INSERT ... SELECT ... FROM temporary_table ON DUPLICATE KEY UPDATE`. – Barmar Apr 24 '19 at 19:08
  • See https://stackoverflow.com/questions/15271202/mysql-load-data-infile-with-on-duplicate-key-update – Barmar Apr 24 '19 at 19:08
  • this answer https://stackoverflow.com/questions/15271202/mysql-load-data-infile-with-on-duplicate-key-update seems unnecessarly complex to me. Plz check my short command. do you see anything wrong? – user9371654 Apr 24 '19 at 19:27
  • This is the syntax I used to load data from a file and UPDATE the WHOLE record automatically if a duplicate is found in the primary key: `LOAD DATA INFILE '/var/lib/mysql-files/test.csv' REPLACE INTO TABLE 'test'.'t1'` fields terminated BY ','; I have tested it and it works as I described. There is not need to do the lengthy process. Can you please confirm or notify me if I'm wrong. It is extremely sensitive and important to me. – user9371654 Apr 24 '19 at 19:31
  • I used REPLACE not UPDATE. Sorry about confusion. With REPLACE I do not get error. It REPLACES duplicate rows automatically. – user9371654 Apr 24 '19 at 19:32
  • As mentioned in the other question, `REPLACE INTO TABLE` works by deleting the old row and inserting a new row. This is simpler, but it has extra overhead. If you use `AUTO_INCREMENT`, all these columns will change. – Barmar Apr 24 '19 at 19:33
  • But you can use it if it fits your needs. – Barmar Apr 24 '19 at 19:34
  • If it will INSERT non-duplicates, and will delete the old and update with the new if found duplicate, this is what I need. Sigh! – user9371654 Apr 24 '19 at 19:40