How to ensure column uniqueness

Hi,
Coming from an Oracle background, I'm used to being able
to create a unique key on a column that allows many null
values, ie if a value exists it must be unique, otherwise
it can be null.
It appears as though SQL Server allows only 1 null value
in the same situation, ie create a unique constraint on a
column and once you try to insert a 2nd row with a null
value I get a constraint violation. [Thx to those that
replied to my last question on this]
I don't really want to write triggers testing for such
conditions. Is there some form of constraint that can do
this for me?
TIA,
SJTYour observations are correct. You can create a view conatining all rows but the NULL. Then create a
unique index (or possible a unique constraint) on that index.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"SJT" <scott.taylor@.pwcs.com.au> wrote in message news:054601c36ad9$46dd1f90$a501280a@.phx.gbl...
> Hi,
> Coming from an Oracle background, I'm used to being able
> to create a unique key on a column that allows many null
> values, ie if a value exists it must be unique, otherwise
> it can be null.
> It appears as though SQL Server allows only 1 null value
> in the same situation, ie create a unique constraint on a
> column and once you try to insert a 2nd row with a null
> value I get a constraint violation. [Thx to those that
> replied to my last question on this]
> I don't really want to write triggers testing for such
> conditions. Is there some form of constraint that can do
> this for me?
>
> TIA,
> SJT|||You can use an indexed view to enforce uniqueness only for non-NULL values:
CREATE TABLE Sometable (keycol INTEGER PRIMARY KEY, colx INTEGER NULL)
GO
CREATE VIEW Sometable_Unique_Non_NULL
WITH SCHEMABINDING
AS SELECT colx FROM dbo.Sometable WHERE colx IS NOT NULL
GO
CREATE UNIQUE CLUSTERED INDEX uclcolx ON Sometable_Unique_Non_NULL (colx)
INSERT INTO Sometable VALUES (1,1)
INSERT INTO Sometable VALUES (2,NULL)
INSERT INTO Sometable VALUES (3,NULL)
--
David Portas
--
Please reply only to the newsgroup
--

How to enfore a primary key range ??

