Solved

TSQL Help - updating a filed in table2 with multiple records from table1

Posted on 2013-02-01
14
390 Views
Last Modified: 2013-02-02
Experts recently helped me with this problem, but now I have tried to implement it with two different tables and it is looping endlessly even with only 10 records in one table and 3 records in the 2nd.  In the previous post "http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_28002232.html" I was given two solutions one used a function and the other the code below from vadimrapp1.
declare @dummy int
select @dummy=1

while @@rowcount>0 begin
      update table2
      set sections=isnull(sections,'') + ',' + table1.section
      from table1 join table2 on table1.semesterps=table2.semesterps
         and table1.semester=table2.semester
         and table1.course=table2.course
         and isnull(table2.sections,'') not like '%' + table1.section + '%'
          -- assuming all courses have same number of digits
end
update table2 set sections = substring(sections,2,9999)

I thought I had the code above working, but it is not with this new data.

Here is code to produce table1
CREATE TABLE table1 (
     SemesterPS varchar(4),
     Crs_Subj varchar(4),
     Crs_Num varchar(3),
     Crs_Section varchar(3),
     Sequence int,
     NoteText varchar(255))

INSERT INTO table1 (SemesterPS, Crs_Subj, Crs_Num, Crs_Section, [Sequence], NoteText)
SELECT '2133', 'ACCT', '200', '194', 1, 'This course is Online.'
UNION All
SELECT '2133', 'ACCT', '200', '194', 2, 'Non resident students participant policy.'
UNION All
SELECT '2133', 'ACCT', '200', '194', 3, 'Cannot register for more than two online courses.'
UNION All
SELECT '2133', 'ACCT', '201', '195', 1, 'This course is Online.'
UNION All
SELECT '2133', 'ACCT', '201', '195', 2, 'Non resident students participant policy.'
UNION All
SELECT '2133', 'ACCT', '201', '195', 3, 'Cannot register for more than two online courses.'
UNION All
SELECT '2133', 'AGED', '220', '194', 1, 'This course is Online.'
UNION All
SELECT '2133', 'AGED', '220', '194', 2, 'Non resident students: contact 800/597-1444 about the site participant policy.'
UNION All
SELECT '2135', 'AGED', '220', '194', 3, 'A 400 level section meets with a 600 level section.'
UNION All
SELECT '2135', 'AGED', '220', '194', 4, 'Section satisfies the University multicultural requirement.'

GO

Open in new window


Here is the code to product table2
CREATE TABLE table2 (
     SemesterPS varchar(4),
     Crs_Subj varchar(4),
     Crs_Num varchar(3),
     Crs_Section varchar(3),
     Notes varchar(1200))

INSERT INTO table2 (SemesterPS, Crs_Subj, Crs_Num, Crs_Section, Notes)
SELECT '2133', 'ACCT', '200', '194', Null
UNION All
SELECT '2133', 'ACCT', '201', '195', Null
UNION All
SELECT '2133', 'AGED', '220', '194', Null

GO

Open in new window


Table1 is created by our mainframe system and contains multiple records for a course with different notes. We want the tsql to combine all note lines for 1 course (in a semester) and update a single field in table2 which as other course attributes.  I don't want to use a function until we upgrade sql server.  Here is the code that is looping endlessly.
-- these two lines were supplied by vadimrapp1. I don't know what -- they do or if they are needed.
declare @dummy int
select @dummy=1

while @@rowcount>0 
begin
	update table2
	set Notes = ' ' + table1.NoteText
	from table2 join table1 
	on table2.SemesterPS = table1.SemesterPS
	   and table2.Crs_Subj = table1.Crs_Subj
	   and table2.Crs_Num = table1.Crs_Num
	   and table2.Crs_Section = table1.Crs_Section
	   and isnull(table2.Notes,'') not like '%' + table1.NoteText + '%'
end
update table2 set Notes = substring(Notes,2,1200) WHERE Left(Notes,1) = ' '

Open in new window


Thanks for your help.
0
Comment
Question by:scover22
  • 8
  • 4
  • 2
14 Comments
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
Comment Utility
OK, when you SET NOTES you are basically resetting to the last notetext, so it always finds another notetext.

You need to build the string up.

So please use the following set :

	set Notes = isnull(notes+' ','') + table1.NoteText

Open in new window


And the reason for the dummy select is to get a value in @@rowcount
0
 
LVL 42

Expert Comment

