CODEWITHSUNDEEP
mysql
Mike Hermary
Mike Hermary

Reputation: 376

MySQL update column with value from a different table

I have two tables with the following structure and example content. Table one has the membership_no set to the correct values, but table two has some incorrect values in the membership_no column. I am needing to query both tables and check to see when the membership_no values are not equal, then update table two's membership_no column with the value from table one.

Table One:

id     membership_no
====================
800960 800960
800965 800965

Table Two:

id     membership_no
====================
800960 800970
800965 800975

Update query so far. It is not catching all of the incorrect values from table two.

UPDATE
tabletwo
    INNER JOIN
    tableone ON tabletwo.id = tableone.id
SET 
    tabletwo.membership_no = tableone.membership_no;

EDIT: Including SHOW CREATE and SELECT queries for unmatched membership_no column values.

Table One SHOW:

CREATE TABLE `n2z7m3_kiduka_accounts_j15` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`membership_no` int(11) NOT NULL,
...
`membershipyear` varchar(100) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `user_id` (`user_id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=800987 DEFAULT CHARSET=utf8

Table Two SHOW:

CREATE TABLE `n2z7m3_kiduka_accounts` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`membership_no` int(11) NOT NULL,
...
`membershipyear` varchar(100) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `user_id` (`user_id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=801072 DEFAULT CHARSET=utf8

SELECT query for unmatched membership_no column values:

SELECT 
    u.name,
    a.membership_no as 'Joomla 1.5 accounts table', 
    j.membership_no as 'Joomla 3.0 accounts table' 
FROM 
    n2z7m3_kiduka_accounts_j15 AS a 
    INNER JOIN n2z7m3_users AS u ON a.user_id = u.id
    INNER JOIN n2z7m3_kiduka_accounts AS j ON a.user_id = j.membership_no 
    and a.membership_no != j.membership_no
ORDER BY u.name;

Upvotes: 0

Views: 166

Answers (2)

Martin
Martin

Reputation: 22760

While Tim's Answer is perfectly valid, another variation is to add the filter qualifier to the ON clause such that: 

UPDATE tabletwo
INNER JOIN
tableone ON tabletwo.id = tableone.id AND tabletwo.membership_no <> tableone.membership_no
SET 
tabletwo.membership_no = tableone.membership_no;

This means that you don't have the WHERE filter so it will process all rows, but will act on only those with differing membership_no values. Because it is an INNER JOIN the results will be both tables or no tables (Skipped/NULL result).

EDIT:

If you suspect you have a problem still, what does the MySQL command respond, do you have a specific error notice? With 80k columns, it may take a while for the comand to actually process , so are you giving the command time to complete or is PHP or the system causing the command to abort due to execution time expiry? (Update your execution time on PHP and MySQL and rerun query just to see if that causes it to complete successfully?)

Suggestion

As another sggestion I think your UNIQUE KEY should also be your AI key so for both tables:

DROP INDEX `user_id` ON <table> #removes the current unique index.

then

CREATE UNIQUE INDEX `id` ON <table> #addes unique index to the A_I column.

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520938

You just need to add a WHERE clause:

UPDATE
    tabletwo
INNER JOIN
    tableone
    ON tabletwo.id = tableone.id
SET 
    tabletwo.membership_no = tableone.membership_no
WHERE tabletwo.membership_no <> tableone.membership_no

Upvotes: 1

Related Questions