Solved

SQL query help

Posted on 2013-06-22
3
298 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
Comment Utility
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
Comment Utility
cheers!!
why did my query not work?? can you please explain
0
 
LVL 25

Expert Comment

by:jogos
Comment Utility
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now