If i may ask, Is it possible to update multiple rows in a database at once. if yes, how?
I have a dbase of almost 100 people. Each one has a unique reference number like this: NPHCDA/MSS/109 but i want to update the records of the hundred people and include year into their reference number so it all looks like this: NPHCDA/MSS/10/109 where '10' stands for 2010
Netcode 33 Veteran Poster
izyrider 0 Newbie Poster
yes.
it goes something like this:
UPDATE sometable SET thisfield = LEFT(thisfield, 11) + '10/' + RIGHT(thisfield, 3)if the third '/' is not always the eleventh character and is not always followed by three characters you will need to work harder on the substring concatenation (perhaps CHARINDEX() either repeated three times or together with REVERSE(), perhaps PATINDEX()) - it all depends on your data. it will be slower, but with 100 rows you wont notice the additional millisecond or two.
if you are not using SQL-Server, you may have addtional string manipulators to play with (e.g. Split()) and you might have to adjust quote (' or ") and concatenator (+ or &)
please also consider doing exactly the opposite of what you plan!
not only keep the 10 "year" in a separate field, but split what appears to be three concatenated values into their own individual fields!
izy
Be a part of the DaniWeb community
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.