CODEWITHSUNDEEP
mysqlsql
Vivek Goel
Vivek Goel

Reputation: 24140

Mapping two mysql table based on email address

I have two mysql table

Table 1 

id email  other fields

Table 2

id email other fields

where id for table1 is auto increment mysql column. and in table 2 id is only primary key not NULL .

some how the id for table 2 had gone wrong. Now both tables are having some records and I want to make id in table 2 same as table 1.

let's take an example

Table 1 have 

1 [email protected]
2 [email protected]

Table 2 have 

1 [email protected]
2 [email protected]

if I fire query 

update 
  table2 
set 
  id=(select id from table 1 where email="[email protected]") 
where 
  email = "[email protected]"

I will get error because table 2 is already having id value of 2. now how can I solve this problem ?

I want id of table2 same as table1 based on email.

Upvotes: 1

Views: 95

Answers (3)

Vishwanath Dalvi
Vishwanath Dalvi

Reputation: 36611

How about this :

update table2 set email=(
case
when email='[email protected]' Then '[email protected]'
when email ='[email protected]' Then '[email protected]'
end)

Upvotes: 1

Marco
Marco

Reputation: 57573

You can do what @rickythefox told you or you could reach your goal with two queries:

  1. Run your query adding to table2.id a fixed quantity to table1.id (e.g. 500.000)
  2. Update your table2 with another query subtracting fixed quantity

Upvotes: 0

Joe
Joe

Reputation: 15802

Presumably you're doing a one-off fix. Go into phpMyAdmin, or your favourite database editing tool and remove the PK constraint from table2. Fix your records with those queries, then re-add the PK constraint.

Upvotes: 1

Related Questions