Solved

update issue in ms sql server 2005

Posted on 2011-02-26
30
191 Views
Last Modified: 2012-05-11
I get an error on this....
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '='.



UPDATE C
set TIME_SENT_RANGE = CASE
 WHEN (LEFT(c.TO_NUMBER,3 = ac.AREA_CODE) like '%Eastern%' THEN 'Nat_Eastern'
 WHEN (LEFT(c.TO_NUMBER,3 = ac.AREA_CODE) like '%Central%' THEN 'Nat_Central'
 WHEN (LEFT(c.TO_NUMBER,3 = ac.AREA_CODE) like '%Mountain%'  THEN 'Nat_Mountain'
 WHEN (LEFT(c.TO_NUMBER,3 = ac.AREA_CODE) like '%Pacific%' THEN 'Nat_Pacific'
FROM Campaign C
JOIN Area_Code_Time_Zone AC
WHERE TC_ID = 'TC_ID_SD9FJG3K' and CAMPAIGN_NAME = 'School_lunch'
0
Comment
Question by:GlobaLevel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 10
  • 4
  • +2
30 Comments
 
LVL 29

Accepted Solution

by:
sammySeltzer earned 167 total points
ID: 34988192
You need to change your code:

WHEN (LEFT(c.TO_NUMBER,3 = ac.AREA_CODE) like '%Eastern%' THEN 'Nat_Eastern'

to

WHEN (LEFT(c.TO_NUMBER,3) = ac.AREA_CODE) like '%Eastern%' THEN 'Nat_Eastern'

do it for the rest of the lines
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34988200
END seems to be missing
UPDATE C
set TIME_SENT_RANGE = CASE
.......
END -- missing
FROM Campaign C
JOIN Area_Code_Time_Zone AC
WHERE TC_ID = 'TC_ID_SD9FJG3K' and CAMPAIGN_NAME = 'School_lunch'

Open in new window

0
 
LVL 29

Expert Comment

by:sammySeltzer
ID: 34988202
actually that was incorrect.

0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 29

Expert Comment

by:sammySeltzer
ID: 34988203
I meant my post, sorry
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34988210
As sammy mentioned something wrong with your case statement
I think, it could be something like
WHEN (LEFT(c.TO_NUMBER,3) = ac.AREA_CODE AND ac.AREA_CODE like '%Eastern%')  THEN 'Nat_Eastern'
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34988211
Check your requirement. What conditions to satisfy. Give it properly
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34988226
There are many issues found with your query
1.
LEFT(c.TO_NUMBER,3

Open in new window

is wrong. It should be closed like
LEFT(c.TO_NUMBER,3)

Open in new window


2.
(LEFT(c.TO_NUMBER,3 = ac.AREA_CODE) like '%Eastern%' 

Open in new window

This is not valid CASE statement. It should be splitted off as I mentioned before or as you want.

3.

Open in new window

END is missing for CASE statement.

Raj
0
 
LVL 10

Author Comment

by:GlobaLevel
ID: 34988290
I saw that unvalid case statementand know I have this..still errors:

UPDATE C
set TIME_SENT_RANGE = CASE
 WHEN LEFT(c.TO_NUMBER,3) = ac.AREA_CODE and ac.TIME_ZONE like '%Eastern%' THEN 'Nat_Eastern'
 WHEN LEFT(c.TO_NUMBER,3) = ac.AREA_CODE and ac.TIME_ZONE like '%Central%' THEN 'Nat_Central'
 WHEN LEFT(c.TO_NUMBER,3) = ac.AREA_CODE and ac.TIME_ZONE like '%Mountain%'  THEN 'Nat_Mountain'
 WHEN LEFT(c.TO_NUMBER,3) = ac.AREA_CODE and ac.TIME_ZONE like '%Pacific%' THEN 'Nat_Pacific'
END
FROM Campaign C
JOIN Area_Code_Time_Zone AC --ON c.TC_ID = ac.Country
WHERE c.TC_ID = 'TC_ID_SD9FJG3K' and c.CAMPAIGN_NAME = 'School_lunch'
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34988434
What is the error message now ?
Raj
0
 
LVL 41

Expert Comment

by:Sharath
ID: 34988559
JOIN condition is missing. Change the WHERE clause to JOIN condition.
UPDATE C 
set TIME_SENT_RANGE = CASE 
 WHEN LEFT(c.TO_NUMBER,3) = ac.AREA_CODE and ac.TIME_ZONE like '%Eastern%' THEN 'Nat_Eastern'
 WHEN LEFT(c.TO_NUMBER,3) = ac.AREA_CODE and ac.TIME_ZONE like '%Central%' THEN 'Nat_Central'
 WHEN LEFT(c.TO_NUMBER,3) = ac.AREA_CODE and ac.TIME_ZONE like '%Mountain%'  THEN 'Nat_Mountain'
 WHEN LEFT(c.TO_NUMBER,3) = ac.AREA_CODE and ac.TIME_ZONE like '%Pacific%' THEN 'Nat_Pacific'
END
FROM Campaign C
JOIN Area_Code_Time_Zone AC --ON c.TC_ID = ac.Country
ON c.TC_ID = 'TC_ID_SD9FJG3K' and c.CAMPAIGN_NAME = 'School_lunch'

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 34988569
if you really have the JOIN condition on Country and just commented out in your code, then uncomment that.
UPDATE C 
set TIME_SENT_RANGE = CASE 
 WHEN LEFT(c.TO_NUMBER,3) = ac.AREA_CODE and ac.TIME_ZONE like '%Eastern%' THEN 'Nat_Eastern'
 WHEN LEFT(c.TO_NUMBER,3) = ac.AREA_CODE and ac.TIME_ZONE like '%Central%' THEN 'Nat_Central'
 WHEN LEFT(c.TO_NUMBER,3) = ac.AREA_CODE and ac.TIME_ZONE like '%Mountain%'  THEN 'Nat_Mountain'
 WHEN LEFT(c.TO_NUMBER,3) = ac.AREA_CODE and ac.TIME_ZONE like '%Pacific%' THEN 'Nat_Pacific'
END
FROM Campaign C
JOIN Area_Code_Time_Zone AC ON c.TC_ID = ac.Country
AND c.TC_ID = 'TC_ID_SD9FJG3K' and c.CAMPAIGN_NAME = 'School_lunch'

Open in new window

0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34988581
Or ?
UPDATE C 
set TIME_SENT_RANGE = CASE 
 WHEN LEFT(c.TO_NUMBER,3) = ac.AREA_CODE and ac.TIME_ZONE like '%Eastern%' THEN 'Nat_Eastern'
 WHEN LEFT(c.TO_NUMBER,3) = ac.AREA_CODE and ac.TIME_ZONE like '%Central%' THEN 'Nat_Central'
 WHEN LEFT(c.TO_NUMBER,3) = ac.AREA_CODE and ac.TIME_ZONE like '%Mountain%'  THEN 'Nat_Mountain'
 WHEN LEFT(c.TO_NUMBER,3) = ac.AREA_CODE and ac.TIME_ZONE like '%Pacific%' THEN 'Nat_Pacific'
END
FROM Campaign C
JOIN Area_Code_Time_Zone AC ON c.TC_ID = ac.Country
WHERE c.TC_ID = 'TC_ID_SD9FJG3K' and c.CAMPAIGN_NAME = 'School_lunch'

Open in new window

0
 
LVL 10

Author Comment

by:GlobaLevel
ID: 34988985
the code attached is working...yet returning no results...

after doing some tests....this portion is not working...
LEFT(c.TO_NUMBER,3) = ac.AREA_CODE

TO_NUMBER column contains data like this:
21285474125

AREA_CODE cotains data like this:
212

yet its now working for some reason....




UPDATE C 
set TIME_SENT_RANGE = CASE 
 WHEN LEFT(c.TO_NUMBER,3) = ac.AREA_CODE and ac.TIME_ZONE like '%Eastern%' THEN 'Nat_Eastern'
 WHEN LEFT(c.TO_NUMBER,3) = ac.AREA_CODE and ac.TIME_ZONE like '%Central%' THEN 'Nat_Central'
 WHEN LEFT(c.TO_NUMBER,3) = ac.AREA_CODE and ac.TIME_ZONE like '%Mountain%'  THEN 'Nat_Mountain'
 WHEN LEFT(c.TO_NUMBER,3) = ac.AREA_CODE and ac.TIME_ZONE like '%Pacific%' THEN 'Nat_Pacific'
END
FROM Campaign C
JOIN Area_Code_Time_Zone AC ON c.TC_ID = ac.Country
WHERE c.TC_ID = 'TC_ID_SD9FJG3K' and c.CAMPAIGN_NAME = 'School_lunch'

Open in new window

0
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 166 total points
ID: 34989416
try this.
UPDATE C 
   SET TIME_SENT_RANGE = CASE 
                           WHEN LEFT(CONVERT(VARCHAR,c.TO_NUMBER),3) = ac.AREA_CODE 
                                AND ac.TIME_ZONE LIKE '%Eastern%' THEN 'Nat_Eastern' 
                           WHEN LEFT(CONVERT(VARCHAR,c.TO_NUMBER),3) = ac.AREA_CODE 
                                AND ac.TIME_ZONE LIKE '%Central%' THEN 'Nat_Central' 
                           WHEN LEFT(CONVERT(VARCHAR,c.TO_NUMBER),3) = ac.AREA_CODE 
                                AND ac.TIME_ZONE LIKE '%Mountain%' THEN 'Nat_Mountain' 
                           WHEN LEFT(CONVERT(VARCHAR,c.TO_NUMBER),3) = ac.AREA_CODE 
                                AND ac.TIME_ZONE LIKE '%Pacific%' THEN 'Nat_Pacific' 
                         END 
  FROM Campaign C 
       JOIN Area_Code_Time_Zone AC 
         ON c.TC_ID = ac.Country 
 WHERE c.TC_ID = 'TC_ID_SD9FJG3K' 
       AND c.CAMPAIGN_NAME = 'School_lunch'

Open in new window

0
 
LVL 10

Author Comment

by:GlobaLevel
ID: 34989542
Sharath_123,

I got this error....

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "c.TO_NUMBER" could not be bound.
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34990639
That errro says that the column 'TO_NUMBER' is not in the table with alias 'c' - It is Campaign - Verify it

Let us know
Raj
0
 
LVL 23

Assisted Solution

by:Rajkumar Gs
Rajkumar Gs earned 167 total points
ID: 34990640
My updated query - but check the issue I mentioned in my previous comment
UPDATE  C
SET     TIME_SENT_RANGE = CASE WHEN LEFT(LTRIM(CAST(C.TO_NUMBER AS VARCHAR)), 3) = LTRIM(CAST(ac.AREA_CODE AS VARCHAR))
                                    AND ac.TIME_ZONE LIKE '%Eastern%'
                               THEN 'Nat_Eastern'
                               WHEN LEFT(LTRIM(CAST(C.TO_NUMBER AS VARCHAR)), 3) = LTRIM(CAST(ac.AREA_CODE AS VARCHAR))
                                    AND ac.TIME_ZONE LIKE '%Central%'
                               THEN 'Nat_Central'
                               WHEN LEFT(LTRIM(CAST(C.TO_NUMBER AS VARCHAR)), 3) = LTRIM(CAST(ac.AREA_CODE AS VARCHAR))
                                    AND ac.TIME_ZONE LIKE '%Mountain%'
                               THEN 'Nat_Mountain'
                               WHEN LEFT(LTRIM(CAST(C.TO_NUMBER AS VARCHAR)), 3) = LTRIM(CAST(ac.AREA_CODE AS VARCHAR))
                                    AND ac.TIME_ZONE LIKE '%Pacific%'
                               THEN 'Nat_Pacific'
                          END
FROM    Campaign C
        JOIN Area_Code_Time_Zone AC ON C.TC_ID = ac.Country
WHERE   C.TC_ID = 'TC_ID_SD9FJG3K'
        AND C.CAMPAIGN_NAME = 'School_lunch'

Open in new window


Raj
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34991742
update c
set time_sent_range
 = case when ac.time_zone like '%Eastern%' then 'Nat_eastern'
            when ac.time_zone like '%Central%' then 'Nat_Central'
            when ac.time_zone like '%Mountain%' then 'Nat_Mountain'
            when ac_time_zone like '%Pacific%' then 'Nat_Pacific'
            else NULL
            end
from campaign as c
inner join area_code_time_zone as ac
on ac.country=c.tc_id
where C.TC_ID = 'TC_ID_SD9FJG3K'
        AND C.CAMPAIGN_NAME = 'School_lunch'
and  left(c.to_number,3)=ac.area_code
0
 
LVL 10

Author Comment

by:GlobaLevel
ID: 34994417
Raj...I ran yours...though it executed with no errors...


(0 row(s) affected)

...when 3 should have been...
0
 
LVL 10

Author Comment

by:GlobaLevel
ID: 34994444
Sharath...

ran yours....
(0 row(s) affected)

...when 3 should have been...
UPDATE C 
   SET TIME_SENT_RANGE = CASE 
                           WHEN LEFT(CONVERT(VARCHAR,c.TO_NUMBER),3) = ac.AREA_CODE 
                                AND ac.TIME_ZONE LIKE '%Eastern%' THEN 'Nat_Eastern' 
                           WHEN LEFT(CONVERT(VARCHAR,c.TO_NUMBER),3) = ac.AREA_CODE 
                                AND ac.TIME_ZONE LIKE '%Central%' THEN 'Nat_Central' 
                           WHEN LEFT(CONVERT(VARCHAR,c.TO_NUMBER),3) = ac.AREA_CODE 
                                AND ac.TIME_ZONE LIKE '%Mountain%' THEN 'Nat_Mountain' 
                           WHEN LEFT(CONVERT(VARCHAR,c.TO_NUMBER),3) = ac.AREA_CODE 
                                AND ac.TIME_ZONE LIKE '%Pacific%' THEN 'Nat_Pacific' 
                         END 
  FROM Campaign C 
       JOIN Area_Code_Time_Zone AC 
         ON c.TC_ID = ac.Country 
 WHERE c.TC_ID = 'TC_ID_SD9FJG3K' 
       AND c.CAMPAIGN_NAME = 'School_lunch'

Open in new window

0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34994448
This could be because there is no records mathcing your WHERE conditon
Try this query and check whether any results ?
SELECT * FROM
FROM    Campaign C
        JOIN Area_Code_Time_Zone AC ON C.TC_ID = ac.Country
WHERE   C.TC_ID = 'TC_ID_SD9FJG3K'
        AND C.CAMPAIGN_NAME = 'School_lunch'

Open in new window


Raj
0
 
LVL 10

Author Comment

by:GlobaLevel
ID: 34994479
I wanted to provided sample data...so we're all looking at the same thing...obviously two tables in play here

Area_Code_Time_Zone
area_code                                                time_zone
 201        US        New Jersey        Eastern        Nat_Eastern


campaign
to_number
8479222700
0
 
LVL 10

Author Comment

by:GlobaLevel
ID: 34994481
forgot to attach....
[To_Number] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

------------

CREATE TABLE [dbo].[Area_Code_Time_Zone](
	[AREA_CODE] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[COUNTRY] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[STATE] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[TIME_ZONE] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[NATIONAL_CODE] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

Open in new window

0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34994497
I meant for that update query to work - there should be some records matching that WHERE condition
So you are saying - no records, right ?
Raj
0
 
LVL 10

Author Comment

by:GlobaLevel
ID: 34994499
Raj...

ran this:
SELECT * FROM
FROM    Campaign C
        JOIN Area_Code_Time_Zone AC ON C.TC_ID = ac.Country
WHERE   C.TC_ID = 'TC_ID_SD9FJG3K'
        AND C.CAMPAIGN_NAME = 'School_lunch'

got this:
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'FROM'.
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34994583
Oops! It is a simple error - See two 'FROM' :)

Try this query and please let me know
SELECT * FROM
Campaign C
        JOIN Area_Code_Time_Zone AC ON C.TC_ID = ac.Country
WHERE   C.TC_ID = 'TC_ID_SD9FJG3K'
        AND C.CAMPAIGN_NAME = 'School_lunch' 

Open in new window

0
 
LVL 10

Author Comment

by:GlobaLevel
ID: 35003927
Raj,

THis ran fine....yet pulled no results....just empty columns..


SELECT * FROM
Campaign C
        JOIN Area_Code_Time_Zone AC ON C.TC_ID = ac.Country
WHERE   C.TC_ID = 'TC_ID_SD9FJG3K'
        AND C.CAMPAIGN_NAME = 'School_lunch'  
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35003964
That means you do not have matching records. What do you want to update?
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 35004013
GlobaLevel,

That is what I am saying - http:#34994497

There is no records to update based on that WHERE condition.

Raj
0
 
LVL 10

Author Comment

by:GlobaLevel
ID: 35008264
Sharath..Raj...

got it...here is the issue....there are no common columns between these two tables...

I need
1) to get the area code from the TO_NUMBER column off the Campaign table...this where I just need the first 3 digits...so all numbers are listed as 7144568973..so I need to get the 714...
2) after I get just the areacode...I need to go to the area code table and compare to see if that area code(714) is found in the AREA_CODE column....get the value on the same row in the NATIONAL
3) if there is a match...then write the value from the NATIONAL_CODE column on the area_code table and write it to the TIME_SENT_RANGE on the campaign table...

any questions, please let me know...
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

719 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