[Help Please!] Compare 2 tables 4 rows with condition

madkitty · #1 (**permalink**) 05-23-11, 05:39

I have two tables of integers in which I need to compare two columns as follow:
table_1
col1 | col2 | col3 |

table 2
col1 | col2 | col3 |

I need to copy the content of table_2.col3 INTO table_1.col3 If I have the following condition :
table_1.col1 >= table_2.col1
AND IF table_1.col2 <= table_2.col2

This is what I wrote on MySQL:

Code:

mysql> INSERT INTO table_1.col3 
-> SELECT * FROM table_2.col3 WHERE 
-> table_1.col1 >= table_2.col1 
-> AND table_1.col2 <= table_2.col2;

The 1st issue I have is ... This doesn't work at all.. it returns that table_1.col3 doesn't exist (even thou it does exit..)

The 2nd issue is that MySQL would compare line 01 to line 01, then line 02 to line 02 for each table. Optimally I need it to compare line 01 of table_1 to each line in table_2 and then, go to line 02, compare it to each line in table_2 and so on..Is it possible?

r937 · #2 (**permalink**) 05-23-11, 07:30

Code:

UPDATE table_1 
INNER
  JOIN table_2
    ON table_2.col1 <= table_1.col1
   AND table_2.col2 >= table_1.col2
   SET table_1.col3 = table_2.col3

madkitty · #3 (**permalink**) 05-24-11, 04:13

Hi,

MySQL can compare row by row two different tables (let's say table1 and table2). Is it possible to make it compare row 01 from table1 to the EVERY rows in table2, then compare row02 from table1 to EVERY rows in table2 and so on...?

madkitty · #4 (**permalink**) 05-24-11, 04:19

Thanks but .. it doesn't work on my computer. It returns an error "Lock wait timeout exceeded"

This one works well, it displays on screen every matching element from table1.col3. Still I need to populate table1 from these elements.

Code:

mysql> SELECT table2.col3 FROM table1, table2
    -> WHERE (table1.col1<= table2.col1
    -> AND table1.col2>= table2.col2);

How?...I do not know

r937 · #5 (**permalink**) 05-24-11, 04:36

Quote:

Originally Posted by madkitty

WHERE table_1.col1 >= table_2.col1
AND table_1.col2 <= table_2.col2

Quote:

Originally Posted by madkitty

WHERE (table1.col1 <= table2.col1
AND table1.col2 >= table2.col2)

can't make up your mind, can you

r937 · #6 (**permalink**) 05-24-11, 04:38

Quote:

Originally Posted by madkitty

Is it possible ...

of course it is possible

madkitty · #7 (**permalink**) 05-24-11, 09:14

haha Thanks & Sorry the 1st statement is the correct one

but still i can't get this to work on my computer.. The table1 is not updated, how can I fix this?

Also, do you know how can I compare each row in table1 to every rows in table2 ?