Comparing two fields in two different tables - update on match

Databases
1

Here is my problem, in a simplified manner.

Table1

id
userid
model
serial
returned

Table2

id
model
serial

I need to run a query that updates “table 1 > returned” to a value of 1 when it’s “model & serial” matches the “model & serial” found in table 2. The company involved does not use the serial number as a unique value - it uses the combination of both. When a product is returned to them, they upload nothing more than the model and serial to “table 2”.

With a single field I would have no problems doing this, but I am questioning the proper procedure when I need to compare both fields.

For the record: Table1 holds hundreds of thousands of records, and table2 gets about 500 per week.

Any suggestions?

2 
UPDATE Table1
INNER
  JOIN Table2
    ON Table2.model = Table1.model
   AND Table2.serial = Table1.serial
   SET Table1.returned = 1
3

It’s that simple? I need to brush up on my SQL :slight_smile:
Thanks - I’ll give it a go!

4

Thanks r937! Works great!

Quick question, that likely has a can of worms for an answer.

When I run this query through PHPMyAdmin it updates 500 records in about 4 minutes. During that time, the server is very responsive on the front end, and admin area. No problem bouncing around the site.

If I run the same query through a very, very simple PHP file - the update takes the same amount of time but consumes a lot more resources. I can’t even visit a page on the server while it updates.

What should I be looking at as a possible problem? Both the site and PHPMyAdmin use the same PHP config, and MySQL DB.

5

Do you have an index on model-serial in both tables?

6

There is an index on Model/Serial on Table1, but not table2.

7

and what happened when you added an index on model-serial in table2?

:slight_smile:

8

:slight_smile: I’ll give it a try.

But still, why would this query halt the system in it’s tracks in a standard PHP script, and not in PHPMyAdmin?

9

OK - I can see my last question is irrelevant.
I just ran a quick test.

I ran a similar query, but rather than update, I just selected (since the updates are all done for the day).

When only table one was indexed: 1739 records found in more than 5 minutes.
When both tables were indexed: 1739 records found in 0.0197 seconds

Lesson learned :slight_smile:

10

the best lessons are the ones you discover yourself :slight_smile:

11

hello r937,

Thank for the reply to the thread which i found useful but need more. This is how I used it but want to be sure. The tables there in are actual.

update rmc_raw_data
INNER
JOIN handover_main
ON handover_main.handover_time = rmc_raw_data.rmc_time
AND handover_main.handover_date = rmc_raw_data.rmc_date
SET rmc_raw_data.handover = “Handover”;

the query returns one result which i manually checked and is correct. since the records are numerous, i need a way to check if the query is working correctly.

However, the query searches for for date and time in one table and match them with date and time in another table and if there is a match, it inserts handover in one of the tables.

I need a way to validate the result of the query. Please suggest. Many thanks.

Paul

12

use the join conditions and WHERE conditions in a SELECT statement, to identify which rows will be updated

then run the update

then run the SELECT again and do spot checks to make sure the right ones got updated

simple, innit :slight_smile:

13

Thanks.