Solved

update issue in ms sql server 2005

Posted on 2011-02-26
30
189 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
  • 12
  • 10
  • 4
  • +2
30 Comments
 
LVL 28

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 28

Expert Comment

by:sammySeltzer
ID: 34988202
actually that was incorrect.

0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 28

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 40

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 40

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 40

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 40

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Detach & Attach 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.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

820 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