[RESOLVED] Help with UPDATE based on SELECT

fotofx

I have a table we will call table_b that has a field called "phone_number" and a field called "valid".

I have another table we will call table_a that also has a field called "phone_number"

There are about 1000 rows in table_a and 90000 in table_b

I want to UPDATE table_a SET valid="Y" WHERE (if number exists in table_b)

I am not sure of the syntax to use for the WHERE..

Any help is appreciated.

dalecosp

Does this help?

mysql> select * from foo;
+------+------+
| ab   | val  |
+------+------+
|  345 |    0 |
|   43 |    0 |
+------+------+
2 rows in set (0.00 sec)

mysql> select * from bar;
+------+------+
| cd   | val  |
+------+------+
|    1 |  345 |
|    2 | 4434 |
|    3 | 3345 |
|    4 |   42 |
+------+------+
4 rows in set (0.00 sec)

mysql> update foo set val=1 where ab in (select val from bar);
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from foo;
+------+------+
| ab   | val  |
+------+------+
|  345 |    1 |
|   42 |    0 |
+------+------+
2 rows in set (0.00 sec)

fotofx

I have the solution..

UPDATE table_b b
JOIN table_a a

ON a.phone_number = b.phone_number
SET b.valid = 'Y';

Thanks for the assistance.

dalecosp

You're welcome, of course.

If I may, you might be well-advised to seek advice from someone who is really a good
database programmer as to whether you're better off with joins or subqueries on this.

Of course, if this is a small dataset, it might not matter. However, as someone who (now)
has to worry everyday about how much CPU the SQL server is using when we even have
a dozen clients at once on the website, it might be well worth your time to think about.

Here's a couple resources that seem to discuss this a little bit:

http://onlamp.com/lpt/a/5220
http://stackoverflow.com/questions/141278/subqueries-vs-joins

Of course, one says subqueries are faster, and the other says joins are ... so we obviously
need someone smarter 😉

I'm sure that there are a number of factors that might affect this ... your RDBMS and its
optimizer, the size of the tables involved, etc., etc. I'm very much a generalist; I don't even
know a real DBA 🙁