Hi, i am trying to change the appointment time for a given appointment however it keeps pulling up errors such as
'time must be set to on or off' and 'unknown command where time...'
this is my appointments table and insert statements;
create table Appointments
(Appointment_No number(2) not null,
Time_Date Date,
Attending_Surgeon VarChar(25) not null,
Cost Number(2),
Pet_ID Char(3) not null,
Staff_ID Char(5) not null,
CONSTRAINT Appointment_pk PRIMARY KEY (Appointment_No),
FOREIGN KEY (Pet_ID) references Pets (Pet_ID),
FOREIGN KEY (Staff_ID) references Staff (Staff_ID));
alter session set nls_date_format = 'hh24:mi dd-mm-yyyy';
insert into Appointments values ('1' , to_date('09:00 14-01-2013') , 'Dr Lehart' , '10' , 'P02', 'LEH01');
insert into Appointments values ('2' , to_date('09:45 14-01-2013') , 'Dr Shivago' , '10' , 'P04' , 'SHI02');
insert into Appointments values ('3' , to_date('15:00 14-01-2013') , 'Dr Strevens' , '10' , 'P06' , 'STR06');
insert into Appointments values ('4' , to_date('11:30 15-01-2013') , 'Dr Mitchell' , '10' , 'P08' , 'MIT08');
insert into Appointments values ('5' , to_date('16:30 15-01-2013') , 'Nurse West' , '10' , 'P10' , 'WES12');
insert into Appointments values ('6' , to_date('10:15 16-01-2013') , 'Dr Bean' , '10' , 'P01' , 'BEA03');
insert into Appointments values ('7' , to_date('12:30 16-01-2013') , 'Dr Jones' , '10' , 'P03' , 'JON05');
insert into Appointments values ('8' , to_date('14:45 16-01-2013') , 'Dr Thomas' , '10' , 'P05' , 'THO04');
insert into Appointments values ('9' , to_date('09:45 17-01-2013') , 'Dr Platt' , '10' , 'P07' , 'PLA07');
insert into Appointments values ('10' ,to_date('15:45 17-01-2013') , 'Nurse Cotton' , '10' , 'P09' , 'COT10');
Now this is my update statement that im trying to achieve to change appointment time for given appointment;
Set Time_Date = to_date(’16:00 19-01-2013’)
Where Time_Date = to_date(’15:45 17-01-2013);
Help :)
Hi Friend,
This is all Oracle pl\sql syntax, you are wrong place. Eventhough it is pl\sql, by seeing above syntax I will suggest you need to work much on basic SQL.
I will point out few silly mistakes here like
create table Appointments
(Appointment_No
number(2) not null,
Time_Date Date,
Attending_Surgeon VarChar(25) not null,
Cost
Number(2),
Pet_ID Char(3) not null,
Staff_ID Char(5) not null,
CONSTRAINT Appointment_pk PRIMARY KEY (Appointment_No),
FOREIGN KEY (Pet_ID) references Pets (Pet_ID),
FOREIGN KEY (Staff_ID) references Staff (Staff_ID));
alter session set nls_date_format = 'hh24:mi dd-mm-yyyy';
insert into Appointments values (
'1' , to_date('09:00 14-01-2013') , 'Dr Lehart' ,
'10' , 'P02', 'LEH01');
insert into Appointments values (
'2' , to_date('09:45 14-01-2013') , 'Dr Shivago' ,
'10' , 'P04' , 'SHI02');
insert into Appointments values (
'3' , to_date('15:00 14-01-2013') , 'Dr Strevens' ,
'10' , 'P06' , 'STR06');
insert into Appointments values (
'4' , to_date('11:30 15-01-2013') , 'Dr Mitchell' ,
'10' , 'P08' , 'MIT08');
insert into Appointments values ('
5' , to_date('16:30 15-01-2013') , 'Nurse West' ,
'10' , 'P10' , 'WES12');
insert into Appointments values ('
6' , to_date('10:15 16-01-2013') , 'Dr Bean' ,
'10' , 'P01' , 'BEA03');
insert into Appointments values ('
7' , to_date('12:30 16-01-2013') , 'Dr Jones' ,
'10' , 'P03' , 'JON05');
insert into Appointments values ('
8' , to_date('14:45 16-01-2013') , 'Dr Thomas' ,
'10' , 'P05' , 'THO04');
insert into Appointments values ('
9' , to_date('09:45 17-01-2013') , 'Dr Platt' ,
'10' , 'P07' , 'PLA07');
insert into Appointments values ('
10' ,to_date('15:45 17-01-2013') , 'Nurse Cotton' , '
10' , 'P09' , 'COT10');
Set Time_Date = to_date('16:00 19-01-2013')
Where Time_Date = to_date(
'15:45 17-01-2013); <---
missing single quote here
You have defined number datatype for column Appointment_No and Cost, but inserting your values as
varchar...why ??
Please Mark as Answer if my post solved your problem or Vote As Helpful if this helps. Blogs: www.sqlserver2005forum.blogspot.com
