0

have 2 tables one countryCapCurLang another CountryBasicInfo both have common Varchar(130) Null column COUNTRY_NAME. which include country names. both have alpha_2 columns with values in CountryBasicInfo like AF for Afghanistan etc.i wish to add alpha_2 values to countryCapCurLang table table countryCapCurLang :

| id | alpha_2 |COUNTRY_NAME|
| -- | ------- | ---------- |
|  1 | Null    |Afghanistan |
|  2 | Null    |France      |

and table CountryBasicInfo :

| id | alpha_2 |COUNTRY_NAME|
| -- | ------- | ---------- |
|  1 | AF      |Afghanistan |
|  2 | FR      |France      |

updated using

update countryCapCurLang 
left join CountryBasicInfo using(COUNTRY_NAME) 
set countryCapCurLang.alpha_2=(select * from  CountryBasicInfo where  countryCapCurLang.Country_NAME like '%'+CountryBasicInfo.COUNTRY_NAME+'%');

which gave errors on afghanistan, russia etc and did zero updation. if i donot use like and simply put = sign, it updates many but leaves 24 out of 195 countries.

select * from CountryBasicInfo where country_name='Afghanistan'

it gave no result then

select * 
from CountryBasicInfo 
where country_name like '%'+Afghanistan'+'%'

gave a row. i thought its due to some space etc in entry. hence tried a lot of few are under.

UPDATE `countrylangcapcur` 
SET `countrylangcapcur`.`alpha_2`=( select `alpha_2` from `countrybasicinfo` LEFT JOIN countrylangcapcur ON `countrylangcapcur`.`COUNTRY_NAME` like '%'+`countrybasicinfo`.`COUNTRY_NAME`+'%');

` then i tried

update `countrylangcapcur` 
INNER JOIN `countrybasicinfo` on `countrylangcapcur`.`country_name` like '%'+`countrybasicinfo`.`country_name`+'%' 
SET `countrylangcapcur`.`alpha_2`=( select `alpha_2` from `countrybasicinfo` WHERE `countrylangcapcur`.`country_name` like '%'+`countrybasicinfo`.`country_name`+'%');`
P.Salmon
  • 17,104
  • 2
  • 12
  • 19
DEV KAMAL
  • 1
  • 4
  • +is an arithmetic operator in mysql , and your joins look like sql-server syntax please add the result of select version() – P.Salmon Jan 23 '23 at 08:48

1 Answers1

0

To update a column in a table based on another table's column, where there is a common column in both of the tables I would try updating your query to:

UPDATE countryCapCurLang 
SET alpha_2 = (SELECT alpha_2 FROM CountryBasicInfo WHERE CountryBasicInfo.country_name = countryCapCurLang.country_name);

There is no need to perform joins inside of your inner select query.

bosowski
  • 124
  • 6
  • It was done at last when nothing worked then I tried even those old ways of Access. I know it's very lengthy. They worked on few values but left many felds un filled even – DEV KAMAL Jan 23 '23 at 03:16
  • Does my solution work for you? If not, you probably have some inconsistencies in your data between the two tables. – bosowski Jan 23 '23 at 03:24
  • i had already tried that . that command updated the countryCapCurLang table column alpha_2 which represents country codes. but out of 195 countries listed names among both tables, 24 were not entered. i tried concat(), in(), trim() functions thought that e.g. Afghanistan in table CountryBasicInfo has spaces. then i have to use LIKE .but it gives errors. problem is in data?? both tables have same data. In case it be a large data what will i do? recent quests about that i gone thru but remain almost unsolved. thaks for editing I didnot know how to. regards to you – DEV KAMAL Jan 23 '23 at 09:19
  • You have to examine the data and figure out where the mismatch is, or what the problem is. Run the update query I posted and examine the entries that have not been updated - there have to be differences in the data for some of the entries to not update, or perhaps the column that you're trying to update from is null? If you can't figure it out, post the data from some of the rows that aren't working and the corresponding rows from the other table. – bosowski Jan 23 '23 at 12:30
  • I can send you the files but do not know how to . Can i share from my google drive?? All things I already tried mismstch is in country basic info entries only few need like to search others do with equal to sign. But why? Is there any bug in mysql – DEV KAMAL Jan 23 '23 at 13:34
  • i opened the exported countryBasicInfo table with hex editor online [enter link description here][1] [1]: https://hexed.it i seen that there are unwanted ansi codes lying against those values which were left in uodating. like `('#?Afghanistan',` the hash is written for example only otherwise it was different ansi font coud not write here . then i opened it in vscode . same thing is there. repaired it. and its working fine now – DEV KAMAL Jan 25 '23 at 04:51
  • here is the link that describes the issue to much extent https://stackoverflow.com/questions/3891331/find-non-ascii-characters-in-varchar-columns-using-sql-server – DEV KAMAL Jan 25 '23 at 05:03