Align two mysql tables

Question

I have two databases. WordpressDB and JoomlaDB

WordpressDB has table db1_users JoomlaDB has table db2_users

thoose tables has the same fields names

id  | email | Login | Password

General database is WordpressDB. So i need all id in JoomlaDB make the same as id in WordpressDB where email field is the same.

So how to do that, it is about 10.000 entries?

Doing that will completely break Joomla. The ID is not there for you to muck with. It is a key used in all of the other tables. Instead of trying to join on id, try to join on email. — kainaw, Aug 18 '15 at 13:57

score 1 · Accepted Answer · edited Jul 09 '17 at 08:49

1

I don't know if this is right or not, but if you're looking for query to update ID in table joomla using data ID in table wordpress, I think you can use this :

update WordpressDB.tbl_user1 
set WordpressDB.tbl_user1.ID=(
select JoomlaDB.tbl_user2.ID from JoomlaDB.tbl_user2 
where JoomlaDB.tbl_user2.EMAIL=WordpressDB.tbl_user1.EMAIL
)

edited Jul 09 '17 at 08:49

Jack Bonneman

1,821
18
24

answered Aug 18 '15 at 14:10

Rifai

194
11

problem is that two different databases and has different user names and passwords – Dmitrij Holkin Aug 19 '15 at 07:30

score 1 · Answer 2 · edited May 23 '17 at 12:19

1

You can create a column wp_user_id in the table db2_users and create a migration script to map the user ids from WordpressDB db1_users to JoomlaDB where same mail exists.

In this way you wont affect how Joomla handles user ids and you can achieve your needs.

Just note that you may fall a victim of concurrent update since tow modules might update the same row at the same time, in this case you might need a locking mechanism when updating user rows on JoomlaDB.

edited May 23 '17 at 12:19

Community

1
1

answered Aug 18 '15 at 14:23

KAD

10,972
4
31
73

So how to look that migration script? – Dmitrij Holkin Aug 19 '15 at 07:37
You will need an UPDATE query with join between the two databases as long as they are on the same server, where you set the wp_user_id in the JoomlaDb based on the email matching when joining the two tables by email... You can benefit from this [link](http://stackoverflow.com/questions/8057565/mysql-update-statement-inner-join-tables) for more information about using updates with joins.. – KAD Aug 19 '15 at 07:48

Align two mysql tables

2 Answers2