Database update SQL

Question

Suppose I have the following table

MemberTable

ID (1 letter + 5 digits) | Name | Type

And I have the following records

A00000 | Peter | A 
B00001 | Amy   | B 
C00002 | Susan | A
...

Now I need to update all the ID base on the follow criteria:

All start with 00-
Then comes with 2 digits base on the member type
Then add back the original ID except the 1st letter

Member type digits mapping

A -> 00 
B -> 01 
C -> 02

There the following would be the data I suppose to have after update

00-0000000 | Peter | A 
00-0100001 | Amy   | B 
00-0000002 | Susan | A

Suppose the mapping only a document reference, ie. do not have a table store the mapping details.

Any suggested update statement? Thanks for help.

Sigh, why do people put all that HTML markup in the question, instead of using the SO code formatting tool? I was going to reformat it, but it's too much trouble editing it. — Barmar
– Barmar, Commented Aug 16, 2013 at 4:37

Barmar · Accepted Answer · 2013-08-16 04:40:27Z

3

You didn't specify which DBMS, this is a MySQL answer:

update MemberTable
set id = concat('00-',
                case Type
                    when 'A' then '00'
                    when 'B' then '01'
                    when 'C' then '02'
                end,
                substr(id, 2))

The syntax for other databases will be slightly different, but the basic idea should be the same.

answered Aug 16, 2013 at 4:40

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Adriaan Stander · Accepted Answer · 2013-08-16 04:42:01Z

1

Lets say you are using SQL Server, you can try something like

DECLARE @Table TABLE(
        ID VARCHAR(20),
        [Type] VARCHAR(20)
)
INSERT INTO @Table VALUES ('A00000','A'),('B00001','B'),('C00002','A')

SELECT  *,
        '00-' + 
        CASE
            [Type] 
             WHEN 'A' THEN '00'
             WHEN 'B' THEN '01'
             WHEN 'C' THEN '02'
        END +
                        SUBSTRING(ID,2,LEN(ID)-1)
FROM    @Table

SQL Fiddle DEMO

answered Aug 16, 2013 at 4:42

Adriaan Stander

167k32 gold badges294 silver badges288 bronze badges

Collectives™ on Stack Overflow

Database update SQL

2 Answers 2

Comments

SQL Fiddle DEMO

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related