Solved

SQL query help

Posted on 2013-06-22
3
303 Views
Last Modified: 2013-06-22
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
Comment
Question by:zolf
  • 2
3 Comments
 
LVL 25

Accepted Solution

by:
jogos earned 500 total points
ID: 39267732
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
 

Author Closing Comment

by:zolf
ID: 39267737
cheers!!
why did my query not work?? can you please explain
0
 
LVL 25

Expert Comment

by:jogos
ID: 39267808
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

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQLCMD Syntax 2 17
MS SQL query to show nearest date 6 44
SQLCMD Output to file from xpcmd_shell 6 21
SQL Recursion 6 22
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question