UPDATE a column based on the value of another column in the same table

Question

UPDATE a column REGNO based on the value of another column SERIAL and YEAR_ADMITTED in the same table.

SERIAL | REGNO | YEAR_ADMITTED | FULL_NAME
---------------------------------------------
0001   |       |  2011         | LUM Anye
1009   |       |  2014         | Hans Musah

You can reference other fields in the same row in the `SET` and `WHERE` clause in an `UPDATE` statement, if you didn't know. — gen_Eric, Jan 19 '15 at 21:22

score 1 · Answer 1 · answered Jan 19 '15 at 21:34

1

As per Fabricator's suggestion amended to include a where clause so you don't overwrite and existing REGNO.

UPDATE students SET REGNO = CONCAT( year_admitted, SERIAL )
WHERE REGNO IS NULL OR REGNO=''

You may have to CAST year_admitted if it is not already a string

UPDATE students SET REGNO = CONCAT( CAST(year_admitted as CHAR), SERIAL )
WHERE REGNO IS NULL OR REGNO=''

answered Jan 19 '15 at 21:34

Dijkgraaf

11,049
17
42
54

You are right, CAST it as YEAR(year_admitted). Thanks for pointing that out. – Nditah Jan 19 '15 at 21:54

score 0 · Answer 2 · edited Jan 19 '15 at 21:22

0

UPDATE `students` SET `REGNO` = ( SELECT CONCAT( YEAR( `year_admitted` ) , `SERIAL` ) )
WHERE SERIAL = SERIAL

edited Jan 19 '15 at 21:22

gen_Eric

223,194
41
299
337

answered Jan 19 '15 at 21:22

Nditah

1,429
19
23

2

how about `UPDATE students SET REGNO = CONCAT( YEAR( year_admitted ) , SERIAL )` – Fabricator Jan 19 '15 at 21:25
`WHERE SERIAL = SERIAL` does nothing whatsoever. Are you entering a variable there? – gen_Eric Jan 19 '15 at 21:26
Thank you Frabricator and @Rocket Hazmat – Nditah Jan 19 '15 at 21:59

UPDATE a column based on the value of another column in the same table

2 Answers2

Linked