Help with updating Multiple Records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with updating Multiple Records

Author

Topic

djandlo
Starting Member

1 Post

Posted - 2011-08-10 : 14:49:54

Hi,I'm not totally sure on how to update multiple records with multiple codes so, I'm asking for help.

?(I have an answer on this 1st step so,this is just background info) I need to run an update script to clear out the data from iweb_conf_demo.Attendee_ribbons.

?After I've cleared the records - I'll need to run multiple scripts to update multiple records on the iweb_conf_demo.Attendee_ribbons for different groups of registrants. For example ID #'s 123456, 123465, 654321 & 543216 will need to be updated with the Codes 0020 & Code 0650 found in Gen_table.code.

I was sucessful in updating a single record with the script below:

UPDATE iweb_conf_demo SET iweb_conf_demo.Attendee_ribbons = '0770' where (iweb_conf_demo.ID = '219910')

Any assistance would be greatly appreciated - thanks

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-08-10 : 17:30:57

Every SQL forum or newsgroup expects that you will post DDL, sample data and clear specifications which might might include output. This is explained in the FAQ section. We can not read minds; what would we need to know to do your job for you?

Tables must have keys and should have DRI, constraints, and all the basic features of a schema. You should know use ISO-8601 Standards for temporal data, avoid needless dialect, basic data modeling and use ISO-11179 Standards for data element names. Please tell us if you can change the DDL.

>> Hi,I'm not totally sure on how to update multiple records [sic: rows are not records] with multiple codes so, I'm asking for help.

>> I have an answer on this 1st step so,this is just background info) I need to run an update script to clear out the data from Iweb_Conf_Demo.attendee_ribbons. <

That is not an UPDATE; it is simply:

DELETE FROM Iweb_Conf_Demo;

>> after I've cleared the records [sic] - I'll need to run multiple scripts to update multiple records [sic] on the iattendee_ribbons column for different groups of registrants. For example attendee_id in {123456, 123465, 654321, 543216} will need to be updated with the attendance_code in {0020, 0650} found in Gen_table.attendance_code. <<

There is no such thing as a generic “code”; it has to be a “<something>_code” and the same rule applies to generic magical universal “id” instead. I also have to assume that “ribbons” is singular and not a list.

>> I was successful in updating a single record [sic] with the script below: <<

Think sets

UPDATE Iweb_Conf_Demo
SET attendee_ribbons = '0770'
WHERE attendee_id = '219910')

Look up the MERGE statement. Build a table of pairs of (attendee_id, attendee_ribbons) and use it:

MERGE INTO Iweb_Conf_Demo AS Target
USING (SELECT X.*
FROM (VALUES ('123456', '0020'),
(..), (..))
AS X(attendee_id, attendee_ribbons))
AS Source
ON Source.attendee_id = Target.attendee_id
WHEN MATCHED
THEN UPDATE
SET SET attendee_ribbons = Source.attendee_ribbons
WHEN NOT MATCHED
THEN INSERT (attendee_id, attendee_ribbons);

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources