Update function
-
Hi,
I have 2 tables (class & schedule), these 2 tables have a column that is identical.
class.classID
schedule.classID
the class.classID has been populated with several values, I want to update the schedule.classID column so that it has exactly the same values as the class.classID column. I've tried this code:
Update schedule
set schedule.classID = class.classID
from class join schedule
on class.classID = schedule.classID
but I get a ' 0 rows affected' and no change takes place.
Can any1 tell me where Im going wrong or suggest an alternative code.
Much appreciated
-
Is the schedule table currently empty and you just want to populate it with all current classid's from class?
If so then
insert into schedule (classid)
select classid from class
would do it
your query above is looking for matching rows in the schedule table so if there is nothing in it then it won't find anything.
HTH
Growing old is mandatory, growing up is optional -
Mark - what is the DDL of your class and schedule tables ?! Is there another column you can link ?!
As bond007 says - you're joining on classID - the same column you want to update - even if you find matching rows it won't help because what you need is to update with IDs that are present in class but NOT in schedule...
**ASCII stupid question, get a stupid ANSI !!!** -
Thanks for you input.
The schedule table consists of columns that are identical to columns from several other tables.
trainers.trainerID..........schedule.trainerID
students.studentID.......schedule.studentID
class.classID................schedule.classid
branches.branchID........schedule.branchID
course.courseID...........schedule.courseID
Since I have entered values into the columns of the tables (trainers,students etc)
I want some way of those values being automaitcially inserted into the schedule table.
The schedule table has 2 columns that I want to add data manually, they are 'start' and 'end', which are columns
that hold starting and ending dates. The schedule table also has a primary key column (schedID) which also
the identifier
I tried your suggestion, however the insert could not be completed because the schedId column
will not allow nulls, but it shouldnt generate a null, it should generate sql genetated identity number,
that is unique for each row.
Here the ddl's u asked for:
Schedule:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[schedule]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[schedule]
GO
CREATE TABLE [dbo].[schedule] (
[schedID] [int] NOT NULL ,
[classID] [int] NOT NULL ,
[courseID] [int] NOT NULL ,
[branchID] [int] NOT NULL ,
[studentID] [int] NOT NULL ,
[trainerID] [int] NOT NULL ,
[start] [smalldatetime] NULL ,
[end] [smalldatetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[schedule] ADD
CONSTRAINT [FK_schedule_branches] FOREIGN KEY
(
[branchID]
 
REFERENCES [dbo].[branches] ([branchID]
 
,CONSTRAINT [FK_schedule_class] FOREIGN KEY
(
[classID]
 
REFERENCES [dbo].[class] ([classID]
 
,CONSTRAINT [FK_schedule_course] FOREIGN KEY
(
[courseID]
 
REFERENCES [dbo].[course] ([courseID]
 
,CONSTRAINT [FK_schedule_Students] FOREIGN KEY
(
[studentID]
 
REFERENCES [dbo].[Students] ([studentID]
 
,CONSTRAINT [FK_schedule_trainers] FOREIGN KEY
(
[trainerID]
 
REFERENCES [dbo].[trainers] ([trainerID]
 
GO
Class:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_schedule_class]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[schedule] DROP CONSTRAINT FK_schedule_class
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[class]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[class]
GO
CREATE TABLE [dbo].[class] (
[classID] [int] IDENTITY (1, 1) NOT NULL ,
[classNo] [int] NULL ,
[branchID] [int] NOT NULL ,
[capacity] [tinyint] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[class] WITH NOCHECK ADD
CONSTRAINT [PK_class] PRIMARY KEY CLUSTERED
(
[classID]
 
ON [PRIMARY]GO
ALTER TABLE [dbo].[class] WITH NOCHECK ADD
CONSTRAINT [CK__class__capacity__6D0D32F4] CHECK ([capacity] > 0)
GO
CREATE UNIQUE INDEX [class_UNCI_classID] ON [dbo].[class]([classID]) ON [PRIMARY]
GO
CREATE UNIQUE INDEX [class_UNCI_branchID] ON [dbo].[class]([branchID]) ON [PRIMARY]
GO
-
Looking at the DDL for schedule the scheduleid column isn't defined with Identity so it won't generate a new number.
I can see the relationship between class and branch, i assume one branch can have many classes but only one class is related to a branch. It would be handy to know how the other tables are exactly related/linked. You ideally need to write an insert query that joins each table so you get a result something along the lines of classid, courseid, branchid, studentid, trainerid. You need to insert each of these at the same time as each column in your schedule table is defined as NOT NULL. Without seeing all the DDL its hard to give you a full answer, for me anyway, others might have a better idea!
Growing old is mandatory, growing up is optional -
What about the branchID in the 2 tables ?! You could use that in your join instead if these can be linked!
**ASCII stupid question, get a stupid ANSI !!!** -
Thanks..I manages to get it working by using the insert into statements all at once, that eliminated all the nulls. I got another problem though. You guys will probably find it simple to solve, but i've onle been doing sql for 3 weeks, and sometimes i find it pretty hard.
I have a table called schedule that is made up of several columns. The columns of concern are start date and end_date. Say the start date is today (19th july 2005), is there a function i could write that would add a certain amount of weeks to start date. basically a function to determin the end_date where the parameters are the start date and the number of weeks, when executed the function would the end_date as the result of the function.
CREATE PROC start_end
@start smalldatetime, @weeks
As
BEGIN
end_date = @start + @weeks (where weeks is a number entered by the user)
I think the function would be something like that
-
Mark - please take a look at the DATEADD SQL date function on BOL - it's tailormade for your requirement....
You would do something like : "SELECT DATEADD(wk, 6, getdate()) AS end_date"...
**ASCII stupid question, get a stupid ANSI !!!**
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply