PtboGiser
asked on
Update Query
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.
Can someone help me out please.
Errors received are
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'FROM'.
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
Can someone help me out please.
Errors received are
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'FROM'.
You are missing a right parentheses ) mark, probably immediately before FROM.
ASKER
I'm just trying to figure out how to build a computed column, Is it alter table or a select statement?
Thanks Angel
Thanks Angel
ASKER
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
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
if the column already exists, you have to drop the column first, and then alter the table
and indeed there is a ) misplaced in your UPDATE code, it should be at the end not not after END
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)
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
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_Direc tion))
FROM Street S
-- This will Restrict Whole Table Update
WHERE S.Street_Full_Name='' OR S.Street_Full_Name is NULL
Thanks...
SNKBalaji.
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_Direc
FROM Street S
-- This will Restrict Whole Table Update
WHERE S.Street_Full_Name='' OR S.Street_Full_Name is NULL
Thanks...
SNKBalaji.
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
ASKER
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 ')'.
then run the following query.
alter table dbo.Street ADD
Street_Full_Name AS LTRIM(RTRIM(Prefix_Full)+'
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 ')'.
ASKER
i added the Right bracket it ran successfully
ASKER
The new column now appears in the table, No records have been populated. Something may be wrong with the code?
CC specs show.
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])))
ASKER
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
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
ASKER
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
SET
Street_Full_Name = LTRIM(RTRIM(s.Prefix_Full)
' ' + 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
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
ASKER
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_Directi on).
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_Directi
Then you can do an IsNull or a Coalesce in that case:
The only problem is you would have to account for the N/A in the columns.
rtrim(' ' + IsNull([Suffix_Full], rtrim([Suffix_Direction))
or
' ' + Rtrim(Coalesce(Suffix_Full, Suffix_Direction, Suffix_Cnty))
The only problem is you would have to account for the N/A in the columns.
ASKER
SELECT ltrim(rtrim([Prefix_Full])+ ' ' +
rtrim([Street_Name]) +case rtrim(' ' + IsNull([Suffix_Full], rtrim([Suffix_Direction))
end + ' ' + rtrim([Suffix_Direction]))
FROM dbo.Street s
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?
That should work. Then you can turn it into your computed column.
ASKER
Close,
Msg 174, Level 15, State 1, Line 2
The isnull function requires 2 argument(s).
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
Msg 174, Level 15, State 1, Line 2
The isnull function requires 2 argument(s).
ASKER
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?
Should i have a different command?
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
ASKER
Msg 174, Level 15, State 1, Line 2
The isnull function requires 2 argument(s).
The isnull function requires 2 argument(s).
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
ASKER
The best bet is to get the query down as a query first:
Once that is working, then alter the table.
Select blah
from dbo.Street.
Once that is working, then alter the table.
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
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
ASKER
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
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
ASKER
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
Just as an update this Creates the Computed Column properlyI'm now working on the Conditions to have the code properly with the desired columns
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.
ASKER
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.
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.
I'm thinking i should be using a nested query?
Results-1.PNG
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
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
I'm thinking i should be using a nested query?
Results-1.PNG
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
WAHOO, thx , Nulls freak me out. I'll continue to test thanks
ASKER
Thanks :)
ASKER
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?
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
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.
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.
the update syntax issue solved:
Open in new window