SQL WHERE Like Query 1

[RosieGp]

Hi all,
I have an update query as following:

UPDATE EmployeeTable
SET [Address] = '#AddressUpdate#',
WHERE [Employee_ID] = '#EmployeeID#'

From my fornt end I'm grabing the Employee_ID, and the SQL table looks like the following:
Employee_ID Address
1001-1 128 W.South Blvd
45902-1 345 West Virginia
459-1 123 S. Pontiac Trail
459-2 123 S. Pontiac Trail
459-3 123 S. Pontiac Trail
7896-1 5679 N. Kraft Drive
7896-2 5679 N. Kraft Drive

So suppose if I selected EmployeeID 7896-1 to update the address and I run the above query it will only update adress for 7896-1. For example:
Employee_ID Address
1001-1 128 W.South Blvd
45902-1 345 West Virginia
459-1 123 S. Pontiac Trail
459-2 123 S. Pontiac Trail
459-3 123 S. Pontiac Trail
7896-1 8796 S. Hampton Inns
7896-2 5679 N. Kraft Drive



But I want it to update address for both 7896-2 also. For example:
Employee_ID Address
1001-1 128 W.South Blvd
45902-1 345 West Virginia
459-1 123 S. Pontiac Trail
459-2 123 S. Pontiac Trail
459-3 123 S. Pontiac Trail
7896-1 8796 S. Hampton Inns
7896-2 8796 S. Hampton Inns

How to change my update query to get this result.
Any help is appreciated...
Thanks...

 

[simian336]

UPDATE EmployeeTable
SET [Address] = '#AddressUpdate#',
where left(Employee_ID,4)='7896'


Simi

 

[gmmastros]

But I want it to update address for both

What is the basis for the decision to update both? Because employee id is similar or because there is a match in address?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RosieGp]

I want to update
7896-1 5679 N. Kraft Drive
7896-2 5679 N. Kraft Drive

based on the [Employee_ID] = '#EmployeeID#'
So in a way just check for anything before '-' if they match then update both the addresses based on the Employee_ID...

@Simi,
where left(Employee_ID,4)='7896' this is not going to work for me as some id's could be 3 digit before the '-' some 8 and so on...
For example:

101-1
101-3
23678-1
23678-2
23678-3
4568789-1
4568789-2
4568789-3
4568789-4

Thanks for your reply...

 

[gmmastros]

Try this:

UPDATE EmployeeTable
     SET [Address] = '#AddressUpdate#', 
Where	[Employee_ID] Like Left('#EmployeeID#', PatIndex('%-%', '#EmployeeID#' + '-')-1) + '%'

If this works and you want me to explain it, let me know.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RosieGp]

George,
it did not work...
is there another way of doing this...

 

[gmmastros]

What didn't work about it?

It worked in my sample. See:

Declare @Temp Table(Data VarChar(20), Color VarChar(20))

Insert Into @Temp(Data,Color) Values('101-1','red')
Insert Into @Temp(Data,Color) Values('101-3','red')
Insert Into @Temp(Data,Color) Values('23678-1','Blue')
Insert Into @Temp(Data,Color) Values('23678-2','Blue')
Insert Into @Temp(Data,Color) Values('23678-3','Blue')
Insert Into @Temp(Data,Color) Values('4568789-1','Green')
Insert Into @Temp(Data,Color) Values('4568789-2','Green')
Insert Into @Temp(Data,Color) Values('4568789-3','Green')
Insert Into @Temp(Data,Color) Values('4568789-4','Green')

Declare @EmployeeId VarChar(20)

Set @EmployeeId = '4568789-2'

Select 'Before', * From @Temp

Update	@Temp
Set		Color = 'Purple'
Where	Data Like Left(@EmployeeId, PatIndex('%-%', @EmployeeId + '-')-1) + '%'

Select  'after', * From @Temp

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RosieGp]

I think it did work sorry. i had it wrong....
thanks a lot.