I've inherited the following situation...
The table contains 4 columns... script below
Note that the first 3 columns denote the primary Key...
Actually what is really meant is the following...
Let's say the values for one row are as follows...
Code= A
LowVal = 25
HighVal=50
UseThis=Fred
What they want to be implied by this row... if Code=A and the test val is
between 25 and 50 UseThis= Fred
They want to disallow any row that overlaps from being added... such as the
following...
Code= A
LowVal = 30
HighVal=40
UseThis=Joe
How can you enforce something like this ?
CREATE TABLE [dbo].[Table1] (
[Code] [char] (10) COLLATE Latin1_General_BIN NOT NULL ,
[LowVal] [decimal](6, 0) NOT NULL ,
[HighVal] [decimal](6, 0) NOT NULL ,
[UseThis] [char] (10) COLLATE Latin1_General_BIN NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[Code],
[LowVal],
[HighVal]
) ON [PRIMARY]"Rob" <rwchome@.comcast.net> wrote in message
news:l4WdnX_h78I-eyzeRVn-vA@.comcast.com...
> I've inherited the following situation...
> The table contains 4 columns... script below
> Note that the first 3 columns denote the primary Key...
> Actually what is really meant is the following...
> Let's say the values for one row are as follows...
> Code= A
> LowVal = 25
> HighVal=50
> UseThis=Fred
> What they want to be implied by this row... if Code=A and the test val is
> between 25 and 50 UseThis= Fred
> They want to disallow any row that overlaps from being added... such as
> the following...
> Code= A
> LowVal = 30
> HighVal=40
> UseThis=Joe
> How can you enforce something like this ?
>
> CREATE TABLE [dbo].[Table1] (
> [Code] [char] (10) COLLATE Latin1_General_BIN NOT NULL ,
> [LowVal] [decimal](6, 0) NOT NULL ,
> [HighVal] [decimal](6, 0) NOT NULL ,
> [UseThis] [char] (10) COLLATE Latin1_General_BIN NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Table1] ADD
> CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
> (
> [Code],
> [LowVal],
> [HighVal]
> ) ON [PRIMARY]
>
You'll have to use a trigger for this, eg:
create trigger Table1_no_overlap
on Table1 for insert, update
as
begin
if exists
(
select *
from Table1 l
join Table1 r
on l.LowVal < r.LowVal
and l.HighVal > r.LowVal
)
begin
raiserror('Change would create overlapping range.',16,1)
rollback transaction
end
end
David|||Thanks David,
Maybe I am doing something wrong, but I was able to add the following rows
after applying the trigger...
insert into Table1 Values('A',20,100,'Joe')
insert into Table1 Values('A',20,500,'FRED')
Rob
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:eDxVWD1CGHA.344@.TK2MSFTNGP11.phx.gbl...
> "Rob" <rwchome@.comcast.net> wrote in message
> news:l4WdnX_h78I-eyzeRVn-vA@.comcast.com...
> You'll have to use a trigger for this, eg:
> create trigger Table1_no_overlap
> on Table1 for insert, update
> as
> begin
> if exists
> (
> select *
> from Table1 l
> join Table1 r
> on l.LowVal < r.LowVal
> and l.HighVal > r.LowVal
> )
> begin
> raiserror('Change would create overlapping range.',16,1)
> rollback transaction
> end
> end
>
> David
>|||Hi, Rob
Use the following trigger:
alter trigger Table1_no_overlap
on Table1 for insert, update
as
begin
if exists
(
select *
from Table1 t
join inserted i
on t.LowVal between i.LowVal and i.HighVal
or i.LowVal between t.LowVal and t.HighVal
where i.Code<>t.Code or i.LowVal<>t.LowVal or i.HighVal<>t.HighVal
)
begin
raiserror('Change would create overlapping range.',16,1)
rollback transaction
end
end
If you want to allow overlapping ranges for different codes (but not
for the same code), the trigger would be like this:
alter trigger Table1_no_overlap
on Table1 for insert, update
as
begin
if exists
(
select *
from Table1 t
join inserted i
on t.Code=i.Code and (
t.LowVal between i.LowVal and i.HighVal
or i.LowVal between t.LowVal and t.HighVal
)
where i.LowVal<>t.LowVal or i.HighVal<>t.HighVal
)
begin
raiserror('Change would create overlapping range.',16,1)
rollback transaction
end
end
Razvan|||Rob wrote:

> Thanks David,
> Maybe I am doing something wrong, but I was able to add the following rows
> after applying the trigger...
> insert into Table1 Values('A',20,100,'Joe')
> insert into Table1 Values('A',20,500,'FRED')
> Rob
>
Try it like this. Notice that I've added an extra constraint, modified
the join in the trigger and added CODE to the join. That's my reading
of what you want to achieve. Test carefully.
ALTER TABLE table1 ADD CONSTRAINT ck_table1_lowval_highval
CHECK (lowval <= highval) ;
GO
create trigger Table1_no_overlap
on Table1 for insert, update
as
begin
if exists
(
select *
from Table1 l
join Table1 r
on l.LowVal < r.HighVal
and l.HighVal > r.LowVal
and l.code = r.code
)
begin
raiserror('Change would create overlapping range.',16,1)
rollback transaction
end
end
GO
David Portas
SQL Server MVP
--|||Hi, David
Your trigger doesn't allow any row to be inserted.
Razvan|||Razvan Socol wrote:
> Hi, David
> Your trigger doesn't allow any row to be inserted.
> Razvan
You're right. Here's a correction:
create trigger Table1_no_overlap
on Table1 for insert, update
as
begin
if exists
(
select *
from Table1 l
join Table1 r
on l.LowVal < r.HighVal
and l.HighVal > r.LowVal
and l.code = r.code
and (l.LowVal <> r.LowVal
or l.HighVal <> r.HighVal)
)
begin
raiserror('Change would create overlapping range.',16,1)
rollback transaction
end
end
GO
David Portas
SQL Server MVP
--|||Hi, David
My understanding of the original post is that the following rows are
not allowed (but your trigger allows them):
insert into Table1 Values('A',20,100,'Joe')
insert into Table1 Values('A',100,150,'FRED')
Rob wrote:
> What they want to be implied by this row... if Code=A and the test val is
> between 25 and 50 UseThis= Fred
The following rows would be ok:
insert into Table1 Values('A',20,100,'Joe')
insert into Table1 Values('A',101,150,'FRED')
Razvan|||Thank you both Razvan and David...
Sorry I was not clear on this, actually same HighVal on one row may be equal
to LowVal on another...
The code applied uses > LowVal and <= HighVal...
Rob
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1135755688.241085.105660@.g49g2000cwa.googlegroups.com...
> Hi, David
> My understanding of the original post is that the following rows are
> not allowed (but your trigger allows them):
> insert into Table1 Values('A',20,100,'Joe')
> insert into Table1 Values('A',100,150,'FRED')
> Rob wrote:
> The following rows would be ok:
> insert into Table1 Values('A',20,100,'Joe')
> insert into Table1 Values('A',101,150,'FRED')
> Razvan
>|||You might want to add some other constraints. I would not allow the
low and high values to be the same; disjoint ranges will allow you to
use a more readable BETWEEN predicate.
CREATE TABLE Table1
(foo_code CHAR (10) NOT NULL,
low_val DECIMAL(6,0) NOT NULL,
high_val DECIMAL(6,0) NOT NULL,
use_this CHAR (10) DEFAULT '{{ none }}' NOT NULL,
CHECK (low_val <= high_val)
PRIMARY KEY (foo_code,low_val, high_val),
UNIQUE (foo_code,low_val),
UNIQUE (foo_code, high_val)
);
Besides not having overlaps, you might want to avoid gaps in the
ranges.
CREATE TRIGGER Table1_No_Gaps
ON Table1 FOR INSERT, UPDATE
AS
BEGIN
IF EXISTS
(SELECT *
FROM Table1 AS T1
GROUP BY T1.foo_code
HAVING MAX(high_val)- MIN(low_val) +1
= SUM(high_val - low_val + 1)
BEGIN
RAISERROR ('Code Range Errors',16,1);
ROLLBACK TRANSACTION;
END;
END;sql

How to encrypt MS SQL Server 2000

How could i encrypt a ms sql 2000 database. what is when i do a backup of
the db. will i recieve a certificade with the key.
Does somebody have some experiences about the best way for encrypting an sql
server and the protocol.
Thank's for some infos
MichelMichel wrote:
> How could i encrypt a ms sql 2000 database. what is when i do a backup of
> the db. will i recieve a certificade with the key.
> Does somebody have some experiences about the best way for encrypting an sql
> server and the protocol.
> Thank's for some infos
> Michel
For encrypted backups: http://www.quest.com/litespeed_for_sql_server
Search Books Online for "encryption" to understand how to encrypt the
network protocol.
Encrypting the entire database rarely makes sense. If you need to store
or transmit the database via an insecure medium then use an encrypted
backup.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

How to encrypt MS SQL Server 2000

How could i encrypt a ms sql 2000 database. what is when i do a backup of
the db. will i recieve a certificade with the key.
Does somebody have some experiences about the best way for encrypting an sql
server and the protocol.
Thank's for some infos
MichelMichel wrote:
> How could i encrypt a ms sql 2000 database. what is when i do a backup of
> the db. will i recieve a certificade with the key.
> Does somebody have some experiences about the best way for encrypting an s
ql
> server and the protocol.
> Thank's for some infos
> Michel
For encrypted backups: http://www.quest.com/litespeed_for_sql_server
Search Books Online for "encryption" to understand how to encrypt the
network protocol.
Encrypting the entire database rarely makes sense. If you need to store
or transmit the database via an insecure medium then use an encrypted
backup.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

How to drop Primary key by specifying column only?

I checked column from xsd file which created from DataBase.

And before installing my application I will check any column in destination base whether have complete column or not.

And if there is any column which is not wanted column(not same as xsd structure)

I will delete it by creating sql command as follow "ALTER TABLE tableName DROP COLUMN column1, column2, ....."

and Execute it by program initialization.

So

I need delete it by run-time

However some column may be Primary Key with any reason

That's why I can't delete them by simple command

I expect to delete them by specific column instead of specific constraint name which is not sure name.

Please advise me...

I don't think you can do it by just specifying a column but you can do it this way:

To get the name of the primary key:

SELECT constraint_name
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE (TABLE_NAME = 'TestTable') AND (CONSTRAINT_TYPE = 'PRIMARY KEY')

To drop the primary key:

ALTER TABLE TestTable
DROP CONSTRAINT PK_TestTable

|||

Thanks a lot

:D

How to drop primary in script

Hi, does someone know how to drop primary key using TSQL? We need to put
everything into TSQL script. Somehow, I could not find anything in BOL.
please help.
Thanks, Anna
Look at Alter Table..drop constraint.. in BOL
"Anna Linne" <Anna Linne@.discussions.microsoft.com> wrote in message
news:C330A43E-CE07-428B-98FD-A56EB60B9634@.microsoft.com...
> Hi, does someone know how to drop primary key using TSQL? We need to put
> everything into TSQL script. Somehow, I could not find anything in BOL.
> please help.
> Thanks, Anna
|||HI
ALTER TABLE dbo.Table1
DROP CONSTRAINT PK_Table1
Andras Jakus MCDBA
"Anna Linne" wrote:

> Hi, does someone know how to drop primary key using TSQL? We need to put
> everything into TSQL script. Somehow, I could not find anything in BOL.
> please help.
> Thanks, Anna
|||alter table table1 add constraint pk primary key(column1)
alter table table1 drop constraint pk
"Anna Linne" wrote:

> Hi, does someone know how to drop primary key using TSQL? We need to put
> everything into TSQL script. Somehow, I could not find anything in BOL.
> please help.
> Thanks, Anna

How to drop primary in script

Hi, does someone know how to drop primary key using TSQL? We need to put
everything into TSQL script. Somehow, I could not find anything in BOL.
please help.
Thanks, AnnaLook at Alter Table..drop constraint.. in BOL
"Anna Linne" <Anna Linne@.discussions.microsoft.com> wrote in message
news:C330A43E-CE07-428B-98FD-A56EB60B9634@.microsoft.com...
> Hi, does someone know how to drop primary key using TSQL? We need to put
> everything into TSQL script. Somehow, I could not find anything in BOL.
> please help.
> Thanks, Anna|||HI
ALTER TABLE dbo.Table1
DROP CONSTRAINT PK_Table1
Andras Jakus MCDBA
"Anna Linne" wrote:
> Hi, does someone know how to drop primary key using TSQL? We need to put
> everything into TSQL script. Somehow, I could not find anything in BOL.
> please help.
> Thanks, Anna|||alter table table1 add constraint pk primary key(column1)
alter table table1 drop constraint pk
"Anna Linne" wrote:
> Hi, does someone know how to drop primary key using TSQL? We need to put
> everything into TSQL script. Somehow, I could not find anything in BOL.
> please help.
> Thanks, Anna

How to drop all primary key of all tables in DB?

How to drop all primary key of all tables in DB?
After reading a document for explaning how to use clustered index, I knew
set all primary key to be clustered is not correct for me.
I would like to drop them. recreate a new nonclustered primary key and
create other clustered indexes.
Now I would like to know how to drop all primary key of all tables in DB by
T-SQL, because there are too many tables to drop primary key by hand.
Thanks
--UsingSQL2005DevFrank
declare @.pklist table
(
ident int identity(1, 1),
pkname sysname,
tablename sysname
)
insert into
@.pklist
(
pkname,
tablename
)
select
constraint_name,
table_name
from
information_schema.table_constraints
where
constraint_type = 'primary key'
set @.counter = @.@.rowcount
while @.counter > 0
begin
select @.constraint = pkname, @.table = tablename from @.pklist where ident =
@.counter
exec ('alter table [' + @.table + '] drop constraint [' + @.constraint + ']')
set @.counter = @.counter - 1
end
"Frank Lee" <Reply@.to.newsgroup> wrote in message
news:ezgjh5CEGHA.3528@.TK2MSFTNGP12.phx.gbl...
> How to drop all primary key of all tables in DB?
> After reading a document for explaning how to use clustered index, I knew
> set all primary key to be clustered is not correct for me.
> I would like to drop them. recreate a new nonclustered primary key and
> create other clustered indexes.
> Now I would like to know how to drop all primary key of all tables in DB
> by T-SQL, because there are too many tables to drop primary key by hand.
> Thanks
> --UsingSQL2005Dev
>|||Hi
You should extend Uri's solution so that all Foreign Keys that reference
your Primary Key is removed before trying to remove the Primary Key.
John
"Frank Lee" <Reply@.to.newsgroup> wrote in message
news:ezgjh5CEGHA.3528@.TK2MSFTNGP12.phx.gbl...
> How to drop all primary key of all tables in DB?
> After reading a document for explaning how to use clustered index, I knew
> set all primary key to be clustered is not correct for me.
> I would like to drop them. recreate a new nonclustered primary key and
> create other clustered indexes.
> Now I would like to know how to drop all primary key of all tables in DB
> by T-SQL, because there are too many tables to drop primary key by hand.
> Thanks
> --UsingSQL2005Dev
>|||I see. Thx.
"John Bell" <jbellnewsposts@.hotmail.com> glsD:eNvQsxGEGHA.3892@.TK2MSFTNGP10.phx.g
bl...
> Hi
> You should extend Uri's solution so that all Foreign Keys that reference
> your Primary Key is removed before trying to remove the Primary Key.
> John
> "Frank Lee" <Reply@.to.newsgroup> wrote in message
> news:ezgjh5CEGHA.3528@.TK2MSFTNGP12.phx.gbl...
>

How to drop all PKs on tables in database?

I have a list of 35 tables that need to drop the primary key index from in my database.

My problem is as follows for these 35 tables:

1. How can I get a list of all the primary keys for this subset of tables in my database
2. How can I drop just the PK for each of these tables?

I want an easy quick way to do this without having to manually do this for each of the 35 tables in my database. I dont want to do this for all tables just the subset.

Thanksdeclare @.Table varchar(100)
declare @.PK varchar(100)
declare cur cursor for select name,object_name(parent_obj) from sysobjects where xtype='PK' and object_name(parent_obj) in ('table1','table2',.....)
open cur
fetch next from cur into @.PK, @.Table
while @.@.fetch_status = 0
begin
exec ('alter table ' + @.Table + ' drop constraint ' + @.PK )
fetch next from cur into @.PK, @.Table
end
close cur
deallocate cur|||I think it would be safer to do this inside the loop:

print 'alter table ' + @.Table + ' drop constraint ' + @.PK

rather than this:

exec ('alter table ' + @.Table + ' drop constraint ' + @.PK )

that way, you can inspect the result for correctness, make sure you really want to execute it, etc.

when playing the sql-from-sql game, you should execute only after inspecting the result, IMO.|||the answer mention above would goes wrong if foreign keys are there so we need to drop all foreing keys first then apply above mention procedure
1 step first
drop all foreign keys relation ship
declare @.Table varchar(100)
declare @.FK varchar(100)
declare cur cursor for select name,object_name(parent_obj) from sysobjects where xtype='F' and object_name(parent_obj) in ('Table1','Table2',...)
open cur
fetch next from cur into @.FK, @.Table
while @.@.fetch_status = 0
begin
exec ('alter table ' + @.Table + ' drop constraint ' + @.FK )
fetch next from cur into @.FK, @.Table
end
close cur
deallocate cur
2. step second now drop all primary key relation ship
declare @.Table varchar(100)
declare @.PK varchar(100)
declare cur cursor for select name,object_name(parent_obj) from sysobjects where xtype='PK' and object_name(parent_obj) in ('table1','table2',.....)
open cur
fetch next from cur into @.PK, @.Table
while @.@.fetch_status = 0
begin
exec ('alter table ' + @.Table + ' drop constraint ' + @.PK )
fetch next from cur into @.PK, @.Table
end
close cur
deallocate cur|||right... we need to drop the FKs first.
but the code from greenindia is having the following problems

1) xtype for foriegn-key is "F" and not "FK"
2) who says that the tables with FKs belongs to the same set of that with PKs? since the same set of tables are used in the code
object_name(parent_obj) in ('Table1','Table2',...)

u need a trip from sysforeignkeys to trap the relation properly :rolleyes:|||you are my dear friend upalsen , xtype for foreign key should have been F instead of FK.|||anybody think of asking our friend why he would want to do this before we hand him a loaded gun?|||anybody think of asking our friend why he would want to do this before we hand him a loaded gun?

What would be the fun in that?|||Hi all,

Thanks for your help, however I cannot see the output of the print commands in SQL Server Query Analyzer when I run the cursor script:

declare @.Table varchar(100)
declare @.PK varchar(100)
declare cur cursor for select name,object_name(parent_obj) from sysobjects where xtype='P' and object_name(parent_obj) in ('table1', 'table2', 'table3')
open cur
fetch next from cur into @.PK, @.Table
while @.@.fetch_status = 0
begin
print ('alter table ' + @.Table + ' drop constraint ' + @.PK )
fetch next from cur into @.PK, @.Table
end
close cur
deallocate cur

print 'alter table ' + @.Table + ' drop constraint ' + @.PK|||does this query return anything? if not, there's your answer.

select name,object_name(parent_obj) from sysobjects where xtype='P' and object_name(parent_obj) in ('table1', 'table2', 'table3')

how to do this select query?

I have a table that looks something like this:

CREATE TABLE Oval_Import
(
IdNum INT NOT NULL PRIMARY KEY,
DOB datetime NOT NULL,
.
.
.
.
.
)

I'm trying to select all the records from the table (notice the DOB date field returns only the date part), but I ran into problems displaying the rest of the fields after the DOB.

I tried a select query like this but it returned every column 'again' after the DOB:

select IdNum, convert(varchar,DOB,111), * from Oval_Import;
I don't want to explicitly select each individual column after that either because there are very many after the DOB.

So how can I select the rest of the fields with the * but excluding the IdNum and the DOB columns?

It's a good practice to explicitly define each column in the select statement. Also, when you do special conversion on a column, you are essentially creating a new column. It's not possible to remove the original column from the select if you don't explicitly do so.|||

Ok, how about when I do an insert.

I can perform a query like this in sql server:

insert into Oval_Import (IdNum, DOB) values (226882, '1982/1/9');

but I can't do the following, it gives me a "Incorrect syntax near the keyword 'set'." error:

insert into Oval_Import set IdNum=226882, DOB='1982/1/9';
I know in mysql you can perform the latter query for insertion, but how can you do something similar in sql server? I need an insertion query where I can explicitly see which columns are being assigned to which values because my table have many columns.

|||

The INSERT statement syntax supported by SQL Server is the same as that of the ANSI SQL standards. You can only use the VALUES clause to specify the column values. There are extensions to the insert statement that allow you to do insert...Select or insert..exec for example. Perhaps you can do:

