March 6, 2009 at 10:01 am
Hi,
I wondering if you could help me with a SQL problem, it’s probably very simple for you guys.
I have 400 telephone extensions to update but can only seem these once at a time, can you have a look where I'm going wrong when I try to update multiples.
Update person
SET Town = 'TLI'
Where Extension = '7854121', '7856121', '020523' ect ect
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near ','.
Update dbo.person
SET TLI = 'TLI'
Where Extension = '51426'
(1 row(s) affected)
March 6, 2009 at 10:04 am
Use "In" instead of "=".
Update person
SET Town = 'TLI'
Where Extension in ('7854121', '7856121', '020523')
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 6, 2009 at 10:09 am
Hi GSquared,
Thanks for the quick response.
I get "(1 row(s) affected)" with that.
Any other suggestions? I can't seem to find anything online covering this.
March 6, 2009 at 11:55 am
That means there's only one row that has one of those extensions. The other two don't exist in the table.
Try this and see if it gets anything:
select *
from person
Where Extension = '7854121';
select *
from person
Where Extension = '7856121';
select *
from person
Where Extension = '020523';
I bet only one of those finds anything.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 6, 2009 at 4:41 pm
Hi GSquared,
Thanks for the response. I think you are right, just need to find that missing data now!
Can you advise how I would update if the towns where different values as well?
an example.
City: (ml = 4321, TN =2341, NV = 7786)
I've been playing around with different variations of the below:
Update Person
Set City = 'ML', 'TN', 'NV'
Where Extension in ('4321', '2341', '7786')
Msg 102, Level 15, State1, Line 2
Incorrect syntax near 'TN'
March 6, 2009 at 5:29 pm
You can use a case expression:
UPDATE dbo.Person
SET City = CASE Extension
WHEN 4321 THEN 'ML'
WHEN 2341 THEN 'TN'
WHEN 7786 THEN 'NV'
END
WHERE Extension IN (4321, 2341, 7786);
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
March 6, 2009 at 5:32 pm
You can build a temporary mapping table and perform all the updates in a single UPDATE statement - something like the following.
/* Create mapping temporary table */
CREATE TABLE #ExtCityMap (
Extension varchar(20) NOT NULL PRIMARY KEY,
City varchar(10) NOT NULL
)
/* Populate mapping temporary table */
INSERT INTO #ExtCityMap (Extension, City)
SELECT '4321', 'ML' UNION ALL
SELECT '2341', 'TN' UNION ALL
SELECT '7786', 'NV' /* UNION ALL ... */
/* Check the rows that will be updated */
SELECT
P.Extension,
P.City AS CurrentCity,
M.City AS NewCity
FROM Person P
INNER JOIN #ExtCityMap M ON (P.Extension = M.Extension)
/* Uncomment the UPDATE statement if the results of the query are as expected */
/*
UPDATE P SET City = M.City
FROM Person P
INNER JOIN #ExtCityMap M ON (P.Extension = M.Extension)
DROP TABLE #ExtCityMap
*/
March 7, 2009 at 1:54 am
Hi Guys,
Thanks for the responses. I give them a go and keep you posted with how I get on.
Thanks again.
Laurence
March 9, 2009 at 8:12 am
I'd agree with the temp mapping table approach because a CASE statement can inadvertantly overwrite an "earlier" conversion if you're not careful. Also, you can use the IN for the WHERE clause of a SELECT statement instead of doing separate SELECT statements. That would give you less typing to do. Just cut-n-paste the WHERE clause from one statement to all the others.
Usually I do a SELECT, UPDATE and SELECT so I can see what values I affected after the update and can compare the first, unaltered list with the second altered list.
March 12, 2009 at 3:11 am
Hi Guys,
I have managed to complete some of these changes, thanks for the assistance.
However, I have found that a few extensions do not exist on my database.
e.g.
UPDATE dbo.Person
SET City = CASE Extension
WHEN 4321 THEN 'ML'
WHEN 2341 THEN 'TN'
WHEN 7786 THEN 'NV'
ect ect to 1300 rows
END
WHERE Extension IN (4321, 2341, 7786 ect);
If I'm missing 70 of the 1300 rows, is there a way that SQL can highlight these missing rows?
Edit: I realise that the data isn't there, but I would like to find out which extensions are missing so that I can bulk upload them.
March 12, 2009 at 4:09 am
If you had used a temporary mapping table then it would be easy to find the extensions that don't exist in the Person table.
So based on temporary table #ExtCityMap in previous post:
SELECT M.Extension
FROM #ExtCityMap M
LEFT OUTER JOIN dbo.Person P ON (M.Extension = P.Extension)
WHERE (P.Extension IS NULL)
By reversing the join, you can find any extensions in the Person table that don't have mappings defined in the temporary mapping table.
SELECT DISTINCT P.Extension
FROM dbo.Person P
LEFT OUTER JOIN #ExtCityMap M ON (P.Extension = M.Extension)
WHERE (M.Extension IS NULL)
March 12, 2009 at 7:12 am
Hi Andrew, Thanks for the update, I'll try it this way.
Cheers,
Laurence
March 19, 2009 at 4:45 am
Another way, if that doesn't find everything, is to use NOT IN. However, you should be aware that NOT IN uses more processing power than an IN statement.
So:
Select * from dbo.person
where Extension NOT IN ('7854121', '7856121', '020523' )
or Extension is NULL or LTRIM(RTRIM(Extension)) = ''
--The NULL and blank check are just because I like to account for
-- all possibilities
If you don't have a mapping table, this will work for you.
But a mapping table solves all sorts of problems that a manually typed set doesn't assist with. So, if you have one, use it. And if you can create one, you can use the mapping table for all the updates as well as finding values not listed in the mapping table.
March 20, 2009 at 7:34 am
You can also use this for a down and dirty listing:
SELECT DISTINCT Extension
FROM Person
ORDER BY Extension
Regards;
Greg
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply