MySql multiple databases. How to insert?

Question

I have 2 databases I want to update a field (insert?) uuid in table fe_accounts of database feDB, so that NULL i rplaced buy uuid from the table NameHistory in databse NameHistoryDB.

Database 1

Database : NameHistoryDB
Table: NameHistory

+--------------------------------------+------------------+---------------------+---------------------+
| uuid                                 | playername       | dateadded           | lastseen            |
+--------------------------------------+------------------+---------------------+---------------------+
| 0b9bb8dc-051c-4d8d-9029-96d969baece1 | xRazmuz          | 2014-04-09 08:40:47 | 2014-04-15 12:32:40 |
| a61925ba-8a69-464b-89c9-37d3d3fe9ce4 | Sleepyangel99    | 2014-04-09 08:45:09 | 2014-05-05 11:50:38 |
| 3983c0ef-3def-4f1b-ac15-335d6a4e1458 | pulle3           | 2014-04-09 08:51:37 | 2014-04-26 16:48:34 |
| 502f873d-7bf6-4187-bcf1-a5d78f574293 | zohan10          | 2014-04-09 09:04:37 | 2014-04-22 17:07:24 |

Database 2

Database : feDB
Table: fe_accounts

+------------------+---------+--------------------------------------+
| name             | money   | uuid                                 |
+------------------+---------+--------------------------------------+
| xRazmuz          |      74 | NULL                                 |
| Sleepyangel99    |    2650 | NULL                                 |
| pulle3           |    1000 | NULL                                 |
| zohan10          |    1079 | NULL                                 |

I want to insert the uuid from database 2 to database 1, so the result will be like this:

Database : feDB
Table: fe_accounts

+------------------+---------+--------------------------------------+
| name             | money   | uuid                                 |
+------------------+---------+--------------------------------------+
| xRazmuz          |      74 | 0b9bb8dc-051c-4d8d-9029-96d969baece1 |
| Sleepyangel99    |    2650 | a61925ba-8a69-464b-89c9-37d3d3fe9ce4 |
| pulle3           |    1000 | 3983c0ef-3def-4f1b-ac15-335d6a4e1458 |
| zohan10          |    1079 | 502f873d-7bf6-4187-bcf1-a5d78f574293 |

How do i do that?

use "database" (the name of your database) dev.mysql.com/doc/refman/5.0/en/use.html — nicolallias
– nicolallias, Commented May 6, 2014 at 12:28

Abhik Chakraborty · Accepted Answer · 2014-05-06 12:32:44Z

1

One way to do is to JOIN the tables from two databases and then update as

update feDB.fe_accounts db2
inner join NameHistoryDB.NameHistory db1 on db1.playername =  db2.name
set
db2.uuid = db1.uuid
where 
db2.uuid is null

NOTE : To run the above query you need proper permission i.e. mysql user having access to both DB.

Also it will match with the payername, there is no other common key to match so accurate update lies in having unique player names

answered May 6, 2014 at 12:32

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

avisheks · Accepted Answer · 2014-05-06 12:36:12Z

0

Run these two queries serially:

USE feDB;
UPDATE
fe_accounts 
JOIN NameHistoryDB.NameHistory t2 ON playername=name
SET uuid=NameHistoryDB.NameHistory.uuid
WHERE uuid IS NULL;

answered May 6, 2014 at 12:36

avisheks

1,1801 gold badge11 silver badges28 bronze badges

Collectives™ on Stack Overflow

MySql multiple databases. How to insert?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related