update issue in ms sql server 2005

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'
LVL 10
GlobaLevelAsked:
Who is Participating?
 
sammySeltzerConnect With a Mentor Commented:
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
 
Rajkumar GsSoftware EngineerCommented:
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
 
sammySeltzerCommented:
actually that was incorrect.

0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
sammySeltzerCommented:
I meant my post, sorry
0
 
Rajkumar GsSoftware EngineerCommented:
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
 
Rajkumar GsSoftware EngineerCommented:
Check your requirement. What conditions to satisfy. Give it properly
0
 
Rajkumar GsSoftware EngineerCommented:
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
 
GlobaLevelAuthor Commented:
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
 
Rajkumar GsSoftware EngineerCommented:
What is the error message now ?
Raj
0
 
SharathData EngineerCommented:
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
 
SharathData EngineerCommented:
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
 
Rajkumar GsSoftware EngineerCommented:
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
 
GlobaLevelAuthor Commented:
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
 
SharathConnect With a Mentor Data EngineerCommented:
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
 
GlobaLevelAuthor Commented:
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
 
Rajkumar GsSoftware EngineerCommented:
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
 
Rajkumar GsConnect With a Mentor Software EngineerCommented:
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
 
LowfatspreadCommented:
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
 
GlobaLevelAuthor Commented:
Raj...I ran yours...though it executed with no errors...


(0 row(s) affected)

...when 3 should have been...
0
 
GlobaLevelAuthor Commented:
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
 
Rajkumar GsSoftware EngineerCommented:
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
 
GlobaLevelAuthor Commented:
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
 
GlobaLevelAuthor Commented:
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
 
Rajkumar GsSoftware EngineerCommented:
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
 
GlobaLevelAuthor Commented:
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
 
Rajkumar GsSoftware EngineerCommented:
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
 
GlobaLevelAuthor Commented:
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
 
SharathData EngineerCommented:
That means you do not have matching records. What do you want to update?
0
 
Rajkumar GsSoftware EngineerCommented:
GlobaLevel,

That is what I am saying - http:#34994497

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

Raj
0
 
GlobaLevelAuthor Commented:
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
All Courses

From novice to tech pro — start learning today.