phpMyAdmin: Update and add all rows of one table with another


#1

Hi

Im using MySQL/phpMyAdmin.

Ive got 2 identically structured tables. How do I update and add to all the records from one table with the other?

In the old table some rows have been added and some rows have been changed. All of these - together with all the other rows - need to be inserted into the new table.

Do I Empty the table first and then somehow insert the old records in there? I need everything to be identical - even the primary key ID.

Thanks


#2

do an INSERT with the ON DUPLICATE KEY UPDATE option


#3

When I click on a table and go to Export - what settings do I choose? Ive heard “truncate and insert” is what I need, but I dont know which settings to choose for this! (Sorry, Im new to this!)


#4

don’t be truncating anything. you’ll lose your data!

https://dev.mysql.com/doc/refman/8.0/en/truncate-table.html


#5

yeah, sorry, i can’t help you with phpmyadmin

my advice is for you to write the actual SQL query, e.g.

INSERT
  INTO newtable
     ( id
     , foo
     , bar )
SELECT id
     , foo
     , bar
  FROM oldtable
ON DUPLICATE KEY
UPDATE newtable.foo = oldtable.foo
     , newtable.bar = oldtable.bar       

you can, of course, paste and run your query in the SQL tab of phpmyadmin


#6

sorry didn’t read your statement saying that you needed to clear the second table. I can never remember whether it’s truncate or empty that resets the auto id. So i normally do both as it won’t do any harm and one of them will reset it.

First of all though take a backup of everything!!!

then export the data from table 1 into csv.

Once you done that go into the second table and truncate/empty the table and then use the ‘import’ function to import your data from a csv that you’ve created from table 1

think that should do it

BACKUP FIRST THOUGH!!


#7

Why?


#8

Why?

Dont ask. A long story. Something to do with priviledges.

I just need the new table to have EXACTLY the same data as the old table. Is there an easy way I can export the data from the old table and import into the new table?


#9

yes… INSERT with ON DUPLICATE KEY UPDATE option


#10

Just so you know, to give you the correct solution to the real problem we need to know what that real problem is. As is, you are asking how to solve your attempted solution which is most often not the right approach.


#11

What I think is the real problem is that there is a privilegdes issue. The site sits on a shared server and so I cant get access to view or edit priviledges.
With MariaDB theres seems to be an issue (correct me if Im wrong!) that if you imports tables (as I did from the old database) it imports the tables but not the priviledges assigned to them.
I already have 2 tables in my new db with the right priviledges. I just need them to contain exactly the same data as the old tables.