?
Solved

Update Query

Posted on 2013-01-22
34
Medium Priority
?
289 Views
Last Modified: 2013-01-24
I need the best way to update my Street_full_Name column. This column is derived from 4 other columns within the same table. Would this be the best time to use a computed column or trigger?
I am of the understanding that creating a trigger on the same table may cause some headaches. This i'm not sure about i read it somewhere.
My update code i'm working on looks like this.
update dbo.Street

 SET
    Street_Full_Name = LTRIM(RTRIM(s.Prefix_Full)+' '+RTRIM(s.Street_Name)+
        CASE WHEN s.Suffix_Cnty = 'n/a' AND s.Suffix_Bell = 'n/a' THEN '' ELSE ' '+RTRIM(s.Suffix_Full)END)
         +' '+ RTRIM(s.Suffix_Direction
        
                FROM dbo.Street s

Open in new window


Can someone help me out please.
Errors received are
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'FROM'.
0
Comment
Question by:PtboGiser
[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
  • 19
  • 10
  • 3
  • +2
34 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38806486
yes, a computed column should do...

the update syntax issue solved:
update s
 SET
    Street_Full_Name = LTRIM(RTRIM(s.Prefix_Full)+' '+RTRIM(s.Street_Name)+
        CASE WHEN s.Suffix_Cnty = 'n/a' AND s.Suffix_Bell = 'n/a' THEN '' ELSE ' '+RTRIM(s.Suffix_Full)END)
         +' '+ RTRIM(s.Suffix_Direction
                FROM dbo.Street s

Open in new window

0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38806490
You are missing a right parentheses ) mark, probably immediately before FROM.
0
 

Author Comment

by:PtboGiser
ID: 38806493
I'm just trying to figure out how to build a computed column, Is it alter table or a select statement?
Thanks Angel
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 

Author Comment

by:PtboGiser
ID: 38806524
ok so the query is working for the Records where
s.Suffix_Cnty = 'n/a' AND s.Suffix_Bell = 'n/a'

how would i change it if one or the other is 'n/a' but not both
I can have 3 scenerios.
If s.Suffix_Cnty = 'n/a' AND s.Suffix_Bell = 'n/a' then do not populate that part of the statement

If s.Suffix_Cnty != 'n/a' AND s.Suffix_Bell = 'n/a' then populate Street_Name_Full so it includes the Suffix_Full column

If  s.Suffix_Cnty = 'n/a' AND s.Suffix_Bell != 'n/a' then populate Street_Name_Full so it includes the Suffix_Full column
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38806536
if the column already exists, you have to drop the column first, and then alter the table
alter table dbo.Street DROP column Street_Full_Name
go
alter table dbo.Street ADD
    Street_Full_Name AS LTRIM(RTRIM(Prefix_Full)+' '+RTRIM(Street_Name)+
        CASE WHEN Suffix_Cnty = 'n/a' AND Suffix_Bell = 'n/a' THEN '' ELSE ' '+RTRIM(Suffix_Full) END
         +' '+ RTRIM(Suffix_Direction)

Open in new window


and indeed there is a ) misplaced in your UPDATE code, it should be at the end not not after END
update s
 SET
    Street_Full_Name = LTRIM(RTRIM(s.Prefix_Full)+' '+RTRIM(s.Street_Name)+
        CASE WHEN s.Suffix_Cnty = 'n/a' AND s.Suffix_Bell = 'n/a' THEN '' ELSE ' '+RTRIM(s.Suffix_Full)END
         +' '+ RTRIM(s.Suffix_Direction)
                FROM dbo.Street s
                                         

Open in new window

0
 
LVL 3

Expert Comment

by:Nalinkumarbalaji
ID: 38806543
Hi,
Using Trigger is not advisable. Use the same if there is any new insertion in the table.

Below Query will work. You can use the condition for checking whether the respective value is already update or not.

-- Update Statement
UPDATE      S.STREET_FULL_NAME=
      LTRIM(RTRIM(S.Prefix_Full)) +'_'
      + LTRIM(RTRIM(S.Street_Name)) +'_'+
      CASE WHEN S.Suffix_Cnty='n/a' AND
            S.Suffix_Bell='n/a' THEN ''
      ELSE S.Suffix_Full END +'_'
      + LTRIM(RTRIM(S.Suffix_Direction))
FROM      Street S
-- This will Restrict Whole Table Update
WHERE       S.Street_Full_Name='' OR S.Street_Full_Name is NULL

Thanks...
SNKBalaji.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 38806549
Something like this?

update dbo.Street
SET
    Street_Full_Name = LTRIM(RTRIM(s.Prefix_Full) + ' ' + RTRIM(s.Street_Name)+
        CASE 
        WHEN s.Suffix_Cnty = 'n/a' AND s.Suffix_Bell = 'n/a' THEN '' 
        WHEN s.Suffix_Cnty <> 'n/a' AND s.Suffix_Bell = 'n/a' THEN ' ' + s.Suffix_Cnty
        WHEN s.Suffix_Cnty = 'n/a' AND s.Suffix_Bell <> 'n/a' THEN ' ' + s.Suffix_Bell
		ELSE ' ' + RTRIM(s.Suffix_Full) END)
         + ' ' + RTRIM(s.Suffix_Direction)
FROM dbo.Street s

Open in new window

0
 

Author Comment

by:PtboGiser
ID: 38806797
So i should drop the column Street_Name_Full
then run the following query.

alter table dbo.Street ADD
    Street_Full_Name AS LTRIM(RTRIM(Prefix_Full)+' '+RTRIM(Street_Name)+
        CASE WHEN Suffix_Cnty = 'n/a' AND Suffix_Bell = 'n/a' THEN '' ELSE ' '+RTRIM(Suffix_Full) END
         +' '+ RTRIM(Suffix_Direction)

Will this be then populated in the Computed Column Specs part of the Street Table?
I'm getting an Error
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ')'.
0
 

Author Comment

by:PtboGiser
ID: 38806803
i added the Right bracket it ran successfully
0
 

Author Comment

by:PtboGiser
ID: 38806821
The new column now appears in the table, No records have been populated. Something may be wrong with the code?

CC specs show.
(ltrim(((((rtrim([Prefix_Full])+' ')+rtrim([Street_Name]))+case when [Suffix_Cnty]='n/a' AND [Suffix_Bell]='n/a' then '' else ' '+rtrim([Suffix_Full]) end)+' ')+rtrim([Suffix_Direction])))

Open in new window

0
 

Author Comment

by:PtboGiser
ID: 38806870
Thanks for all your help in making me understand Computed Columns a bit. With this and some reading it has really helped.

That being said my code is still wrong the conditions i need to populate table properly. Maybe i'm going at it wrong. would it  be easier to code regardless of what Cnty_Suffix pr Bell_Suffix equal populate Suffix_Full where it's not null
0
 

Author Comment

by:PtboGiser
ID: 38806881
something like this?
SET
    Street_Full_Name = LTRIM(RTRIM(s.Prefix_Full) + ' ' + RTRIM(s.Street_Name)+
        ' ' + RTRIM(s.Suffix_Full)where s.Suffix_Full not null)
         + ' ' + RTRIM(s.Suffix_Direction)
FROM dbo.Street s

Sorry quick run at the code to get your eyes on it. I'm sure its not right. I wanted your thoughts
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 38806924
Somethin like this?
SELECT	ltrim(rtrim([Prefix_Full])+ ' ' + 
			rtrim([Street_Name]) +
			case 
				when [Suffix_Cnty]='n/a' AND [Suffix_Bell]='n/a' then '' 
				else  rtrim(' ' + IsNull([Suffix_Full],'')) 
			end + ' ' + 
			rtrim([Suffix_Direction]))
FROM dbo.Street s

Open in new window

0
 

Author Comment

by:PtboGiser
ID: 38807000
I'm thinking just drop the when [Suffix_Cnty]='n/a' AND [Suffix_Bell]='n/a' then  all together.

Just use
----- If Suffix_Full is not NULL then populate using Suffix_Full
------if Suffix_Full is NULL skip it and move to the next column of code(rtrim([Suffix_Direction).
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 38807337
Then you can do an IsNull or a Coalesce in that case:
rtrim(' ' + IsNull([Suffix_Full], rtrim([Suffix_Direction))

or

' ' + Rtrim(Coalesce(Suffix_Full, Suffix_Direction, Suffix_Cnty))

Open in new window


The only problem is you would have to account for the N/A in the columns.
0
 

Author Comment

by:PtboGiser
ID: 38807350
SELECT	ltrim(rtrim([Prefix_Full])+ ' ' + 
           rtrim([Street_Name]) +case rtrim(' ' + IsNull([Suffix_Full], rtrim([Suffix_Direction))
           end + ' ' + rtrim([Suffix_Direction]))
FROM dbo.Street s 

Open in new window


There are no 'n/a' values in the Suffix_Full column it is either populated with (Road, street, Avenue, Drive, etc.) or  is null.
So like this?
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 38807609
That should work. Then you can turn it into your computed column.
0
 

Author Comment

by:PtboGiser
ID: 38809826
Close,

alter table dbo.Street ADD Street_Full_Name AS ltrim(rtrim([Prefix_Full])+ ' ' + 
           rtrim([Street_Name]) + case rtrim(' ' + IsNull([Suffix_Full]), rtrim([Suffix_Direction))
           end + ' ' + rtrim([Suffix_Direction])))
FROM dbo.Street

Open in new window


Msg 174, Level 15, State 1, Line 2
The isnull function requires 2 argument(s).
0
 

Author Comment

by:PtboGiser
ID: 38809854
I want to add the Suffix_Full column when Suffix is populated or is not null. so if it is POPULATED. If its null i don't want to add anything.
Should i have a different command?
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 38810302
Try this:
alter table dbo.Street ADD Street_Full_Name AS ltrim(rtrim([Prefix_Full])+ ' ' + 
           rtrim([Street_Name]) + case rtrim(' ' + IsNull([Suffix_Full]), '')
           end + ' ' + rtrim([Suffix_Direction])))
FROM dbo.Street

Open in new window

0
 

Author Comment

by:PtboGiser
ID: 38810314
Msg 174, Level 15, State 1, Line 2
The isnull function requires 2 argument(s).
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 38810764
The parentheses was in the wrong place:
alter table dbo.Street ADD Street_Full_Name AS ltrim(rtrim([Prefix_Full])+ ' ' + 
           rtrim([Street_Name]) + case rtrim(' ' + IsNull([Suffix_Full],''))
           end + ' ' + rtrim([Suffix_Direction])))
FROM dbo.Street

Open in new window

0
 

Author Comment

by:PtboGiser
ID: 38810835
Still is i think, I've been trying to figure them out for a while.

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ')'.

Image of Code in SSMS
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 38812390
The best bet is to get the query down as a query first:
Select blah
from dbo.Street.

Open in new window


Once that is working, then alter the table.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38813230
I don't see why you have the CASE END in your code any longer ...
aligning the code shows you had indeed a ) too much

ALTER TABLE dbo.Street 
  ADD Street_Full_Name AS ltrim(
      rtrim( [Prefix_Full] )+ ' ' 
   + rtrim([Street_Name])
   + rtrim(' ' + IsNull([Suffix_Full],'')) + ' ' 
   + rtrim([Suffix_Direction])
    )
FROM dbo.Street

Open in new window

0
 

Author Comment

by:PtboGiser
ID: 38814675
Good Point Jimpin, I guess i'm in over my head here. I will back track and start with something simple like so. Which is not getting me any results jsut null values.
Then Progress to the Alter Table command.
select ltrim(
      rtrim( [Prefix_Full] )+ ' '
   + rtrim([Street_Name])
   + rtrim(' ' + IsNull([Suffix_Full],'')) + ' '
   + rtrim([Suffix_Direction])
    ) FROM dbo.Street

Thanks
0
 

Author Comment

by:PtboGiser
ID: 38815057
alter table dbo.Street DROP column Street_Full_Name
go
ALTER TABLE dbo.Street 
  ADD Street_Full_Name AS ltrim(
      rtrim( [Prefix_Full] )+ ' ' 
   + rtrim([Street_Name])
   + ' ' + Rtrim(Coalesce(Suffix_Full, Suffix_Direction, Suffix_Cnty))
   + rtrim([Suffix_Direction])
    ) Persisted

Open in new window

Just as an update this Creates the Computed Column properly
I'm now working on the Conditions to have the code properly with the desired columns
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 38815122
In SQL, [anything] + Null = Null. So you should make sure that everything is wrapped with IsNull. The better way is when you design the table originally, you make sure that all columns have a default value whether it is an empty string, a zero. Dates are always a bear.
0
 

Author Comment

by:PtboGiser
ID: 38815159
Thanks Jim. Wrapping with isnull is something i am reading about right now.

I notice when i am doing a simple select query in testing the results are as follows. I get 40 records compelted properly of the 3009.
 Select 
 ltrim(
      rtrim([Prefix_Full]) + ' ' + 
	  rtrim([Street_Name]))
 From dbo.Street

Open in new window


I get the attached results1.jpg.

When i add the next column to join the 3rd column i receive the same amount of records 3009. But all results are Null.
 Select 
 
 ltrim(
      rtrim([Prefix_Full]) + ' ' + 
	  rtrim([Street_Name]) + ' ' +
	  rtrim([Suffix_Full]))
 
From dbo.Street

Open in new window


I'm thinking i should be using a nested query?
Results-1.PNG
0
 
LVL 38

Accepted Solution

by:
Jim P. earned 2000 total points
ID: 38815634
No need to have a nested query. Just handle the nulls:

Select 
 
 ltrim(
      rtrim(isnull([Prefix_Full],'')) + ' ' + 
	  rtrim(isnull([Street_Name],'')) + ' ' +
	  rtrim(isnull([Suffix_Full],'')))
 
From dbo.Street

Open in new window

0
 

Author Comment

by:PtboGiser
ID: 38816085
WAHOO, thx , Nulls freak me out. I'll continue to test thanks
0
 

Author Closing Comment

by:PtboGiser
ID: 38816230
Thanks :)
0
 

Author Comment

by:PtboGiser
ID: 38816283
Hey Guys
This now  works great on my current Street table. What is the best way to take the Column results and move them into My Segment table for the use of annotation?
Can i write a trigger that pulls the STREET_FULL_NAME computed column to update in my Segment table that has the same column in it?
UPDATE Segment
        SET     Street_Full_Name = st.Street_Full_Name,
                AnnoName = st.AnnoName
        FROM    dbo.Segment s
        
        INNER JOIN dbo.Street st ON s.Street_ID = st.Street_ID

Open in new window

0
 
LVL 38

Expert Comment

by:Jim P.
ID: 38816712
Can i write a trigger that pulls the STREET_FULL_NAME

You probably can. But the how to is a whole new question. ;-)

Glad to be of assistance. May all your days get brighter and brighter.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

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.
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

770 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