Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

2 tables dependent on the other Part II 1

Status
Not open for further replies.

Lladros

Programmer
Feb 21, 2008
18
US
I inserted a temp column which consisted of the old ids. It created that and made the new lessons with the new school year. I was able to insert the new assignments with the new years, but when I referred back to the lesson table of the column oldId, it did not update to the new ids. I used your code (Borislav Borissov)

ALTER TABLE Lesson ADD OldId int DEFAULT(0)
GO
UPDATE Lessons Set OldId = Less_Id WHERE schlyr = 2008
INSERT INTO Lessons (lesson_title, lesson_desc, inst, schlyr, OldId)
SELECT (lesson_title, lesson_desc, inst, 2009, OldId)
FROM Lessons WHERE schlyr = 2008

INSERT INTO ASSIGNMENTS
(assign_title, assign_desc, assign_less_id, schlyr)
SELECT assign_title, assign_desc, Lessons.less_id , 2009
FROM ASSIGNMENTS
INNER JOIN Lessons ON ASSIGNMENTS.assign_less_id = Lessons.OldId AND Lessons.schlyr = 2009
WHERE ASSIGNMENTS.schlyr = 2008
ALTER TABLE Lesson DROP COLUMN OldId int DEFAULT(0)

 
Maybe I have mistake in the code.
The first thing I saw is this:
Code:
INSERT INTO Lessons (lesson_title, lesson_desc, inst, schlyr, OldId)
SELECT (lesson_title, lesson_desc, inst, 2009, [b]OldId[/b])
       FROM Lessons WHERE schlyr = 2008
At the last place you should use current Id:
Code:
INSERT INTO Lessons (lesson_title, lesson_desc, inst, schlyr, OldId)
SELECT (lesson_title, lesson_desc, inst, 2009, [b]Less_Id[/b])
       FROM Lessons WHERE schlyr = 2008

As I said that code is not tested at all :)

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP
 
This is what I have now that works:

ALTER TABLE lessons ADD oldId int DEFAULT(0)
GO
UPDATE lessons SET oldId = lesson_Id WHERE lesson_schlyr = '2008'
INSERT INTO lessons (lesson_title, lesson_desc, lesson_inst, lesson_schlyr, oldId)
SELECT lesson_title, lesson_desc, lesson_inst, '2009', oldId FROM lessons
WHERE lesson_schlyr = '2008'
INSERT INTO assignments (assign_title, assign_text, assign_less_id, assign_schlyr)
SELECT assign_title, assign_text, assignments.assign_less_id , '2009' FROM assignments
WHERE assignments.assign_schlyr = '2008'
UPDATE assignments SET assignments.assign_less_id = lessons.lesson_id
FROM assignments INNER JOIN lessons ON assignments.assign_less_id = lessons.oldId
WHERE assignments.assign_schlyr ='2009' AND lessons.lesson_schlyr = '2009'
ALTER TABLE module DROP COLUMN oldId

Now my only problem is this error:

Server: Msg 5074, Level 16, State 1, Line 12
The object 'DF__lessons__oldId__76969D2E' is dependent on column 'oldId'.
Server: Msg 4922, Level 16, State 1, Line 12
ALTER TABLE DROP COLUMN oldId failed because one or more objects access this column.

I tried to add the line: ALTER TABLE lessons NOCHECK CONSTRAINT ALL before the updates, but that doesn't seem to work.

ALMOST THERE!!!!
 
Remove DEFAULT(0)
Code:
ALTER TABLE lessons ADD oldId int NULL

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top