I have 2 tables in the same DB in phpmyadmin. Table ‘csv’ is where I imported data in order to clean it and format it. ‘client’ is a table/back-end connected to my forms UI. I am trying to INSERT selected columns from ‘csv’ INTO ‘client’. I have checked that they are both in MyISAM format. And I am not including any PK columns. The ‘client’ table has a PK column that is generated by my front-end code. But I cleared that table of any test data so there can’t be any duplicates.
Here is the sql I’m using:
INSERT INTO client
(`first_name`, `last_name`, `street_address`, `city`, `zipcode`, `cell`)
SELECT `CSVFirst` as `first_name`,`CSVLast` as `last_name`, `CSVAddress` as `street_address`, `CSVCity` as `city`, `CSVZip` as `zipcode`, `CSVPhone` as `cell`
FROM csv
any help is greatly appreciated.
2
Answers
It turns out I needed an UPDATE not an INSERT. The Duplicate key message was because when INSERTing, it tries to add rows after the last existing row. which all have NULL pk values, hence the duplictae. So to add rows into an existing set of rows, use UPDATE:
JOIN table1 ON table2.
first_name
= table1.table1First
AND table2.street_address
= table1.table1Address
AND table2.cell
= table1.table1Phone
SETday
=table1day
WHERE 1Try
INSERT IGNORE INTO
read more about it here https://www.tutorialspoint.com/mysql/mysql-handling-duplicates.htm#:~:text=Use%20the%20INSERT%20IGNORE%20command,silently%20without%20generating%20an%20error.