How to avoid executing mysql queries in nested loop?

Question

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:

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

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:

    email: email1(at)gmail.com
    list_id: 1
    disclaimer1: 0
    disclaimer2: 0
    gender: M
    dob: 1975-05-02
    region: Portugal

and

    email: email2(at)gmail.com
    list_id: 33
    disclaimer1: 1
    disclaimer2: 0
    gender: M
    dob: 1975-05-02
    region: Portugal

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

How will you provide the correct version? I mean if for a specific email, there are 10 lists it is subscribed in, which one should be chosen (and its data copied to the other 9)? — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Sep 27, 2012 at 22:45
is this a one-time run or is it something that will have to be done continuously? — solidau
– solidau, Commented Sep 27, 2012 at 22:46
this question screams for a groupwise max solution in your update query, depending on which version of the data you want to use to update the other rows. (what ypercube already asked for clarification) — Kaii
– Kaii, Commented Sep 27, 2012 at 23:18
@ypercube For example if I'm comparing list_id 1 and list_id 33, I would like to keep the values from list_id 1. — mjpramos
– mjpramos, Commented Sep 28, 2012 at 12:36
@kaii In the groupwise max solution I don't understand what field would be used to MAX(). — mjpramos
– mjpramos, Commented Sep 28, 2012 at 12:44

ngoa · Accepted Answer · 2012-09-27 22:28:56Z

3

@bigman what you have posted seems to be right but I guess the question is on how to change all the other data similar to list_id: 1 with the same email.

UPDATE my_table t,
 (
    SELECT
        email,
        disclaimer1,
        disclaimer2,
        gender,
        dob,
        region
    FROM
        my_table
    WHERE
        list_id = 1
) t1
SET 
 t.disclaimer1 = t1.disclaimer1,
 t.gender = t1.gender,
 t.dob = t1.dob,
 t.region = t1.region
WHERE
   t.email = t1.email

I guess this should work for all the entries that would have the list_id: 1 email address.

answered Sep 27, 2012 at 22:28

ngoa

8305 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

wesside Over a year ago

Ya, don't copy my answer. If you're going to put in a bad answer don't bother. Both of the emails are the same, it will update the first one, then get to the second record and reupdate it back with the old information. He would need an additional flag to figure out which ones need to be updated from the other, otherwise its a cluster F. Ending up with uncertain and VERY incorrect data.

Kaii Over a year ago

@bigman actually this answer is more correct than yours. And what you critic is just not true, it would update all rows with the data from the row with list_id = 1 instead of the data of a random row mysql decides to select, which depends on the version and engine used. as the behaviour for your DISTINCT solution is undefined.

wesside Over a year ago

@kaii, he's not trying to update 61,000 rows with the same data as id 1. He wants every email to be updated down the line, automagically.

wesside Over a year ago

@Kaii thats an example. He has multiple cirumstances.

Kaii Over a year ago

@bigman no he isn't updating all 61,000 rows at once, and this query doesn't. It updates all rows that have the same email address as the one with list_id = 1 with the data of that specific row. Assumed that not all 61,000 rows have the same email address (which is a safe assumption), this query might actually do what the OP requests-

|

Collectives™ on Stack Overflow

How to avoid executing mysql queries in nested loop?

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related