How To I Update The Id In My All Records?

Question

0.00/5 (No votes)

See more:

Dear all,

i have thousand of records to update.my question is like this in my table this is the Reg_no(primary key)

REG_NO
Ex VT/abc/2015/001
VT/abc/2015/002
VT/abc/2015/003
VT/abc/2015/004
VT/abc/2015/005
.
.
more

but i want to change this 3 token 2015 into 2014
ex :
REG_NO
VT/abc/2014/001
VT/abc/2014/002
VT/abc/2014/003
VT/abc/2014/004
.
.
more

please help me

Posted 24-Mar-15 4:17am

nuke_infer

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ZurdoDev · Accepted Answer · 2015-03-24T05:13:00

Solution 1

Try something like this:

SQL

UPDATE table1
SET field1 = REPLACE(field1, '2014', '2015')

However, if these are really primary keys and may be pointing to other tables then you may have a big problem. I would suggest you don't do primary keys like this but use a guid or identity field instead.

Posted 24-Mar-15 5:13am

ZurdoDev

Comments

nuke_infer 24-Mar-15 11:30am

you mean UPDATE table1 SET reg_no = (reg_no,'2014',2015); ?

ZurdoDev 24-Mar-15 11:30am

UPDATE table1 SET reg_no = REPLACE(reg_no,'2014',2015)

This will replace the string 2014 in reg_no to 2015.

nuke_infer 24-Mar-15 11:33am

but friend there is mixing with 2014 and 2015 in that key. therefor i want to check these record id if it is 2015 then change as a 2014 if not its ok like wise.how can i do it? please help me i confused about this

ZurdoDev 24-Mar-15 11:33am

I don't understand.

nuke_infer 24-Mar-15 11:36am

ex : VT/abc/2014/001
VT/abc/2014/002
VT/abc/2014/003
VT/abc/2015/004
VT/abc/2015/005

now it has a 2014 and 2015 both.but i want to change this 2015 into 2014 ?

ZurdoDev 24-Mar-15 11:40am

This doesn't make any sense. You wrote you want to change "this 2015 into 2015." You can't change 2015 into 2015, they are the same thing.

Give one simple example, and perhaps that will help.

ZurdoDev 24-Mar-15 11:40am

If you want 2015 to be 2014 then reverse the order of the parameters in the REPLACE function. Just look it up, it is easier than you think it might be.

Richard Deeming 24-Mar-15 11:39am

I think he wants the arguments the other way round:
Replace(field1, '2015', '2014')

nuke_infer 24-Mar-15 12:17pm

thank you all.ill proceed and if there is problem ill be back soon.thanx all

ZurdoDev 24-Mar-15 12:22pm

You're welcome. Glad to hear it.

nuke_infer 24-Mar-15 23:45pm

zero row effected :(

nuke_infer 25-Mar-15 0:29am

UPDATE tableA SET branch_id = REPLACE( branch_id, "2015", "2014" ) 0 row effected
why is that. i think it is considering this part also VT/ABC/ in my primary key doesnt it?

ZurdoDev 25-Mar-15 7:38am

If you do SELECT branch_id FROM tableA, what are some of the results?

nuke_infer 25-Mar-15 12:12pm

SELECT branch_id FROM tableA retrieving branch id like VT/ABC/2014/001,VT/ABC/2014/001,VT/ABC/2014/002,VT/ABC/2014/003,VT/ABC/2015/004,VT/ABC/2015/005 ... more

after setting UPDATE tableA SET branch_id =REPLACE(branch_id,"2015","2014")

result begin 0 row effected

i did in my c panel php myadmin. but this is locally i did using dummy table it is successfully affected. any reason?

ZurdoDev 25-Mar-15 12:13pm

You probably got errors because the key is used on other tables. Just a guess.

nuke_infer 25-Mar-15 12:23pm

this table is no relationship with other tables.thats mean no foreign key

ZurdoDev 25-Mar-15 12:31pm

Try something like

SELECT branch_id, REPLACE(branch_id,"2015","2014") AS new_branch_id
FROM tableA

to see what you get.

nuke_infer 25-Mar-15 12:35pm

ok dear ill check it