Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Need to change column values in a database table

Posted on 2014-12-07
3
Medium Priority
?
135 Views
Last Modified: 2014-12-07
I have a database table shown below. The table actually has hundreds of records in it, so I am only showing 6 records.
In the code column, the same Code has been assigned to every record. Example : "ABC".
In reality, only the very first record should have a Code value of "ABC". Specifically only the record with the earliest
DateEntered should have the ABC code value. All other Code values should be unique.
I already have a function which generates unqique codes. I just need to figure out how to change each duplicate "ABC" code
(Except the first record with the earliest date).

Do I need to use a cursor to do this, or can this be done with an update statement? I am under a time crunch to get this done,
but am lost as to where to start. Can someone help me out? I would even be willing to pay someone for help on this.





select * from People

Social     Code  DateEntered
---------------------------------------
888659000  ABC  2014-12-07 08:20:58.407
666659700  ABC  2014-12-07 08:30:20.387
900659000  ABC  2014-12-07 08:32:10.320
778659000  ABC  2014-12-07 08:34:07.620
458659000  ABC  2014-12-07 08:35:18.853



Example of how the table might look after the Codes have been changed to unique values

Social     Code  DateEntered
---------------------------------------
888659000  ABC  2014-12-07 08:20:58.407
666659700  XBC  2014-12-07 08:30:20.387
900659000  VBC  2014-12-07 08:32:10.320
778659000  OIC  2014-12-07 08:34:07.620
458659000  NMB  2014-12-07 08:35:18.853
0
Comment
Question by:brgdotnet
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 40485634
if you have a function to generate the "unique" value, you can do this indeed.
update t
  set code = yourfunction()
 from yourtable t
where t.DateEntered > ( select min(x.DateEntered) from yourtable x )

if this assigns the same value to all the records, you need to change the function to accept some argument (even if the function does not need the value as such), and change like this:

update t
  set code = yourfunction( newid() )
 from yourtable t
where t.DateEntered > ( select min(x.DateEntered) from yourtable x )

hope this helps
0
 
LVL 2

Author Comment

by:brgdotnet
ID: 40485737
Wow, you are amazing. Thank you so much. I will test it out now.
0
 
LVL 2

Author Closing Comment

by:brgdotnet
ID: 40485760
Your awesome. Thank you so much. I just really need to start thinking in Sql.
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question