Delete all records in tblA not matching in tblB

I'm hoping someone can tell me how to construct a stored procedure that
deletes all records in tblA not matching the PK in tblB

This gives me the recordset of all records in tblA with no matching
records in tblB (ID is the PK in tblB)

SELECT a.ID
FROM dbo.tblB b
RIGHT OUTER JOIN dbo.tblA a ON b.ID = a.ID
WHERE
b.ID IS NULL

thanks,
lqDELETE FROM tblA
WHERE NOT EXISTS
(SELECT *
FROM tblB
WHERE tblB.id = tblA.id)

(Untested. Make sure you have a current backup and test it out for
yourself first.)

--
David Portas
SQL Server MVP
--|||Thanks for that. I have never used NOT EXISTS before. lq

Defining a concatenated column size

I have a column within a view that is a concatenation of several different columns of another table. It goes something like: tblA.columnA + '-' tblA.columnB + '-' tblA.columnC + '-' tblA.columnD As 'NewColumn'
Is it possible to define the size of the 'NewColumn' within the view? I have a stored procedure that is going to create a temp table based on this view, but when I execute the stored procedure I get the following error:
Line 1: Incorrect syntax near '*'.
In looking at the stored procedure code, the error is a result of when the create table occurs, it defines the NewColumn as nvarchar(*), which is causing the error.
Any help would be appreciated.
Thanks.
On Fri, 18 Jun 2004 07:49:01 -0700, Kirk wrote:

>I have a column within a view that is a concatenation of several different columns of another table. It goes something like: tblA.columnA + '-' tblA.columnB + '-' tblA.columnC + '-' tblA.columnD As 'NewColumn'
>Is it possible to define the size of the 'NewColumn' within the view? I have a stored procedure that is going to create a temp table based on this view, but when I execute the stored procedure I get the following error:
>Line 1: Incorrect syntax near '*'.
>In looking at the stored procedure code, the error is a result of when the create table occurs, it defines the NewColumn as nvarchar(*), which is causing the error.
>Any help would be appreciated.
>Thanks.
Hi Kirk,
Define the column as nvarchar(4000) (the maximum length). Or, if you want
the max. length to be shorter, use a lower number. The result of the
concatenation will be trimmed to the max. length the column holds (and you
may also choose to trim explicitly, using LEFT or CAST function).
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)