By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,413 Members | 1,594 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,413 IT Pros & Developers. It's quick & easy.

Trying to update certain rows and its not producing the results i need

P: 5
I am trying to update the titles of all the employees who ase listed as Sales Representative to say Sales Partners but when I execute this code it says no rows are effected and their titles are staying the same. Can someone help me figure out what I am doing wrong?

This is what I have.

USE Northwind
UPDATE dbo.Employees
SET Title = 'Sales Partners'
WHERE Title = 'Sales Representatives'

SELECT * FROM dbo.Employees
Dec 9 '09 #1
Share this Question
Share on Google+
1 Reply


nbiswas
100+
P: 149
Your query is unquestionable(full correct) but I suspect that whether the exact record is matching or not(I don't have Northwind DB with me).

However, I have prepared a sample data with a query for you. Have a look and that might help you.

Sample data

Expand|Select|Wrap|Line Numbers
  1. declare @t table(empid int identity, empname varchar(10),title varchar(50))
  2. insert into @t 
  3.     select 'name1','Sales Representatives' union all
  4.     select 'name2', 'Sales             Representatives' union all
  5.     select 'name3', 'SalesRepresentatives' union all
  6.     select 'name4', '   Sales Representatives' union all
  7.     select 'name5' , 'some other title'
Query

Expand|Select|Wrap|Line Numbers
  1. UPDATE @t
  2. SET Title = 'Sales Partners'
  3. WHERE REPLACE(title,' ','') like '%'+ Replace('Sales Representatives',' ','') + '%'
  4. select * from @t
Output:

Expand|Select|Wrap|Line Numbers
  1. empid    empname    title
  2. 1    name1    Sales Partners
  3. 2    name2    Sales Partners
  4. 3    name3    Sales Partners
  5. 4    name4    Sales Partners
  6. 5    name5    some other title
As you can see, I have taken 4 variations for "Sales Representatives" and using like to find the match. Just check the same in the Northwind as how the data is stored over there!

Hope this helps
Dec 10 '09 #2

Post your reply

Sign in to post your reply or Sign up for a free account.