AbsoluteBeginner AbsoluteBeginner -4 years ago 130
MySQL Question

Change the last two letters in a string in a column when entrie exists in another table

I have a table A that contains a few columns with entries. In column 1 is the campaign name which is unique in the table. Now I want to change the last two letters in a string in another column (let´s say column 2) when the campaign name exists in another table (Table B)(If the campaign name is not in table B, nothing should happen). The "new" two last letters are part of a string in column 2 (1 is also the campaign name) in table B.
-> How can I do this?

Here´s an example:

For example if the table A contained:

|col1 ||col2 |
+--------------------------------+---------------+------
|20161512_NL_Luxus_1_DE ||NewsletterDE |
|20161217_LC_YoBirthdayNo_A_CH ||LifecyclemailUK|
|20161512_NL_Luxus_1_DE ||BKMailCH |
|20161512_NL_SDT_4_DE ||LifecyclemailDE|
|20170117_SV_YoBirthdayYes_A_DE ||BKMailDE |
|20161512_NL_SDT_4_DE ||NewsletterFR |


And table B contained:

|col1 ||col2 |
+--------------------------------+---------------+------
|20161217_LC_YoBirthdayNo_A_CH ||LifecyclemailCH|
|20161512_NL_SDT_4_DE ||NewsletterDE |


I want a query that will change table A to:

|col1 ||col2 |
+--------------------------------+---------------+------
|20161512_NL_Luxus_1_DE ||NewsletterDE |
|20161217_LC_YoBirthdayNo_A_CH ||LifecyclemailCH|
|20161512_NL_Luxus_1_DE ||BKMailCH |
|20161512_NL_SDT_4_DE ||LifecyclemailDE|
|20170117_SV_YoBirthdayYes_A_DE ||BKMailDE |
|20161512_NL_SDT_4_DE ||NewsletterDE |


This what I got so far:

UPDATE table A INNER JOIN table B ON A.col1 = B.col1 SET A.col2 = CONCAT(SUBSTRING(A.col2), RIGHT(B.col2,2));


-> But this is not working correctly....

Thanks in advance!

Answer Source

I think this will do:

update tablea a 
join tableb b on a.col1 = b.col1
set a.col2 = concat(
               susbtr(a.col2, 1, length(a.col2) - 2),
               right(b.col2, 2)
             )
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download