by:EugeneZ
Comment Utility
check - @@ROWCOUNT
http://technet.microsoft.com/en-us/library/ms187316.aspx

about WHILE
http://msdn.microsoft.com/en-us/library/ms178642.aspx

try this code
drop table table1 
CREATE TABLE table1 (
     SemesterPS varchar(4),
     Crs_Subj varchar(4),
     Crs_Num varchar(3),
     Crs_Section varchar(3),
     Sequence int,
     NoteText varchar(255))

INSERT INTO table1 (SemesterPS, Crs_Subj, Crs_Num, Crs_Section, [Sequence], NoteText)
SELECT '2133', 'ACCT', '200', '194', 1, 'This course is Online.'
UNION All
SELECT '2133', 'ACCT', '200', '194', 2, 'Non resident students participant policy.'
UNION All
SELECT '2133', 'ACCT', '200', '194', 3, 'Cannot register for more than two online courses.'
UNION All
SELECT '2133', 'ACCT', '201', '195', 1, 'This course is Online.'
UNION All
SELECT '2133', 'ACCT', '201', '195', 2, 'Non resident students participant policy.'
UNION All
SELECT '2133', 'ACCT', '201', '195', 3, 'Cannot register for more than two online courses.'
UNION All
SELECT '2133', 'AGED', '220', '194', 1, 'This course is Online.'
UNION All
SELECT '2133', 'AGED', '220', '194', 2, 'Non resident students: contact 800/597-1444 about the site participant policy.'
UNION All
SELECT '2135', 'AGED', '220', '194', 3, 'A 400 level section meets with a 600 level section.'
UNION All
SELECT '2135', 'AGED', '220', '194', 4, 'Section satisfies the University multicultural requirement.'

GO
drop table table2
CREATE TABLE table2 (
     SemesterPS varchar(4),
     Crs_Subj varchar(4),
     Crs_Num varchar(3),
     Crs_Section varchar(3),
     Notes varchar(1200))

INSERT INTO table2 (SemesterPS, Crs_Subj, Crs_Num, Crs_Section, Notes)
SELECT '2133', 'ACCT', '200', '194', Null
UNION All
SELECT '2133', 'ACCT', '201', '195', Null
UNION All
SELECT '2133', 'AGED', '220', '194', Null

--before

select * from table1
select * from table2





	update table2
	set Notes = table1.NoteText
	 
	from table2 INNER JOIN
	(
	select
    SemesterPS
,Crs_Num,Crs_Section,Crs_Subj,
    stuff((
        select ' ' + t.NoteText
        from table1 t
        where  t.SemesterPS=t1.SemesterPS and
Crs_Num =t1.Crs_Num and
Crs_Section =t1.Crs_Section and
Crs_Subj=t1.Crs_Subj
        order by t.Sequence
        for xml path('')
    ),1,1,'') as NoteText
from table1 t1
group by SemesterPS
,Crs_Num,Crs_Section,Crs_Subj) table1
	on table2.SemesterPS = table1.SemesterPS
	   and table2.Crs_Subj = table1.Crs_Subj
	   and table2.Crs_Num = table1.Crs_Num
	   and table2.Crs_Section = table1.Crs_Section
	   and isnull(table2.Notes,'') not like '%' + table1.NoteText + '%'
	      
--after
	select * from table1
select * from table2

Open in new window

0
 
LVL 42

Expert Comment

by:EugeneZ
Comment Utility
result
SemesterPS	Crs_Subj	Crs_Num	Crs_Section	Notes
2133	ACCT	200	194	This course is Online. Non resident students participant policy. Cannot register for more than two online courses.
2133	ACCT	201	195	This course is Online. Non resident students participant policy. Cannot register for more than two online courses.
2133	AGED	220	194	This course is Online. Non resident students: contact 800/597-1444 about the site participant policy.

Open in new window

0
 

Author Comment

by:scover22
Comment Utility
Thank you.  This is working.

I want to capture the number of records updated and I've tried putting "Select @RowsAffected = @@RowCount" inside and outside the WHILE loop.  If I put the statement inside the loop, I get the correct count, but only get the first note. If I put it outside the loop the value is 0 even though it updated 4 records.  What do you suggest?

Also I don't seem to need this line anymore
"update table2 set Notes = substring(Notes,2,1200) WHERE Left(Notes,1) = ' '"
Does that make sense given the SET you gave me?

Thanks.
0
 
LVL 42

Expert Comment

by:EugeneZ
Comment Utility
you can add
after update statement
SELECT @@ROWCOUNT

and\or use or use output inside update

-

no need to use loop for this case  and last your update
0
 

Author Comment

by:scover22
Comment Utility
Sorry, I don't understand your answer. Here is my code. Please modify it to show me what you mean.  Thanks.
ALTER       PROCEDURE dbo.UDO_updateNotes @user varchar(10)
AS
SET NOCOUNT ON; 
DECLARE @msg varchar(250)
DECLARE @RowsAffected as integer
-- put value in @@rowcount with dummy statements
declare @dummy int

EXEC UDO_ImportMsg @user, 'Update Notes started.'

select @dummy=1

while @@rowcount>0 
begin
	update SemesterCourses
	set Notes = isnull(Notes+' ','') + NotesImport.NoteText
	from SemesterCourses join NotesImport 
	on SemesterCourses.SemesterPS = NotesImport.SemesterPS
	   and SemesterCourses.Crs_Subj = NotesImport.Crs_Subj
	   and SemesterCourses.Crs_Num = NotesImport.Crs_Num
	   and SemesterCourses.Crs_Section = NotesImport.Crs_Section
	   and isnull(SemesterCourses.Notes,'') not like '%' + NotesImport.NoteText + '%'
end

update SemesterCourses 
	set Notes = substring(Notes,2,1200)
	WHERE Left(Notes,1) = ' '

SET @msg = CAST(@RowsAffected  AS varchar) + ' SemesterCourses Notes updated.'
EXEC UDO_ImportMsg @user, @msg

Open in new window

0
 
LVL 42

Expert Comment

by:EugeneZ
Comment Utility
did you try the above posted example?
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 42

Expert Comment

by:EugeneZ
Comment Utility
ALTER       PROCEDURE dbo.UDO_updateNotes @user varchar(10)
AS
SET NOCOUNT ON; 
DECLARE @msg varchar(250)
DECLARE @RowsAffected as integer
-- put value in @@rowcount with dummy statements
declare @dummy int

EXEC UDO_ImportMsg @user, 'Update Notes started.'

select @dummy=1

	update SemesterCourses
	set Notes = table1.NoteText
	 
	from SemesterCourses  INNER JOIN
	(
	select
    SemesterPS
,Crs_Num,Crs_Section,Crs_Subj,
    stuff((
        select ' ' + t.NoteText
        from NotesImport t
        where  t.SemesterPS=t1.SemesterPS and
Crs_Num =t1.Crs_Num and
Crs_Section =t1.Crs_Section and
Crs_Subj=t1.Crs_Subj
        order by t.Sequence
        for xml path('')
    ),1,1,'') as NoteText
from NotesImport t1
group by SemesterPS
,Crs_Num,Crs_Section,Crs_Subj) table1
	on SemesterCourses.SemesterPS = table1.SemesterPS
	   and SemesterCourses.Crs_Subj = table1.Crs_Subj
	   and SemesterCourses.Crs_Num = table1.Crs_Num
	   and SemesterCourses.Crs_Section = table1.Crs_Section
	   and isnull(SemesterCourses.Notes,'') not like '%' + table1.NoteText + '%'
set @RowsAffected=@@ROWCOUNT
--update SemesterCourses 
--	set Notes = substring(Notes,2,1200)
--	WHERE Left(Notes,1) = ' '

SET @msg = CAST(@RowsAffected  AS varchar) + ' SemesterCourses Notes updated.'
EXEC UDO_ImportMsg @user, @msg 
go

Open in new window

0
 

Author Comment

by:scover22
Comment Utility
I can't use your code with xml see comment from matthewspatrick.  I have SQL server 7 in my test environment and SQL server 2000 in production.


by: matthewspatrick Posted on 2013-01-21 at 10:04:43ID: 38801931
I apologize, I am just now noticing that you indicated SQL Server 2000 as your version.  FOR XML PATH debuted in SQL Server 2005, so it will not work in 2000.

Please clarify which version of SQL Server you are using.
0
 
LVL 42

Expert Comment

by:EugeneZ
Comment Utility
ok,,

please try this one :

	
drop table table1 
CREATE TABLE table1 (
     SemesterPS varchar(4),
     Crs_Subj varchar(4),
     Crs_Num varchar(3),
     Crs_Section varchar(3),
     Sequence int,
     NoteText varchar(255))

INSERT INTO table1 (SemesterPS, Crs_Subj, Crs_Num, Crs_Section, [Sequence], NoteText)
SELECT '2133', 'ACCT', '200', '194', 1, 'This course is Online.'
UNION All
SELECT '2133', 'ACCT', '200', '194', 2, 'Non resident students participant policy.'
UNION All
SELECT '2133', 'ACCT', '200', '194', 3, 'Cannot register for more than two online courses.'
UNION All
SELECT '2133', 'ACCT', '201', '195', 1, 'This course is Online.'
UNION All
SELECT '2133', 'ACCT', '201', '195', 2, 'Non resident students participant policy.'
UNION All
SELECT '2133', 'ACCT', '201', '195', 3, 'Cannot register for more than two online courses.'
UNION All
SELECT '2133', 'AGED', '220', '194', 1, 'This course is Online.'
UNION All
SELECT '2133', 'AGED', '220', '194', 2, 'Non resident students: contact 800/597-1444 about the site participant policy.'
UNION All
SELECT '2135', 'AGED', '220', '194', 3, 'A 400 level section meets with a 600 level section.'
UNION All
SELECT '2135', 'AGED', '220', '194', 4, 'Section satisfies the University multicultural requirement.'

GO
drop table table2
CREATE TABLE table2 (
     SemesterPS varchar(4),
     Crs_Subj varchar(4),
     Crs_Num varchar(3),
     Crs_Section varchar(3),
     Notes varchar(1200))

INSERT INTO table2 (SemesterPS, Crs_Subj, Crs_Num, Crs_Section, Notes)
SELECT '2133', 'ACCT', '200', '194', Null
UNION All
SELECT '2133', 'ACCT', '201', '195', Null
UNION All
SELECT '2133', 'AGED', '220', '194', Null

--before

select * from table1
select * from table2
	



Declare  @SemesterPS varchar(4),
     @Crs_Subj varchar(4),
     @Crs_Num varchar(3),
     @Crs_Section varchar(3),
     @Sequence int,
     @NoteText varchar(255)
declare  @str varchar(8000)



DECLARE note_cursor CURSOR FOR  
select distinct table1.SemesterPS,table1.Crs_Subj ,table1.Crs_Num,table1.Crs_Section
from table2 inner join table1 
	on table2.SemesterPS = table1.SemesterPS
	   and table2.Crs_Subj = table1.Crs_Subj
	   and table2.Crs_Num = table1.Crs_Num
	   and table2.Crs_Section = table1.Crs_Section
	   and isnull(table2.Notes,'') not like '%' + table1.NoteText + '%'


OPEN note_cursor   
FETCH NEXT FROM note_cursor INTO @SemesterPS,@Crs_Subj ,@Crs_Num,@Crs_Section

WHILE @@FETCH_STATUS = 0   
BEGIN   
  
  --select @SemesterPS,@Crs_Subj ,@Crs_Num,@Crs_Section 
  SELECT @str  = COALESCE(@str ,'') + NoteText + ' '  
FROM  table1 
    where  SemesterPS=@SemesterPS and
Crs_Num =@Crs_Num and
Crs_Section =@Crs_Section and
Crs_Subj=@Crs_Subj
  SELECT @str

  update table2
  set table2.Notes=@str
  FROM  table2 
    where  SemesterPS=@SemesterPS and
Crs_Num =@Crs_Num and
Crs_Section =@Crs_Section and
Crs_Subj=@Crs_Subj
set @str=null
       FETCH NEXT FROM note_cursor   INTO @SemesterPS,@Crs_Subj ,@Crs_Num,@Crs_Section 
END   

CLOSE note_cursor  
DEALLOCATE note_cursor

select * from table2

Open in new window

0
 
LVL 42

Expert Comment

by:EugeneZ
Comment Utility
included count
	
drop table table1 
CREATE TABLE table1 (
     SemesterPS varchar(4),
     Crs_Subj varchar(4),
     Crs_Num varchar(3),
     Crs_Section varchar(3),
     Sequence int,
     NoteText varchar(255))

INSERT INTO table1 (SemesterPS, Crs_Subj, Crs_Num, Crs_Section, [Sequence], NoteText)
SELECT '2133', 'ACCT', '200', '194', 1, 'This course is Online.'
UNION All
SELECT '2133', 'ACCT', '200', '194', 2, 'Non resident students participant policy.'
UNION All
SELECT '2133', 'ACCT', '200', '194', 3, 'Cannot register for more than two online courses.'
UNION All
SELECT '2133', 'ACCT', '201', '195', 1, 'This course is Online.'
UNION All
SELECT '2133', 'ACCT', '201', '195', 2, 'Non resident students participant policy.'
UNION All
SELECT '2133', 'ACCT', '201', '195', 3, 'Cannot register for more than two online courses.'
UNION All
SELECT '2133', 'AGED', '220', '194', 1, 'This course is Online.'
UNION All
SELECT '2133', 'AGED', '220', '194', 2, 'Non resident students: contact 800/597-1444 about the site participant policy.'
UNION All
SELECT '2135', 'AGED', '220', '194', 3, 'A 400 level section meets with a 600 level section.'
UNION All
SELECT '2135', 'AGED', '220', '194', 4, 'Section satisfies the University multicultural requirement.'

GO
drop table table2
CREATE TABLE table2 (
     SemesterPS varchar(4),
     Crs_Subj varchar(4),
     Crs_Num varchar(3),
     Crs_Section varchar(3),
     Notes varchar(1200))

INSERT INTO table2 (SemesterPS, Crs_Subj, Crs_Num, Crs_Section, Notes)
SELECT '2133', 'ACCT', '200', '194', Null
UNION All
SELECT '2133', 'ACCT', '201', '195', Null
UNION All
SELECT '2133', 'AGED', '220', '194', Null

--before

--select * from table1
--select * from table2
	



Declare  @SemesterPS varchar(4),
     @Crs_Subj varchar(4),
     @Crs_Num varchar(3),
     @Crs_Section varchar(3),
     @Sequence int,
     @NoteText varchar(255),
	 @RowsAffected int,
  @str varchar(8000)

  set @RowsAffected=0

DECLARE note_cursor CURSOR FOR  
select distinct table1.SemesterPS,table1.Crs_Subj ,table1.Crs_Num,table1.Crs_Section
from table2 inner join table1 
	on table2.SemesterPS = table1.SemesterPS
	   and table2.Crs_Subj = table1.Crs_Subj
	   and table2.Crs_Num = table1.Crs_Num
	   and table2.Crs_Section = table1.Crs_Section
	   and isnull(table2.Notes,'') not like '%' + table1.NoteText + '%'


OPEN note_cursor   
FETCH NEXT FROM note_cursor INTO @SemesterPS,@Crs_Subj ,@Crs_Num,@Crs_Section

WHILE @@FETCH_STATUS = 0   
BEGIN   
 
  --select @SemesterPS,@Crs_Subj ,@Crs_Num,@Crs_Section 
  SELECT @str  = COALESCE(@str ,'') + NoteText + ' '  
FROM  table1 
    where  SemesterPS=@SemesterPS and
Crs_Num =@Crs_Num and
Crs_Section =@Crs_Section and
Crs_Subj=@Crs_Subj
  --SELECT @str

  update table2
  set table2.Notes=@str
  FROM  table2 
    where  SemesterPS=@SemesterPS and
Crs_Num =@Crs_Num and
Crs_Section =@Crs_Section and
Crs_Subj=@Crs_Subj 

select @RowsAffected=@RowsAffected+@@ROWCOUNT
set @str=null

       FETCH NEXT FROM note_cursor   INTO @SemesterPS,@Crs_Subj ,@Crs_Num,@Crs_Section 
END   

CLOSE note_cursor  
DEALLOCATE note_cursor

select * from table2
select @RowsAffected

Open in new window

0
 

Author Closing Comment

by:scover22
Comment Utility
mark_wills gave me exactly what I needed to get the simple code to work. I figured out how to get the correct rowcount myself.  EugeneZ, I appreciate your effort, but your approach was making something simple more complicated. Thank you both.
0
 
LVL 42

Expert Comment

by:EugeneZ
Comment Utility
:0 - enjoy my code, time and recommendations that helped you


I just gave you another simple ways to get what you need with attempt to read your mind: if another solution helped you - you should tell so - instead of staritng 2 ways conversation that took my time to help you - and as result - no real appreciation. If you just said - you do not need - I could find what to do with my time instead of writing code for you ..
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
Sorry, didnt see any of the above conversation - you guys have been busy while I was sleeping (gotta love international time zones).

The second update to get rid of the leading space should not be needed any more. It is taken care of in the first update. And you also run the risk of truncating the note message to a max of 1200 (which you might want to still do - up to you).

And probably is best to use @RowsAffected instead of @@rowcount - simply set @rowsaffected = 1 at the begining, and after the update (still in the loop) set @rowsaffected = @@rowcount
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now