Fakhrul’z | is a .Net Developer

Today is my “SQL script” day. A little bit of C# coding and the rest is a SQL script. My colleague needs to update her table for 99 rows. Of course she can change it one by one using a normal form but that will waste a lot of time. So I decided to make a script for her.

The script is like this:

select s.MatrixNo, u.Name,
'update mdl_user set idnumber = ''' + cast(s.MatrixNo as varchar(100)) + ''' where username = ''' + cast(u.Name as varchar(100)) + ''''
from Student s
inner join User u
where s.MatrixNo in
(
'MMU01223',
'MMU01224',
...
'MMU00004',
'MMU00977',
)

After execute this, it will generate the other script in column no 3.

It will produce something like this:

update mdl_user set idnumber = 'A4583E1C-EF25-4476-937B-15FAC1509303' where username = 'userName1'
update mdl_user set idnumber = 'A9145152-1354-487D-B5D7-2A619D46FDBE' where username = 'userName2'
...
update mdl_user set idnumber = 'E202DC3C-A157-4C13-AF32-9B6E011D2C36' where username = 'userName3'
update mdl_user set idnumber = '9533C44E-F3E2-49FD-A012-9B6E011D2C3F' where username = 'userName4'
update mdl_user set idnumber = 'A6D46AB8-14B7-4DAF-A362-9B6E011D2C3F' where username = 'userName5'
update mdl_user set idnumber = '148153AF-84C1-4833-8F2F-9B6E011D2C43' where username = 'userName6'

With this, we had saved a lot of time.