Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2009
    Posts
    4

    Unanswered: compare 2 columns with wildcard

    Hi all,
    I have 2 tables.i.e. Table1 and Table2.

    Table1
    ---------------
    Code
    ---------------
    a_b
    c_d
    e_f
    f_g

    Table2
    ------------
    Msg
    ------------
    s_c_d_1
    r_a_b_o
    t_f_g_k
    A_e_f_D

    I want to compare the two columns of the respective Tables and get the output as..

    Table2
    ----------------------
    Msg || Code
    ---------------------
    s_c_d_1 || c_d
    r_a_b_o || a_b
    t_f_g_k || f_g
    A_e_f_D || e_f

    That is i want to check which code is for which Msg and then update the Table2.code column.

    I tried to implement this by...

    UPDATE Table2.Code SET Table2.Code =(SELECT Table1.Code, Table2.Msg FROM Table1, Table2 WHERE Table1.Code LIKE Table2.Msg);

    I even tried by

    UPDATE Table2.Code SET Table2.Code =(SELECT Table1.Code, Table2.Msg FROM Table1, Table2 WHERE Table1.Code LIKE "'%' + Table2.Msg + '%'");

    But still its not working...

    Please guide me in this....
    Thanking you!!!

  2. #2
    Join Date
    Jun 2009
    Posts
    66
    UPDATE [table2] SET [table2].code = a.code FROM [table1] a, [table2] b WHERE a.Code LIKE "'%" + b.Msg + "%'" AND [table2].code = a.code;

  3. #3
    Join Date
    Jun 2009
    Posts
    4
    I tried implementing your suggestion.. but it shows an error
    it shows an SQL syntax error...
    Please help

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    concatenation in mysql is ~not~ performed with that horrid plus sign

    saldy, neither is it performed with the SQL standard double pipes (||) concatenation operator

    in mysql, you need to use the CONCAT function
    Code:
    SELECT Table2.Msg
         , Table1.Code
      FROM Table1
    INNER
      JOIN Table2
        ON Table2.Msg LIKE CONCAT('%',Table1.code,'%')
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2009
    Posts
    4
    When i tried implementing your suggestion, it shows an error stating that
    !1066 Not unique table/ table: 'Table2'

    What does this mean??
    please help

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by me8042
    What does this mean??
    it means you've made some sort of error in naming your tables

    surely you did not actually name it "table2"

    could you show your real query please? the one that produced the error
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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