0

I have 2 tables tblAddress [(Address nvarchar(500),District nvarchar(50)] and tblDisrtict [(District nvarhchar(50)].

Some sample data:

tblAddress:

HON MANI # no St.Chom Chao Trapeang Thloeng Chaom Chau Dangkao  N/A
CHENG HORN # 18 St 12 Tuek L'ak Muoy Tuol Kouk Phnom Penh   N/A
KHANG HONG # 9 St 50 Boeng Kak Muoy Tuol Kouk Phnom Penh    N/A
SANG MOY #10A St.no Kakab Dangkao Phnom Penh    N/A
NONG NAT #815 St No Stueng Mean Chey Mean Chey Phnom Penh   N/A
CHEA MONG #No St lum Phnom Penh Thmei Phnom Penh Thmei Ruessei Kaev     N/A
SOM KOY # 2E st 16 Phum5 Phsar Kandal Muoy Doun Penh Phnom Penh N/A
LE VANLYDA # 32 st 7 Kdei Chas Bak Khaeng Russei Keo Phnom Penh N/A
LAY NA # 35 st 70 Phum10 Boeng Salang Tuol Kouk Phnom Penh  N/A

tblDistrict:

Chamkar Mon
Doun Penh
Prampir Meakkakra
Tuol Kouk
Dangkao
Mean Chey
Russey Keo
Saensokh
Pur SenChey
Chraoy Chongvar
Praek Pnov
Chbar Ampov

I'd like to have the result as below

  1. delete Name (capital letter) from Address

  2. Update tblAddress column: District (N/A) with column: Address content district from tblDistrict.

    UPDATE tblAddress 
SET District = @district 
WHERE '%' + Address + '%' = @district

    but I don't know how to loop through tblDistrict to pass data to @district.

Any help please.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sokea
  • 327
  • 5
  • 19
  • Is this a one time deal or a reoccurring requirement? – Erik Philips Mar 13 '16 at 05:30
  • @Erik Philips this is just one time deal, and data is more than 100K. – Sokea Mar 13 '16 at 05:32
  • Can you explain how `tblDistrict` linked to `tblAddress`? – har07 Mar 13 '16 at 05:37
  • This might help: [SQL update query using joins](http://stackoverflow.com/questions/982919/sql-update-query-using-joins) – har07 Mar 13 '16 at 05:39
  • @har07 tblDistrict is referenced to tblAddress, the Address column content district word. thay why i'd to update column District in tblAddress with data from tblDistrict. – Sokea Mar 13 '16 at 05:40

1 Answers1

2

If I understand the relation between the two tables correctly, you can achieve that better using JOIN instead of looping :

UPDATE
    tblAddress
SET
    District = B.District
FROM
    tblAddress A
    JOIN
    tblDistrict B ON A.Address LIKE '%' + B.District + '%'
WHERE
    A.District = 'N/A'
har07
  • 88,338
  • 12
  • 84
  • 137
  • thanks. btw. how to remove Name (capital letter) from Address? – Sokea Mar 13 '16 at 06:06
  • how about update the column with the result of [substring after](http://stackoverflow.com/questions/9260044/how-to-split-a-string-after-specific-character-in-sql-server-and-update-this-val) character `#` – har07 Mar 13 '16 at 06:11
  • the Name is specified by all capital letter, some doesn't has # sign for sub-string. so any way to find double capital? – Sokea Mar 13 '16 at 06:31