Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL 2005 Update Query problems...

Author  Topic 

sin2win
Starting Member

4 Posts

Posted - 2010-04-23 : 10:21:53
This is driving me crazy... In my database I have names with suffixes, such as (blue H, red H, white P, yellow R...) I need an update query to look at the suffix of the record, keep the name and replace the suffix with something different. So all "XnameX H" records would become "XnameX C" records. Any help would be greatly appreciated!!

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-23 : 10:35:13
Can you show us some sample data and specific result?
Try this, its maybe correct:

UPDATE table_name
SET column_name = LEFT(column_name, LEN(column_name)-1) + SUBSTRING(@variable, LEN(variable)-1, 1)
WHERE column_name LIKE '%name%' AND SUBSTRING(column_name, LEN(column_name)-1, 1) =
SUBSTRING(@variable, LEN(variable)-1, 1);
Go to Top of Page

sin2win
Starting Member

4 Posts

Posted - 2010-04-23 : 11:46:38
Thank you for the quick reply!
Sample Data:
Red H
Blue H
White PH
Yellow RH
Green P

I need to search through the table for records with the suffix "H" and change all instances' suffix to Q while keeping the original name. I also need to be able to do the same thing for records with suffix "PH" and change all instances' suffix to YQ. I understand what your first posts' code is doing except the @variable/variable part. In the SET statement, the variable would be what I want the suffix changed to, but the @variable/variable confused me in the WHERE clause.
Go to Top of Page

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-23 : 12:01:11
Does the suffix identify after space character in the name?
Also you have a table that stores the new instances? I mean you want multiple update statement or not.
Go to Top of Page

sin2win
Starting Member

4 Posts

Posted - 2010-04-23 : 13:23:04
I just need to look in the name column of a table and change the suffix.

Example:
Red H
Blue H
would become:
Red Q
Blue Q

I would then change the variables for multiple lettered suffix's.
Example:
White PH
would become:
White YQ

I need to change the existing record's suffix, not save the results in a different table.

Table name = TESTcolors
column name = name
@variable1 = original suffix "H"
@variable2 = desired new suffix "Q"

This is my best guess...

alter procedure TESTupdateColorSuffix
(
@variable1 as varchar(5) ,
@variable2 as varchar(5)
)
AS
UPDATE TESTcolors
SET name = LEFT(name, LEN(name)-1) + SUBSTRING(@variable2, LEN(name)-1, 1)
WHERE name LIKE @variable1 AND SUBSTRING(name, LEN(name)-1, 1) = SUBSTRING(@variable1, LEN(name)-1, 1);
Go to Top of Page

sin2win
Starting Member

4 Posts

Posted - 2010-04-23 : 14:16:45
I got it now, your help set me in the right direction. Thank you!
Go to Top of Page

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-23 : 15:29:14
quote:
Originally posted by sin2win

I got it now, your help set me in the right direction. Thank you!


That's great!
Go to Top of Page
   

- Advertisement -