CODEWITHSUNDEEP
phpmysqljoincompare
Anonymous
Anonymous

Reputation: 13

Update rows on one MySQL table where rows with a matching column exist on another table?

I have two tables ("z" and "z_ao"). "z" contains all the zip codes in the U.S. along with other needed information. "z_ao" contains only certain zip codes. I want to set the "ls" column to "1" in every row in "z" where "zip" in "z" matches "zip" in "z_ao".

I have been reading all day to determine the "right" way of doing this, which I think would make use of JOIN but got frustratingly confused and in this case this was a one-time operation so I finally did things the following clumsy, query-intensive way:

$z = mysql_query("SELECT zip_code FROM z");
while($z_row = mysql_fetch_row($z)) {
  $zao = mysql_query("SELECT zip FROM z_ao WHERE zip='$z_row[0]'");
  $zao_num=mysql_numrows($zao);
  if (mysql_numrows($zao) == 1) {
    mysql_query("UPDATE z SET ls = '1' WHERE zip_code='$z_row[0]'");
  }
}

I'm sorry to ask for help because I know the answer is out there but for future reference, I would like to understand what would have been the proper way to do this and I think I need to see an exact example.

Thank you.

Upvotes: 1

Views: 530

Answers (2)

Randy
Randy

Reputation: 16677

how about IN...

update z set ls = 1 where zip_code IN ( select zip_code from z_ao );

Upvotes: 2

Bill Karwin
Bill Karwin

Reputation: 562681

Try a multi-table UPDATE:

UPDATE z INNER JOIN z_ao ON z.zip_code = z_ao.zip SET z.ls='1'

You don't need to do any counts or tests or loops. The JOIN matches rows for you, and automatically excludes zip codes that have no match in your z_ao table.

Upvotes: 5

Related Questions