How to avoid executing mysql queries in nested loop?

Databases
1

Hi,

I’m having a bit of a problem with a task I was assigned.

I have this old phpmynewsletter database (MySQL backend).

Basically all emails are saved in a table with this structure:

[INDENT][INDENT]email varchar(255)
list_id int(11)
hash varchar(255)
disclaimer1 tinyint(1)
disclaimer2 tinyint(1)
gender char(1)
dob date
region varchar(50)[/INDENT][/INDENT]

So one same email can be present several times in the table, this is, subscribed in different newsletters (identified by list_id)

What I need to do is update the records so the fields disclaimer1, disclaimer2, gender, dob and region for a specific list_id are the same for the same email registered with other list_id’s.

For example:

[INDENT][INDENT]email: mjprpub(at)gmail.com
list_id: 1
disclaimer1: 0
disclaimer2: 0
gender: M
dob: 1975-05-02
region: Portugal[/INDENT][/INDENT]

and

[INDENT][INDENT]email: mjprpub(at)gmail.com
list_id: 33
disclaimer1: 1
disclaimer2: 0
gender: M
dob: 1975-05-02
region: Portugal[/INDENT][/INDENT]

How do I update the record with list_id 33 with the correct values from record with list_id 1?

I’m using PHP and I believe that running updates queries in loops is bad practise. The table has around 610000 records.

Hope somebody can give me some insight on how to accomplish this task.

Thanks,
Mário

2

how do you know it is values in listid 1 that are correct and not the values in listid 33? what happens if there are different values for list 23 and 55 for the same user? what record do you use then?

3

Hi guelphdad!

List_id 1 is the oldest.
I’ll just have to consider it the correct one and use it’s values to update the others.