how to specify criteria for update value of Update query?

11 Aug 2006 9:27 PM

Rich

Hello,

I need to update the following table. ID2 is updated to the value of ID1
except if ID1 has a Prefix of 'TCC' that must be replaced with 'SEC'. If ID1
does not have a prefix of 'TCC', then 'SEC' must be added to the ID2 value
plus the value of ID1.

CREATE TABLE tmp1(ID1 varchar(20), ID2 varchar(20))
INSERT INTO tmp1
SELECT 'TCC123', '' UNION
SELECT '2345', '' UNION
SELECT 'TCC456', '' UNION
SELECT 'TCC789', '' UNION
SELECT '4567', '' UNION
SELECT '2789', '' UNION
SELECT 'TCC829', ''

here is the original table (from ddl above)
ID1            ID2
2345
2789
4567
TCC123
TCC456
TCC789
TCC829

The result table needs to look like this:
ID1            ID2
2345    SEC2345
2789    SEC2789
4567    SEC4567
TCC123    SEC123
TCC456    SEC456
TCC789    SEC789
TCC829    SEC829

I can do this:
UPDATE tmp1 SET id2 = replace(id1, 'TCC', 'SEC')

But the result is this:
ID1           ID2
2345    2345
2789    2789
4567    4567
TCC123    SEC123
TCC456    SEC456
TCC789    SEC789
TCC829    SEC829

So I have to go back again to deal with the ID2 values that did not get a
prefix of SEC.

My goal is to perform this update operation in one shot. Here is my
pseudocode

Update tmp1 Set ID2 = Case Substring(ID1,1,3) = 'TCC' Then Replace(ID1,
'TCC', 'SEC') Else 'SEC' + ID1 End

How can I do this?

Thanks,
Rich

11 Aug 2006 9:41 PM

Michael Keating

Show quote

"Rich" <R***@discussions.microsoft.com> wrote in message
news:42BCB9CA-3EFC-44ED-B040-23CEAC6471A3@microsoft.com...
> Hello,
>
> I need to update the following table. ID2 is updated to the value of ID1
> except if ID1 has a Prefix of 'TCC' that must be replaced with 'SEC'. If
> ID1
> does not have a prefix of 'TCC', then 'SEC' must be added to the ID2 value
> plus the value of ID1.
>
> CREATE TABLE tmp1(ID1 varchar(20), ID2 varchar(20))
> INSERT INTO tmp1
> SELECT 'TCC123', '' UNION
> SELECT '2345', '' UNION
> SELECT 'TCC456', '' UNION
> SELECT 'TCC789', '' UNION
> SELECT '4567', '' UNION
> SELECT '2789', '' UNION
> SELECT 'TCC829', ''
>
> here is the original table (from ddl above)
> ID1            ID2
> 2345
> 2789
> 4567
> TCC123
> TCC456
> TCC789
> TCC829
>
>
> The result table needs to look like this:
> ID1            ID2
> 2345 SEC2345
> 2789 SEC2789
> 4567 SEC4567
> TCC123 SEC123
> TCC456 SEC456
> TCC789 SEC789
> TCC829 SEC829
>
>
> I can do this:
> UPDATE tmp1 SET id2 = replace(id1, 'TCC', 'SEC')
>
> But the result is this:
> ID1           ID2
> 2345 2345
> 2789 2789
> 4567 4567
> TCC123 SEC123
> TCC456 SEC456
> TCC789 SEC789
> TCC829 SEC829
>
> So I have to go back again to deal with the ID2 values that did not get a
> prefix of SEC.
>
> My goal is to perform this update operation in one shot. Here is my
> pseudocode
>
> Update tmp1 Set ID2 = Case Substring(ID1,1,3) = 'TCC' Then Replace(ID1,
> 'TCC', 'SEC') Else 'SEC' + ID1 End
>
> How can I do this?
>
> Thanks,
> Rich

you're pretty much there with the psuedocode, you just missed out the "WHEN"
(capitalised for dramatic emphasis <G>)

Update tmp1 Set ID2 = Case WHEN Substring(ID1,1,3) = 'TCC' Then Replace(ID1,
'TCC', 'SEC') Else 'SEC' + ID1 End

MFK.

11 Aug 2006 9:54 PM

Rich

Thanks very much.

I just tried my own version of that

UPDATE tmp1 SET id2 = CASE WHEN substring(ID1,1,3)='TCC' THEN replace(id1,
'TCC', 'SEC') ELSE 'SEC' + ID1 end

which looks exactly like yours :). Well, at least I am consistent with
everyone else.

Thank you for your reply.

Rich

Show quote

"Michael Keating" wrote:

>
> "Rich" <R***@discussions.microsoft.com> wrote in message
> news:42BCB9CA-3EFC-44ED-B040-23CEAC6471A3@microsoft.com...
> > Hello,
> >
> > I need to update the following table. ID2 is updated to the value of ID1
> > except if ID1 has a Prefix of 'TCC' that must be replaced with 'SEC'. If
> > ID1
> > does not have a prefix of 'TCC', then 'SEC' must be added to the ID2 value
> > plus the value of ID1.
> >
> > CREATE TABLE tmp1(ID1 varchar(20), ID2 varchar(20))
> > INSERT INTO tmp1
> > SELECT 'TCC123', '' UNION
> > SELECT '2345', '' UNION
> > SELECT 'TCC456', '' UNION
> > SELECT 'TCC789', '' UNION
> > SELECT '4567', '' UNION
> > SELECT '2789', '' UNION
> > SELECT 'TCC829', ''
> >
> > here is the original table (from ddl above)
> > ID1            ID2
> > 2345
> > 2789
> > 4567
> > TCC123
> > TCC456
> > TCC789
> > TCC829
> >
> >
> > The result table needs to look like this:
> > ID1            ID2
> > 2345 SEC2345
> > 2789 SEC2789
> > 4567 SEC4567
> > TCC123 SEC123
> > TCC456 SEC456
> > TCC789 SEC789
> > TCC829 SEC829
> >
> >
> > I can do this:
> > UPDATE tmp1 SET id2 = replace(id1, 'TCC', 'SEC')
> >
> > But the result is this:
> > ID1           ID2
> > 2345 2345
> > 2789 2789
> > 4567 4567
> > TCC123 SEC123
> > TCC456 SEC456
> > TCC789 SEC789
> > TCC829 SEC829
> >
> > So I have to go back again to deal with the ID2 values that did not get a
> > prefix of SEC.
> >
> > My goal is to perform this update operation in one shot. Here is my
> > pseudocode
> >
> > Update tmp1 Set ID2 = Case Substring(ID1,1,3) = 'TCC' Then Replace(ID1,
> > 'TCC', 'SEC') Else 'SEC' + ID1 End
> >
> > How can I do this?
> >
> > Thanks,
> > Rich
>
> you're pretty much there with the psuedocode, you just missed out the "WHEN"
> (capitalised for dramatic emphasis <G>)
>
> Update tmp1 Set ID2 = Case WHEN Substring(ID1,1,3) = 'TCC' Then Replace(ID1,
> 'TCC', 'SEC') Else 'SEC' + ID1 End
>
>
> MFK.
>
>
>