2 tables dependent on the other Part II 1

[Lladros]

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)

 

[bborissov]

Maybe I have mistake in the code.
The first thing I saw is this:

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:

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

 

[Lladros]

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!!!!

 

[bborissov]

Remove DEFAULT(0)

ALTER TABLE lessons ADD oldId int NULL

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP