Is this possible? Updating using a Function?

[michaelzip]

Hi.

I have this table named tblProduct, which contains fldProductID, fldProductName, fldValue
fldProductName contains its name, and fldValue contains how much is the product cost.

Now I am having trouble with updating fldProductID.
Here's what my fldProductID look like.

fldProductIDG01
G02
G03
GZ01
GZ02
TX01
TX02
TX03
TX04

Its format is [ProductPrefix][ID]
When ProductPrefix is deleted, all of the productID containing that prefix will be deleted.
For example GZ from ProductPrefix was deleted, GZ01 and GZ02 will be deleted. I don't have a problem with that.

The problem is the Update, when I changed the ProductPrefix, from GZ to GZX, the records from fldProducts will be updated too. GZ01 will become GZX01 and GZ02 will become GZX02.

I have one solution regarding that but it is NOT efficient to use, like if you have thousands of records affected. Each item will be selected by using...

SELECT fldProductID FROM tblProduct GROUP BY fldProductID HAVING MID(fldProductID,1,Len(Prefix)) = 'SOMEVALUE'

... and then will apply the UPDATE query ONE by ONE.

What I am thinking/asking is, is it possible to USE a single line query for this? Like,

UPDATE tblProducts SET fldProductID='<NEWVALUE>' WHERE MID(fldProductID,1,Len(Prefix)) = 'SOMEVALUE'

Please help,
Michael

[masocha]

UPDATE tblProducts
SET fldProductID = REPLACE(fldProductID, 'somevalue', 'someothervalue')
WHERE fldProductID LIKE '%somevalue%'

[michaelzip]

masocha,

Thanks for the reply. What if I have a ProductID of G01, G02, GX01,GX02, JG01, JG02 and I want to update G## ID's and use LIKE '%G%'. It will update G## but it will include GX and JG. I just thought of that before.

Michael