September 28, 2007 at 6:17 am
Hi guys, name is Leah and new to SQL. I am trying to INSERT INTO a table where the name of the customer is in another table. I have a candidateTenant table which progresses into the waitList table which then goes into the Tenant table, so basically I am trying to do this(PK are auto numbers so i dont need to include them in the select list)
candidtaeTenant:WaitList:Tenant
1:M 1:M
INSERT INTO tenant (field1, field2,foeld3 ect)
SELECT waitList_no,first_name,last name,
My question is I already have the name in the waitList table and do not want to repeat entereing the name, can I join them as usual with the INSERT INTO statement, any examples would be great.
The second thing is when I add a tenant then they should no longer be on the waitList or a CandidateTenant, so after I do the INSERT INTO statement how do I then delete them from the waitList and candidateTenant tables, just cant seem to get the syntax correct. I think is a cascade delete but I am very new so really not sure. Any advise would be very much appreciated.
September 28, 2007 at 6:57 am
Need to to know key relationships between tables, please post table DDL, sample data and expected results.
When inserting into a table you can select new rows by excluding any already present depending on what the relationship is, for example if waitList_no is to be used to determine the presence of the row then
INSERT INTO Tenant (field1, field2,field3)
SELECT w.waitList_no,w.first_name,w.last_name
FROM Waitlist w
WHERE NOT EXISTS(SELECT * FROM Tenant t WHERE t.waitList_no = w.waitList_no)
Far away is close at hand in the images of elsewhere.
Anon.
September 28, 2007 at 7:07 am
/*
I'm not sure I really understood what you were trying to do but let me take a guess and see if
I can point you in the right direction.
*/
--Lets assume you have table similiar to the following
if object_id('Tenant') is not null
drop table Tenant;
if object_id('WaitListTenant') is not null
drop table WaitListTenant;
if object_id('CandidateTenant') is not null
drop table CandidateTenant;
go
create Table CandidateTenant (
idint identity(1,1),
first_namechar(15),
last_name char(15),
constraint candidateTenant_pk primary key (id)
);
go
create table WaitListTenant (
id int identity (1,1),
candidateTenant_idint not null,
first_namechar(15),
last_name char(15),
constraint WaitListTenant_pk primary key (id),
constraint WaitListTenant_fk_01 foreign key (candidateTenant_id) references CandidateTenant (id) on delete cascade
);
go
create table Tenant (
id int identity (1,1),
first_namechar(15),
last_name char(15),
constraint Tenant_pk primary key (id)
);
go
--Create some Candidate data
insert into CandidateTenant (first_name,last_name)
select 'First01','Last01' union all
select 'First02','Last02' union all
select 'First03','Last03' union all
select 'First04','Last04' union all
select 'First05','Last05' union all
select 'First06','Last06' union all
select 'First07','Last07' union all
select 'First08','Last08' union all
select 'First09','Last09' union all
select 'First10','Last10'
go
--copy first five to WaitListTeneant
insert into WaitListTenant (candidateTenant_id, first_name,last_name)
select id, first_name, last_name
from candidateTenant
where last_name < 'Last06'
go
--Now move one of WaitListTenant entries to the Tenant table and delete from WaitListTenant and CandidateTenant tables
--Since this is really a two part operation we will wrap everything in a transaction
begin transaction
insert into Tenant (first_name,last_name)
select first_name, last_name
from WaitListTenant
where last_name = 'Last02'
--The cascading delete takes care of both tables.
delete from candidateTenant
where last_name = 'Last02'
--The commit occurs only if no errors encountered with both the insert/delete
commit
select * from candidateTenant;
select * from waitListTenant;
select * from Tenant;
September 29, 2007 at 4:57 am
I think I must be misunderstanding my querie, just cant seem to get it tow work
BEGIN TRANSACTION
USE KWEA;
INSERT INTO waitList(candidate_no,exp_start_date,property_type,no_of_rooms,max_rent,no_of_people)
SELECT 1,1-10-2007,'house',3,$300,4
UNION ALL SELECT 2,4-10-2007,'house',4,$350,4
UNION ALL SELECT 3,20-10-2007,'town house',2,$350,2
UNION ALL SELECT 4,5-11-2007,'villa',3,$300,3
UNION ALL SELECT 5,14-10-2007,'house',4,$350,5
DELETE FROM candidateTenant
WHERE waitList.candidate_no in candidateTenant.candidate_no
COMMIT
once someone is entered into the waitList they must be deleted from the candidateTenant table, the waitList table as the candidate_no as foreign key, candidate_nio is promary key in candidateTenantTable (thanks in advance)
October 1, 2007 at 2:23 am
DELETE t
FROM candidateTenant t
WHERE EXISTS(SELECT * FROM waitList w WHERE w.candidate_no = t.candidate_no)
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply