match value against value in other table

[RFH Reseller: Rappie]

Hi Guys,

I am struggleing with the following. I am haveing a kind of a lotery, people recieved a unique code that they need to submit on a website. Unforunatly I recieved the list with unique codes after the lotery started from the printer/publisher. So I couldn't check if the entered code actually exist from the start. That what I want do now.

I have two tables. One table with all the participants and the code they submited. And one thable with just all existing codes.

Table 1
+--------+---------+--------+--------+
| ID     |   name  |  code  | valid  |
+--------+---------+--------+--------+
| 10000  | John    | H3Jk4t |  true  |
| 10001  | Eric    | A8JBsr |  true  |
| 10002  | Sander  | H1HFzd |  false |
| 10003  | Will    | P7LS3w |  true  |
| 10004  | Denis   | Q4PF4q |  true  |
| 10005  | Pete    | H9BQ5d |  false |
| 10006  | Tom     | W3NG7g |  true  |
| 10007  | Valarie | N5EF4l |  true  |
| 10008  | Saskia  | S2BN8f |  true  |
| 10009  | Patrick | C9KE2v |  true  |
+--------+---------+--------+--------+

Table 2
 
+--------+
|  code  |
+--------+
| H3Jk4t |
| A8JBsr |
| H1HFzd |
| P7LS3w |
| Q4PF4q |
| H9BQ5d |
| W3NG7g |
| N5EF4l |
| S2BN8f |
| C9KE2v |
+--------+

I want the check if the codes in table 1 exist in table 2, if they do not exist I want the value in colm valid to be false.

Can I do this with a LEFT JOIN in a update statement?

[Tanzy]

Depending on how many codes you are talking about, and if it is going to be a one off I would do this

UPDATE Table1 t1
SET valid = EXISTS(SELECT t2.code FROM Table1 t2 WHERE t2.code = t1.code)

Note that I have not checked this, however, it should give you an idea

[RFH Customer: webb0]

How about a sub query - something like this might work

UPDATE Table1
set valid = true
WHERE id IN
(SELECT CODE from Table2)

I havent tested either so please do a backup first !

[RFH Reseller: Rappie]

Thnx guys,

I am trying both query's, but they take an awful lot of time to process. I am already waiting 20 minuts on the result using MySQL query browser.

There are over 15.000 records in table 1 and 200.000 records in table 2. So I imagine it will take a while.

[Staff: Warren Ashcroft]

Are you working on ALUMINIUM by any chance? ALUMINIUM started to timeout due to excessive load at about that time...

In any case, no query of this type should take that long - the query is either wrong or the tables need indexes to support the query.

[RFH Reseller: Rappie]

Yeah, that was me on ALUMINIUM . I noticed ALUMINIUM started to struggle so I moved tesing to my desktop. (Should have done so in the first place).

I am not really sure, but isn't indexing only useful for SELECT statments? I thought it doesnt provide any benefits for UPDATE querys. Please correct me if I am wrong though.

I am getting close. The two flowing querys work in showing all the records where the code matches in the two tables. But they both have different output, I cant discover what the differences is

SELECT `code` FROM `table1` WHERE EXISTS(SELECT `code` FROM `table2`)

SELECT * FROM `table1` A, `table2` B
WHERE B.`code` =  A.`code`

So I am searching for a query that only shows the records in table1 that contain codes who do not match in table2.

I thuoght that would be easy, just change WHERE EXISTS to WHERE NOT EXISTS or WHERE B.`code` = A.`code` in to WHERE B.`code` != A.`code`. But that doenst seem to work.

Any one any pointers?

[Staff: Warren Ashcroft]

I am not really sure, but isn't indexing only useful for SELECT statments?

Not necessarily, they will assist any query that has a WHERE clause or a JOIN on columns .

[RFH Reseller: askjim]

How about a sub query - something like this might work

UPDATE Table1
set valid = true
WHERE id IN
(SELECT CODE from Table2)

I havent tested either so please do a backup first !

The above is good approach and good advice regarding backup.

Add indexes on both tables on the code columns.

[RFH Reseller: Rappie]

Webb0's query really helped me out, I am using something similair.

UPDATE Table1 set valid = 'false'
WHERE active = 'true' AND code NOT IN
(SELECT code FROM Table2)

Adding indexes really helped too. One more question though, should I index the `valid` colum as well? Because of the use in the WHERE clause.

[Staff: Warren Ashcroft]

Webb0's query really helped me out, I am using something similair.

UPDATE Table1 set valid = 'false'
WHERE active = 'true' AND code NOT IN
(SELECT code FROM Table2)

Adding indexes really helped too. One more question though, should I index the `valid` colum as well? Because of the use in the WHERE clause.

I don't see the valid column in the where clause?