October 13, 2009 at 8:53 pm
Good afternoon all,
I've been looking through the other threads re: mutliple row updates, but none of them hit the nail on the head for me. I am currently using SQL2000. (migrating right now to 2008 hooray!:-D)
In the context of a school, I have a table called "subjects". Example:
su.sukey (subject unique ID) = '01MAT' (varchar)
su.strand_heading01 = 'Number Sense' (varchar)
su.strand01 = 'Can count up to 10 confidently' (text)
and the strand_heading* and strand* fields will increment from 01 to 15, and the sukey field is made up of school_year + subject acronym. ie 01mat = year 1 maths. This will increment from 01MAT to 06MAT for this example.
What I want to do is fill in the strand_heading* and strand* fields for one subject (su.sukey = 01mat) and then use that data to populate the rest of the subjects. (02mat, 03mat, 04mat etc etc).
This does the job, but I cant help but feel there is a better way? In Java I was using arrays to increment the number portion of the fields.
declare @subj varchar(5)
set @subj = '01mat'
update SU
set [STRAND_HEADING01] = (select strand_heading01 from su where su.sukey = @subj) where (right(su.sukey,3) = right(@subj,3)) and su.sukey <> @subj
update SU
set [STRAND_HEADING02] = (select strand_heading02 from su where su.sukey = @subj) where (right(su.sukey,3) = right(@subj,3)) and su.sukey <> @subj
update SU
set [STRAND_HEADING03] = (select strand_heading03 from su where su.sukey = @subj) where (right(su.sukey,3) = right(@subj,3)) and su.sukey <> @subj
This worked for me, but I hit a snag when I got to the strand* fields. As the field is datatype TEXT, I get an error that text fields cannot be used in subqueries.
Any point in the right direction would be fantastic. Thank you all in advance for your time.
- DamienB
October 14, 2009 at 9:26 am
Just a note for the future, consider moving away from using TEXT fields to Varchar(Max) or Nvarchar(Max).
As for the single statemement, consider something like this. (coded freehand and untested)
UPDATE SU
SET [STRAND_HEADING01] = su1.[STRAND_HEADING01]
,[STRAND_HEADING02] = su1.[STRAND_HEADING02]
,[STRAND_HEADING03] = su1.[STRAND_HEADING03]
FROM SU
JOIN SU su1 on su1.sukey = @subj and right(su1.sukey,3) = right(@subj,3) and su1.sukey <> @subj
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 15, 2009 at 5:32 pm
Thank you for the reply 🙂
That's a clever way to do it, thanks for the help.
October 21, 2009 at 6:37 pm
For anyone trying to solve a similar problem, this is the tested working code that I am using:
declare @subj char(5)
set @subj = '01mat'
update SU
setsu.strand_heading01 = su1.strand_heading01,
su.strand01 = su1.strand01,
su.strand_heading02 = su1.strand_heading02,
su.strand02 = su1.strand02,
su.strand_heading03 = su1.strand_heading03,
su.strand03 = su1.strand03,
su.strand_heading04 = su1.strand_heading04,
su.strand04 = su1.strand04 -- and all the way to 15 rinse repeat
fromsu
left join su su1 on su1.sukey = @subj
where right(su.sukey,3) = right(@subj,3)
October 21, 2009 at 10:06 pm
Thanks for coming back and posting your working code, Damien. It's a nice thing to do.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply