CODEWITHSUNDEEP
mysql
Cross Vander
Cross Vander

Reputation: 2157

MySQL update data by changing first character

I have mysql data table like this:

ID ---- Code
1  ---- A0001
2  ---- A0002
3  ---- A0003
4  ---- B0004
5  ---- B0005

I want to change (update) all data which first char is 'A' to 'B'. So the result would be like this:

ID ---- Code
1  ---- B0001
2  ---- B0002
3  ---- B0003
4  ---- B0004
5  ---- B0005

I already tried this query:

UPDATE `m_anggota` 
SET anggota_barcode = REPLACE(anggota_barcode, 'A', 'B') 
WHERE anggota_barcode LIKE 'A%'

But it's not working (0 rows affected). Anyone know why and how to fix this query?

Upvotes: 0

Views: 374

Answers (3)

Ankit Agrawal
Ankit Agrawal

Reputation: 2454

you can do dynamically like this 

update m_anggota
set anggota_barcode = REPLACE(anggota_barcode,LEFT(anggota_barcode, 1),'B') 
WHERE anggota_barcode LIKE 'A%';

Upvotes: 0

Vipin Jain
Vipin Jain

Reputation: 3756

Try This Its according to change your query

UPDATE `m_anggota` 
SET anggota_barcode = REPLACE(anggota_barcode,'A','B')
WHERE anggota_barcode LIKE 'A%';

but in your expected output column_name is different so query is

UPDATE `m_anggota` 
SET anggota_barcode = REPLACE(Code,'A','B')
WHERE Code LIKE 'A%';

Upvotes: 0

Matriac
Matriac

Reputation: 382

I think you just have to change

WHERE anggota_barcode LIKE 'C%'

By

WHERE anggota_barcode LIKE 'A%'

because you are looking for occurrence that have an A in begining , not a C.

Upvotes: 1

Related Questions