January 4, 2007 at 8:41 am
Hi,
Sorry this is a bit long:
I have two tables, both have exactly the same structure. One will contain old data (say table a) and is used by an app, the other will be refreshed with new data each day from another system (table b) and is only used to update table a.
Of the two tables there will be some rows in table a that no longer appear in table b, I need to be able to delete these rows, additionally I then have to insert rows into table a that do not appear in table a but are in table b. The intersect of the two tables must remain untouched throughout (this table a holds security definitions for an app. in use 24hrs a day).
I've been looking for solutions to this all morning, as I'm using SQL 2000 I don't think I can use except/intersect or common table expressions sadly. Looking at the help files for 2000, for the delete statement syntax I see it is possible to use a join, I think that this could let me do what I need (using outer joins), but I cannot get it to work.
The examples below show what I'm trying to achieve, note that @a has one row that is not common to @b-2 and @b-2 has two rows that are not common to @a:
declare @a table --historic table
(
col1 varchar(10),
col2 varchar(10),
col3 varchar(10)
)
declare @b-2 table --up to date table
(
col1 varchar(10),
col2 varchar(10),
col3 varchar(10)
)
--Populate @a
insert into @a values ('a','a','a')
insert into @a values ('b','b','b')
insert into @a values ('z','z','a')--not in @b-2
--Populate @b-2
insert into @b-2 values ('a','a','a')
insert into @b-2 values ('b','b','b')
insert into @b-2 values ('a','a','z')
insert into @b-2 values ('b','b','z')--not in @a
insert into @b-2 values ('c','c','z')--not in @a
In order to delete just the rows that do not exist in @b-2 from @a I'm trying the following:
delete
from @a
from @a as a left outer join @b-2 as b
on a.col1 = b.col1
and a.col2 = b.col2
and a.col3 = b.col3
I'm obviously missing something simple but sadly the above just deletes everything. Can anyone tell me where I'm going wrong, or perhaps suggest a better way to delete/insert rows that do not overlap?
Any help woud be much appreciated.
Cheers,
Iain
January 4, 2007 at 8:56 am
all that is missing is your WHERE statement after the join, so the SQL knows what to exclude/include in the delete:
delete
from @a
left outer join @b-2 as b
on a.col1 = b.col1
and a.col2 = b.col2
and a.col3 = b.col3
WHERE b.col3 is null
select * from @a Results:
| col1 | col2 | col3 |
| a | a | a |
| b | b | b |
Lowell
January 4, 2007 at 9:00 am
Always the simple things!
Thank you very much. Out of interest is there another way to achieve this with 2000 (I'm not asking for a full example, just a hint)?
Cheers,
Iain
January 4, 2007 at 9:10 am
You could use a subquery.
Delete from a
where a.pk not in (select b.pk from b where a.xx = b.xx)
Something like that. You'd have to work out the joins you are trying to achieve.
January 4, 2007 at 9:35 am
Sweet, wouldn't have though of that (again always overlooking the simple things).
For anyone reading this with the same problem the solution using the above suggestion is:
delete from @a
where col1 not in
(select a.col1
from @a as a INNER join @b-2 as b
on a.col1 = b.col1
and a.col2 = b.col2
and a.col3 = b.col3
)
Thanks again.
January 5, 2007 at 3:23 am
The third variant of expressing the same thing as the shown LEFT JOIN and NOT IN is to use a NOT EXISTS construct.
/Kenneth
January 5, 2007 at 3:40 am
You must use alias:
delete A
from @a A
left outer join @b-2 as b
on A.col1 = b.col1
and A.col2 = b.col2
and A.col3 = b.col3
WHERE b.col1 IS NULL
_____________
Code for TallyGenerator
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply