Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2011
    Posts
    18

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

    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?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2011
    Posts
    18

    Question General Question - Compare tables

    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...?

  4. #4
    Join Date
    Apr 2011
    Posts
    18
    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

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by madkitty View Post
    WHERE table_1.col1 >= table_2.col1
    AND table_1.col2 <= table_2.col2
    Quote Originally Posted by madkitty View Post
    WHERE (table1.col1 <= table2.col1
    AND table1.col2 >= table2.col2)
    can't make up your mind, can you

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by madkitty View Post
    Is it possible ...
    of course it is possible
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2011
    Posts
    18
    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 ?

  8. #8
    Join Date
    Apr 2011
    Posts
    18
    Thanks Can you tell me quickly how can I do that?

    I'm really clueless..

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by madkitty View Post
    Also, do you know how can I compare each row in table1 to every rows in table2 ?
    use a CROSS JOIN
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use a CROSS JOIN
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    threads merged
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Apr 2011
    Posts
    18
    threads merged
    I thought my eyesight was getting bad haha


    Thanks a bunch r937, luv ya XD

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •