Home > IT Answers > SQL Server > Update SQL Server 2005 Scripts
DJandLO
130 pts.
 Update SQL Server 2005 Scripts
SQL Server 2005, SQL, SQL scripts, 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: Aug 10, 2011  1:19 PM GMT
UPDATED: March 31, 2012  6:58:52 PM GMT
RELATED QUESTIONS
Kalfree
60 pts.
 
  Help
 Approved Answer - Chosen by DJandLO (Question Asker)
Hello DJ,

For the first request, you can use
 UPDATE iweb_conf_demo
 SET Attendee_ribbons = NULL


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:

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)


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
ANSWERED:  Aug 10, 2011  3:36 PM (GMT)  by Kalfree   60 pts.
 
Other Answers:
Hello DJ,

For the first request, you can use
 UPDATE iweb_conf_demo
 SET Attendee_ribbons = NULL


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:

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)


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
Last Wiki Answer Submitted:  Aug 10, 2011  3:36 PM (GMT)  by  Kalfree   60 pts.
Latest Answer Wiki Contributors:  Carlosdl   60,255 pts., DJandLO   130 pts.
To see other answers submitted to the Answer Wiki View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



 

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.

Carlosdl
 60,255 pts.
 

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

DJandLO
 130 pts.
 

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

DJandLO
 130 pts.