Ask for a quick update method for records that containing duplicate values

37
January 12, 2019, at 01:40 AM

I have a table with millions of records, but each object could have multiple columns that share the same values while some other columns have different values.

Example: A person has the same email address and the same mobile number, but has different transaction histories, which will generate multiple records in the table.

I want to update the table based on the email address and mobile number. Since multiple records have the same email address and mobile number, the updated column will get the same result.

My code:

UPDATE person 
SET info = concat (email, mobile);

But this code will have a full table scale, and update the records one by one, which is very slow, because the table size is large.

I wonder if there is a quick way to update the field, ie using aggregation method?

FYI, I work on MYSQL 8.0 workbench.

Answer 1

This Statement:

I want to update the table based on the email address and mobile number. Since multiple records have the same email address and mobile number.

It seems like you have Unnormalized table

If you want to update with the same email and the same mobile number. Your update adding a bunch of same records repeatedly and its allocate a lot of space to your Database with redundant data.

I suggest why dont you create another table just for this?

For example:

You have a record of

PersoneID1 email1 Mobile1
PersoneID1 email1 mobile1
PersoneID1 email1 mobile1
PersoneID1 email1 mobile1

Instead of creating four email1 Mobile1

In your newly created table:

it just look like only one record something like:

PersoneID1 email1 Mobile1
READ ALSO
How to enter username in form and then search in database to produce if else statement

How to enter username in form and then search in database to produce if else statement

How would I get the $UN Variable to be set by the input provided by the formThen use the variable to search in the database to provide a users details

20
WHERE IN clause with multiple values

WHERE IN clause with multiple values

I'm trying to select items from a table when any values from a list appear in an array in the table

21
Best practice for storing HTML in mysql DB

Best practice for storing HTML in mysql DB

I'm working on a mini blog which allows users to add a post using a WYSIWYG editor to the site therefore I will be storing this post in my DB

10
MySql error when trying to combine two tables

MySql error when trying to combine two tables

Hi i am getting the error below when i am tryin to combine two tables, Post and User

42