Help with A Update Script 1

[DJandLO]

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.

Any assistance would be greatly appreciated - thanks!

 

[DJandLO]

I meant to include this in my post:

This is about as far as I've gotten on the update portion.


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

 

[wbodger]

I am missing in your explanation how you know what records need to be updated to what code. If it is pre-defined and always the same, perhaps you could use a case statement (never tried that in an update statement, but not sure why it wouldn't work).

 

[DJandLO]

I'm just not sure on how to convert the single update string below to be able to update mutiple records (I'm not advanced in code at all)

The first set of # (0770, 0665) are the code I want to put on the records (the second set of 3's)

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

I hope this helps clarifying?

 

[SQLBill]

You could use dynamic SQL to create the statement. Start with a table that has two columns, one with the Attendee_ribbons values and one with the matching ID values. Then use a loop to go through each row and put the two values into variables which you then use in the dynamic SQL.

TableA
ID Attendee_ribbons
219910 0770

DECLARE @ID INT
DECLARE @ribbons INT
DECLARE @sqlcmd VARCHAR(1000)
DECLARE @cntr INT

SET @cntr = (SELECT COUNT(1) + 1 FROM TableA)

WHILE @cntr > 0

BEGIN

SET @ID = (SELECT TOP 1 ID FROM TableA)
SET @ribbons = (SELECT TOP 1 Attendee_Ribbons FROM TableA)

SET @sqlcmd = 'UPDATE iweb_conf_demo SET iweb_conf_demo.Attendee_ribbons = ' + @ribbons + ' where (iweb_conf_demo.ID = ' + @ID + ')' '

PRINT @sqlcmd
--EXEC (@sqlcmd)

DELETE 
TableA
WHERE ID = @ID

SET @cntr = @cntr -1
END

The PRINT command will let you see what is being run before you actually run it - great for testing. Once everything looks good, comment out the PRINT and uncomment the EXEC.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[DJandLO]

Hi SQLBill,

Thanks!

DJ