Update SQL Server 2005 Scripts
I don't right code & don't have the confidence to try on my own so, I'm asking for help.
Software/Hardware used:
SQL Server 2005
- I need to run an update script to clear out the data from iweb_conf_demo.Attendee_ribbons.
- Then I’ll need to run multiple scripts to update multiple records on the same field iweb_conf_demo.Attendee_ribbons for different groups of registrants. For example ID # 123456, 123465, 654321 & 543216 will need to be updated with the Code 0020 and Code 0650.
Software/Hardware used:
SQL Server 2005
ASKED:
August 10, 2011 1:19 PM
UPDATED:
March 31, 2012 6:58 PM
Answer Wiki:
Hello DJ,
For the first request, you can use
<pre> UPDATE iweb_conf_demo
SET Attendee_ribbons = NULL</pre>
This will clear ALL information from that field in the table. Also, this step seems unnecessary as you will be replacing the values anyway.
For your second request, based on what I could gather about the structure of the DB, You could use something like this:
<pre>UPDATE iweb_conf_demo
SET iweb_conf_demo.Attendee_ribbons= Gen_table.code
FROM iweb_conf_demo
INNER JOIN Gen_table.code
ON (iweb_conf_demo.Attendee_ribbons.ID = Gen_table.code.ID)</pre>
This will set the attendee_ribbons value to the corresponding code value where the IDs match in both tables.
If I made an incorrect assumption about the structure of your database, please provide the corrected details and I will gladly alter the suggested scripts to fit your environment.
Regards,
Kyle
To see all answers submitted to the Answer Wiki: View Answer History.
For the first part you would do something like this:
This would set to NULL the Attendee_ribbons field in all of the records in the table, without any condition.
This is pretty basic. You should have a test database to try and learn.
For the second part you could write multiple updates or use a CASE construct to do it in one shot. You didn’t specify in which cases you want to update the field with 0020 and 0650.
If you are supposed to do this kind of task, you should start learning SQL.
Hi Carlos,
Thanks for the help.
No, I don’t get these types of requests normally but, I have tried to do SQL in a development environment. Unfortunately, I don’t always have a lot of time to devote to playing around with it.
Thanks again,
DJ
Hi Kyle,
The second part of your answer I had to tweak a little but it worked on a single record! I guess I’ll need to add commas separating if I have multiple records to update?
UPDATE iweb_conf_demo
SET iweb_conf_demo.Attendee_ribbons = ‘CODE’
where (iweb_conf_demo.ID = ‘##’)
Thanks!
Darnell