Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 315
  • Last Modified:

SQL query help

Hello there,

I have this four table

Province
County(FK is Provicne table id)
District(FK is County table id)
City (FK is District table id)

and I have this table called telcode which I got from excel which contains the province name and the city code and the tel code.

what I am trying to do now is compare my city table cityname with the cityname of the telcode and if it same then put the telcode from the telcode table into my city table. there are cases where I can have two city name in those tables.so I have added one more check i.e. to also check the province names. the telcode table contains more city which I might not have in my city table,so it needs to ignore those,so I have used LEFT OTHER JOIN

here is my query, but when I run the query I get error

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

UPDATE city SET telcode = (select telcode.telcode from city
INNER JOIN dbo.district ON city.districtID = district.id
INNER JOIN County ON county.id=district.countyID
INNER JOIN province ON province.id = county.provinceID
LEFT OTHER JOIN telcode ON city.cityname = telcode.city)

Open in new window

0
zolf
Asked:
zolf
  • 2
1 Solution
 
jogosCommented:
Syntax more like this (not tested )
UPDATE  c
 SET c.telcode = telcode.telcode 
from city c
INNER JOIN dbo.district ON c.districtID = district.id
INNER JOIN County ON county.id=district.countyID
INNER JOIN province ON province.id = county.provinceID
LEFT OTHER JOIN telcode ON c.cityname = telcode.city

Open in new window

0
 
zolfAuthor Commented:
cheers!!
why did my query not work?? can you please explain
0
 
jogosCommented:
This expects only one value
UPDATE SET   =  

Open in new window

This returns all values for all records
(select telcode.telcode from city
INNER JOIN dbo.district ON city.districtID = district.id
INNER JOIN County ON county.id=district.countyID
INNER JOIN province ON province.id = county.provinceID
LEFT OTHER JOIN telcode ON city.cityname = telcode.city)

Open in new window

And there is no way you indicate that there is a link .... all values returned must be updated in the matching record
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now