insert #T2 values ('aaa', null)
insert #T2 values ('aaa', '222')
GO
print '=3D=3D> INTERSECT'
select col1, col2 from #T1
INTERSECT select col1, col2 from #T2
GO
print '=3D=3D> delete et INTERSECT'
SET NOCOUNT OFF
/*
delete from #T1
where exists (select null from #T2 where #T1.col1 =3D #T2.col1 and
#T1.col2 =3D #T2.col2)
*/
delete from #T1
where exists (
select col1, col2 from #T1
INTERSECT select col1, col2 from #T2
)
SET NOCOUNT ON
select * from #T1
print replicate('=95', 50)
GO
drop table #T1
drop table #T2
--------------------------------------
Le intersect retourne bien les deux lignes que je voudrais supprimer.
Comment le faire comprendre au delete ?
Dans l'=E9tat, la table est vid=E9e, ce qui n'est pas le but :)
La partie comment=E9e fonctionnerait bien, n'=E9taient les probl=E8mes de
NULL.
Quelqu'un peut-il m'aider ?
insert #T2 values ('aaa', null) insert #T2 values ('aaa', '222') GO print '==> INTERSECT' select col1, col2 from #T1 INTERSECT
INTERSECT select col1, col2 from #T2 GO print '==> delete et INTERSECT' SET NOCOUNT OFF /* delete from #T1 where exists (select null from #T2 where #T1.col1 = #T2.col1 an d #T1.col2 = #T2.col2) */ delete from #T1 where exists ( select col1, col2 from #T1 INTERSECT select col1, col2 from #T2 ) SET NOCOUNT ON select * from #T1 print replicate('', 50) GO drop table #T1 drop table #T2 -------------------------------------- Le intersect retourne bien les deux lignes que je voudrais supprimer. Comment le faire comprendre au delete ? Dans l'état, la table est vidée, ce qui n'est pas le but :) La partie commentée fonctionnerait bien, n'étaient les problèmes de NULL. Quelqu'un peut-il m'aider ?
Le valeur null faut éviter de travailler avec dans les jointures ....... J'ai fais qlq modif a ton pour qu'il fonctionne dans ce cas
set nocount on create table #T1 (col1 varchar(10) null, col2 varchar(10) null) create table #T2 (col1 varchar(10) null, col2 varchar(10) null) insert #T1 values ('aaa', null) insert #T1 values ('aaa', '222') insert #T1 values ('bbb', '333') insert #T2 values ('aaa', null) insert #T2 values ('aaa', '222') GO print '==> INTERSECT' select col1, col2 from #T1 INTERSECT select col1, col2 from #T2 GO print '==> delete et INTERSECT' SET NOCOUNT OFF ; /* delete from #T1 where exists (select null from #T2 where #T1.col1 = #T2.col1 and #T1.col2 = #T2.col2) */ with inter as ( select col1, col2 from #T1 INTERSECT select col1, col2 from #T2 ) delete from #T1 from #T1 as T1 inner join inter I on isnull(T1.col1,'null') = isnull(I.col1,'null') and isnull(T1.col2,'null') =isnull(I.col2,'null') ; -- on T1.col1= I.col1 and T1.col2 =I.col2;
select * from #T1
print replicate('', 50) GO drop table #T1 drop table #T2
On 9 juin, 15:31, HRX <hreign...@sidexa.com> wrote:
Bonjour à tous,
Comment mixer les deux notions en objet (SQL Server 2005) ?
Considérons le code suivant :
--------------------------------------
set nocount on
create table #T1 (col1 varchar(10) null, col2 varchar(10) null)
create table #T2 (col1 varchar(10) null, col2 varchar(10) null)
insert #T2 values ('aaa', null)
insert #T2 values ('aaa', '222')
GO
print '==> INTERSECT'
select col1, col2 from #T1
INTERSECT
INTERSECT select col1, col2 from #T2
GO
print '==> delete et INTERSECT'
SET NOCOUNT OFF
/*
delete from #T1
where exists (select null from #T2 where #T1.col1 = #T2.col1 an d
#T1.col2 = #T2.col2)
*/
delete from #T1
where exists (
select col1, col2 from #T1
INTERSECT select col1, col2 from #T2
)
SET NOCOUNT ON
select * from #T1
print replicate('', 50)
GO
drop table #T1
drop table #T2
--------------------------------------
Le intersect retourne bien les deux lignes que je voudrais supprimer.
Comment le faire comprendre au delete ?
Dans l'état, la table est vidée, ce qui n'est pas le but :)
La partie commentée fonctionnerait bien, n'étaient les problèmes de
NULL.
Quelqu'un peut-il m'aider ?
Le valeur null faut éviter de travailler avec dans les
jointures .......
J'ai fais qlq modif a ton pour qu'il fonctionne dans ce cas
set nocount on
create table #T1 (col1 varchar(10) null, col2 varchar(10) null)
create table #T2 (col1 varchar(10) null, col2 varchar(10) null)
insert #T1 values ('aaa', null)
insert #T1 values ('aaa', '222')
insert #T1 values ('bbb', '333')
insert #T2 values ('aaa', null)
insert #T2 values ('aaa', '222')
GO
print '==> INTERSECT'
select col1, col2 from #T1
INTERSECT select col1, col2 from #T2
GO
print '==> delete et INTERSECT'
SET NOCOUNT OFF ;
/*
delete from #T1
where exists (select null from #T2 where #T1.col1 = #T2.col1 and
#T1.col2 = #T2.col2)
*/
with
inter as ( select col1, col2 from #T1
INTERSECT select col1, col2 from #T2 )
delete from #T1
from #T1 as T1 inner join inter I
on isnull(T1.col1,'null') = isnull(I.col1,'null') and
isnull(T1.col2,'null') =isnull(I.col2,'null') ;
-- on T1.col1= I.col1 and T1.col2 =I.col2;
select * from #T1
print replicate('', 50)
GO
drop table #T1
drop table #T2
insert #T2 values ('aaa', null) insert #T2 values ('aaa', '222') GO print '==> INTERSECT' select col1, col2 from #T1 INTERSECT
INTERSECT select col1, col2 from #T2 GO print '==> delete et INTERSECT' SET NOCOUNT OFF /* delete from #T1 where exists (select null from #T2 where #T1.col1 = #T2.col1 an d #T1.col2 = #T2.col2) */ delete from #T1 where exists ( select col1, col2 from #T1 INTERSECT select col1, col2 from #T2 ) SET NOCOUNT ON select * from #T1 print replicate('', 50) GO drop table #T1 drop table #T2 -------------------------------------- Le intersect retourne bien les deux lignes que je voudrais supprimer. Comment le faire comprendre au delete ? Dans l'état, la table est vidée, ce qui n'est pas le but :) La partie commentée fonctionnerait bien, n'étaient les problèmes de NULL. Quelqu'un peut-il m'aider ?
Le valeur null faut éviter de travailler avec dans les jointures ....... J'ai fais qlq modif a ton pour qu'il fonctionne dans ce cas
set nocount on create table #T1 (col1 varchar(10) null, col2 varchar(10) null) create table #T2 (col1 varchar(10) null, col2 varchar(10) null) insert #T1 values ('aaa', null) insert #T1 values ('aaa', '222') insert #T1 values ('bbb', '333') insert #T2 values ('aaa', null) insert #T2 values ('aaa', '222') GO print '==> INTERSECT' select col1, col2 from #T1 INTERSECT select col1, col2 from #T2 GO print '==> delete et INTERSECT' SET NOCOUNT OFF ; /* delete from #T1 where exists (select null from #T2 where #T1.col1 = #T2.col1 and #T1.col2 = #T2.col2) */ with inter as ( select col1, col2 from #T1 INTERSECT select col1, col2 from #T2 ) delete from #T1 from #T1 as T1 inner join inter I on isnull(T1.col1,'null') = isnull(I.col1,'null') and isnull(T1.col2,'null') =isnull(I.col2,'null') ; -- on T1.col1= I.col1 and T1.col2 =I.col2;
select * from #T1
print replicate('', 50) GO drop table #T1 drop table #T2
