CODEWITHSUNDEEP
mysqldatabase
Rakesh
Rakesh

Reputation: 2790

SELECT and UPDATE MySQL query

I am trying to select a field from MySQL table and remove a substring from that field, and update that field.

For example: VAR1010 I want to replace and update VAR with SHS and the remaining part is the same. The result I am expecting is SHS1010

Here is the code:

SELECT SUBSTR(R.regnumber,4,4) as RegNo from registration R WHERE R.teacheremail='param';

UPDATE registration  SET regnumber = 'SHSRegNo' where teacheremail='param';

But it's updating as RegNo.

Upvotes: 0

Views: 255

Answers (3)

DevT
DevT

Reputation: 4933

use replace

UPDATE registration  SET regnumber =  replace(regnumber,'VAR','SHS')
where teacheremail='param';

Upvotes: 1

Jocelyn
Jocelyn

Reputation: 11403

Just do that:

UPDATE registration  SET regnumber = concat('SHS', substr(regnumber, 4, 4))
WHERE teacheremail='param';

Upvotes: 2

Taryn
Taryn

Reputation: 247730

You can do this in one UPDATE statement:

UPDATE registration  
SET regnumber = concat('SHS', substr(regnumber, 4, 4))
where teacheremail='param';

See SQL Fiddle with Demo

Upvotes: 3

Related Questions