Update through nested query 1

[bryandj23]

Hi All. Attempting to make some changes to our database. I'm able to query the results that need to be changed, I just can't quite figure out how to update a field within the results.

Customer Table
==============
CustId - GUID identifier for customer.
SalesRep - Code for SalesRep at Customer Level.

Sales Table
===========
CustId - Relationship to customer.
SalesId - GUID identifier for sale.
SaleDate - Date of Sale
SalesRep - SalesRep for particular sale.

Query: Show sales where SalesRep <> !!7, but SalesRep at Customer Level is !!7.

Task: Change SalesRep at Sales level to !!7 where SalesRep isn't !!7 at the Sales Level, but is !!7 at Customer Level.

Successful query to display sales that need to be changed:

(select * from Sales where CustId in
(select CustId from Customer where SalesRep = '!!7')
and SalesRep <> '!!7'
and SaleDate <= '2008-09-25 00:00:00.000'
and Status = 'A')

This query gives me the records that need the "SalesRep" field to be changed. However, I'm lost at how to go about updating the Sales table from here.

I need:

Update Sales
Set SalesRep = '!!7'
Where [query results from previous query]

Would this be a place where an INNER JOIN comes into play? Any help would be sincerely appreciated.

Thanks in advance.

 

[PWise]

update Sales
set  Sales.SalesRep ='!!7'
from sales
inner join Customer 
on Customer.CustId  = sales.CustId 
and Customer.SalesRep ='!!7'
and sales.SalesRep <>'!!7'

 

[bryandj23]

pwise...

Thanks so very much!!! After messing with this for awhile, and not quite understanding "INNER JOIN"s, this makes it much much clearer!

Ran your code, and it processed the 133 rows that I needed. Thank you very much!!