Update SQL Server 2005 Scripts

DJandLO

130 pts.

Tags:

SQL

SQL scripts

SQL Server 2005

SQL Server Scripts

I don't right code & don't have the confidence to try on my own so, I'm asking for help.

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.

I’m not sure if this makes a difference or not but, the Code info comes from Gen_table.code. Any assistance would be greatly appreciated - thanks!

Software/Hardware used:
SQL Server 2005

Asked: August 10, 2011 1:19 PM Last updated: March 31, 2012 6:58 PM

Answer Wiki

Last updated: August 10, 2011 3:36 PM GMT

Kalfree60 pts.
History
Contributors
- Ordered by most recent
- Kalfree60 pts.
- DJandLO130 pts.

Thanks. We'll let you know when a new response is added.

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

Discuss This Question: 3 Replies

carlosdl Aug 10, 2011 1:58 PM GMT
For the first part you would do something like this:
```
UPDATE iweb_conf_demo set Attendee_ribbons = NULL
```
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.
84,495 pointsBadges:
report
DJandLO Aug 10, 2011 2:23 PM GMT

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

130 pointsBadges:

report
DJandLO Aug 10, 2011 6:00 PM GMT

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

130 pointsBadges:

report