compare 2 columns with wildcard

me8042 · #1 (**permalink**) 06-23-09, 11:37

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!!!

sqlguru · #2 (**permalink**) 06-23-09, 11:50

UPDATE [table2] SET [table2].code = a.code FROM [table1] a, [table2] b WHERE a.Code LIKE "'%" + b.Msg + "%'" AND [table2].code = a.code;

me8042 · #3 (**permalink**) 06-23-09, 12:19

I tried implementing your suggestion.. but it shows an error
it shows an SQL syntax error...
Please help

r937 · #4 (**permalink**) 06-23-09, 12:28

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,'%')

me8042 · #5 (**permalink**) 06-24-09, 05:45

When i tried implementing your suggestion, it shows an error stating that
!1066 Not unique table/ table: 'Table2'

What does this mean??
please help

r937 · #6 (**permalink**) 06-24-09, 06:00

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

Internet Services

Web Development

Digital Marketing

Consumer Tech