insert into Oval_Import (IdNum, DOB)
select 226882 as IdNum, '1982/1/9' as DOB

But note that the columns between the select and insert statement list is still by position.

How to do bulk Delete

I have a table that has a primary key made of 3 fields (we don't want to use a surrogate key in this situation). In a particular process there is a work table that contains these 3 PK fields and we want to bulk delete them from the base table. Without looping thru the work table, how can I write a Delete statement to delete records in the base table using the work table rows as the criteria?

This syntax illustrates what I want to do, but is not allowed by SQL.

DELETE bt.* FROM basetable bt
INNER JOIN #work wk On bt.fld1 = wk.fld1 And bt.fld2 = wk.fld2 And bt.fld3 = wk.fld3

The correct DELETE statement using TSQL extension is below:

DELETE basetable

FROM basetable bt
INNER JOIN #work wk

ON bt.fld1 = wk.fld1 And bt.fld2 = wk.fld2 And bt.fld3 = wk.fld3

But best is to use the ANSI SQL syntax which doesn't have any ambiguity:

DELETE FROM basetable

WHERE EXISTS(SELECT * FROM #work as wk

WHERE wk.fld1 = basetable.fld1

AND wk.fld2 = basetable.fld2

AND wk.fld3 = basetable.fld3)

|||Ahhh!!! I tried so many variations .... except that one. Thanks.