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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy