February 10, 2009 at 3:23 am
Setting: 2 tables, tblOld and tblNew. Neither table contains a primary key.
I want to obsolete those records in tblOld that are not in tblNew. Two records
are equal if all fields match. I'm trying to use the t-sql Except operator but
have not been successful. Any ideas apreciated.
CREATE TABLE [dbo].[tblOld](
[c1] [nchar](10) NULL,
[c2] [nchar](10) NULL,
[obsolete] [bit] NULL
)
CREATE TABLE [dbo].[tblNew](
[c1] [nchar](10) NULL,
[c2] [nchar](10) NULL
)
-- truncate table tblold
insert into tblOld values (1,2,0)
insert into tblOld values (2,3,0)
insert into tblOld values (2,5,0)
-- truncate table tblNew
insert into tblNew values (1,2)
insert into tblNew values (3,5)
insert into tblNew values (7,8)
insert into tblNew values (7,9)
select * from tblOld
select * from tblNew
update tblOld
set obsolete = 1
where
(
select c1, c2 from tblOld
except
select c1, c2 from tblNew
)
Expected output:
tblOld
c1c2obsolete
1 2 0
2 3 1
2 5 1
TIA,
Barkingdog
February 10, 2009 at 4:28 am
update tblOld
set obsolete = 1
where not exists (
select 1
from tblNew b
where tblOld.c1 = b.c1 and tblOld.c2 = b.c2
)
February 10, 2009 at 12:23 pm
[font="Verdana"]I do this sort of thing with left outer joins. I believe SQL Server is pretty much smart enough to generate a nearly identical query plan for the two though, so no biggie. Just that I have gotten used to writing it as left outer join.
Here's the same code expressed in that way:
update o
set o.obsolete = 1
from tblOld o
left join
tblNew n
on o.c1 = n.c1 and
o.c2 = n.c2
where n.c1 is null and
n.c2 is null;
[/font]
February 10, 2009 at 1:37 pm
update tblOld
set obsolete = 1
where c1+c2 IN
(
select c1+c2 from tblOld
except
select c1+c2 from tblNew
)
February 10, 2009 at 1:42 pm
pparlapalli (2/10/2009)
update tblOldset obsolete = 1
where c1+c2 IN
(
select c1+c2 from tblOld
except
select c1+c2 from tblNew
)
[font="Verdana"]The problem with this approach is:
If I have two rows in tblOld, with the values (0, 1) and (1, 0). I have one row in tblNew with the values (0, 1). If I use the approach of adding the two rows, then (0, 1) and (1, 0) are functionally equivalent, and I won't end up setting the obsolete flag for (1, 0) as I would expect.
[/font]
July 8, 2015 at 9:20 am
cast(c1 as varchar(10))+cast(c2 as varchar(10))
With this 10 will be diferent then 01
July 8, 2015 at 11:30 am
If the exercise is to find a way to use "except" then you can try:
update t
set obsolete = 1
from tblOld t
left join (
select c1, c2 from tblOld
except
select c1, c2 from tblNew
) X
on X.c1 = t.c1
and X.c2 = t.c2
where x.c1 is null
Otherwise go with Bruce's suggestion.
Don Simpson
July 8, 2015 at 12:01 pm
I'd use a suggestion very similar to Leo's code. I'm not sure if that's the update format that can cause unexpected performance problems with no logical reason.
UPDATE o
SET obsolete = 1
FROM tblOld o
WHERE NOT EXISTS(
SELECT 1
FROM tblNew n
WHERE o.c1 = n.c1
AND o.c2 = n.c2);
July 8, 2015 at 12:16 pm
Or something like this:
with Obsolete as (
select
c1,
c2
from
dbo.tblOld
except
select
c1,
c2
from
dbo.tblNew
)
update t set
obsolete = 1
from
dbo.tblOld t
where
exists(select 1 from Obsolete o where o.c1 = t.c1 and o.c2 = t.c2);
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply