deleting rows from one table when they exist in another

A project I'm working on has a daily update of a table and then reapplies
each day a table of updates called EditHistory.
What's the best way to do this while avoiding duplicate rows.
I want to copy all rows from the table that's been updated, then remove any
records that just came in that are also in EditHistory and then copy the
ones from EditHistory in. This way I'll be left with a combination of the
records that were never edited along with the ones that have been edited but
the ones have been changed would be gone because the ones from EditHistory
override them.
Thanks for your help.Hi Bob
The better option would be not to insert existing rows
UPDATE m
SET x = O.x, y=O.y
FROM MyTable m
JOIN MyOtherTable O ON m.PK = O.PK
INSERT MyTable ( PK, x , y )
SELECT O.PK, O.x, O.y
FROM myOtherTable O
LEFT JOIN MyTable m ON m.PK = O.PK
WHERE m.PK IS NULL
John
"bob" wrote:

> A project I'm working on has a daily update of a table and then reapplies
> each day a table of updates called EditHistory.
> What's the best way to do this while avoiding duplicate rows.
> I want to copy all rows from the table that's been updated, then remove an
y
> records that just came in that are also in EditHistory and then copy the
> ones from EditHistory in. This way I'll be left with a combination of the
> records that were never edited along with the ones that have been edited b
ut
> the ones have been changed would be gone because the ones from EditHistory
> override them.
> Thanks for your help.
>
>sql

deleting rows from one table when they exist in another

A project I'm working on has a daily update of a table and then reapplies
each day a table of updates called EditHistory.
What's the best way to do this while avoiding duplicate rows.
I want to copy all rows from the table that's been updated, then remove any
records that just came in that are also in EditHistory and then copy the
ones from EditHistory in. This way I'll be left with a combination of the
records that were never edited along with the ones that have been edited but
the ones have been changed would be gone because the ones from EditHistory
override them.
Thanks for your help.Hi Bob
The better option would be not to insert existing rows
UPDATE m
SET x = O.x, y=O.y
FROM MyTable m
JOIN MyOtherTable O ON m.PK = O.PK
INSERT MyTable ( PK, x , y )
SELECT O.PK, O.x, O.y
FROM myOtherTable O
LEFT JOIN MyTable m ON m.PK = O.PK
WHERE m.PK IS NULL
John
"bob" wrote:
> A project I'm working on has a daily update of a table and then reapplies
> each day a table of updates called EditHistory.
> What's the best way to do this while avoiding duplicate rows.
> I want to copy all rows from the table that's been updated, then remove any
> records that just came in that are also in EditHistory and then copy the
> ones from EditHistory in. This way I'll be left with a combination of the
> records that were never edited along with the ones that have been edited but
> the ones have been changed would be gone because the ones from EditHistory
> override them.
> Thanks for your help.
>
>

Deleting from a table

Im working on a project that import data into a gash db. there are about 15
tables in this db and each phase of testing requires the tables to be
cleared. This seems to take ages when i got run a query as follows
delete table1
delete table2
etc
any suggestionon speeding this up ?use
truncate table table1
Use this.. if you don't need the data again. Its faster because its not
logged.
Hope this helps.
--
"Peter Newman" wrote:

> Im working on a project that import data into a gash db. there are about 1
5
> tables in this db and each phase of testing requires the tables to be
> cleared. This seems to take ages when i got run a query as follows
> delete table1
> delete table2
> etc
> any suggestionon speeding this up ?|||You might consider using TRUNCATE TABLE instead. This statement generally
uses few locks and less log space.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:59687486-40A1-43C0-BBBA-1068519772F2@.microsoft.com...
> Im working on a project that import data into a gash db. there are about
> 15
> tables in this db and each phase of testing requires the tables to be
> cleared. This seems to take ages when i got run a query as follows
> delete table1
> delete table2
> etc
> any suggestionon speeding this up ?

Deleted table.

Hi all,

I'm struck, i'm working on a production support by mistake i deleted a table without a transaction, is there anyway i could retrieve the records that got deleted. :(

Thanks in advance.

Arun

restore from backup to another database and copy the records over.

Good luck|||

Is there anyway by which i can retrieve it from transaction log.

|||

Possibly, if you haven't backed-up the transaction log since the deletion and if your database's recovery model is not 'Simple'.

One of these tools should be able to help:

http://www.red-gate.com/products/sql_log_rescue/index.htm

http://www.lumigent.com/products/le_sql.html

For future reference, it's a good idea to execute BEGIN TRANSACTION before making manual corrections to Production data - that way you can always ROLLBACK if your code doesn't behave as expected.

Chris

|||If you can get it from a backup, I would highly recommend that. I know it stinks, but it will be the most straightforward (and the most supported :)

delete sql not working.

hi

win 2k and xp
excel 2k
sqlserver version 7
the code below execute but when i query the table, the data is still in there. can anyone help?

Sub UPDATED_DELETE()

Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
dim MyDate As Date

MyDate = Format(Date, "MM/DD/YYYY")

Set conn = New ADODB.Connection
Set cmd = New ADODB.Command

conn.ConnectionString = "ODBC=SQL Server;DSN=LOGCALL_TABLE;UID=richard;APP=Microsoft Query;WSID=RICHARD;Trusted_Connection=Yes"
conn.ConnectionTimeout = 30
conn.Open

Set cmd.ActiveConnection = conn

cmd.CommandText = "DELETE FROM LOGCALL_TABLE WHERE LOGCALL_TABLE.OpenCall like 'X' AND LOGCALL_TABLE.StopTime like '" & Format(Range("I" & CStr(ActiveCell.Row)).Value, "HH:MM:SS") & "' AND LOGCALL_TABLE.EndTime like '" & Format(Range("J" & CStr(ActiveCell.Row)).Value, "HH:MM:SS") & "' AND LOGCALL_TABLE.ClientName like '" & Range("B" & CStr(ActiveCell.Row)).Value & "' AND LOGCALL_TABLE.Representative like '" & Range("C1").Value & "' and LOGCALL_TABLE.DateOnCall like '" & Date & "';"
cmd.Execute
conn.Close
End SubI think it has to do with the date and the way it is formated in my sql statement.

.......and LOGCALL_TABLE.DateOnCall like '" & Date & "';"
cmd.Execute

i had it formated this way before as required by ms access, but that does not work.

..... and LOGCALL_DB.DateOnCall = # " & Date & " #;"

any thoughts on how to format this baby?

thanks in advance.

Alex|||I think it has to do with the date and the way it is formated in my sql statement.

.......and LOGCALL_TABLE.DateOnCall like '" & Date & "';"
cmd.Execute

i had it formated this way before as required by ms access, but that does not work.

..... and LOGCALL_DB.DateOnCall = # " & Date & " #;"

any thoughts on how to format this baby?

thanks in advance.

Alex|||to begin with, LIKE should only be used with strings

what is DateOnCall? datetime or varchar?|||Be smart and create a stored procedure in you SQL Server database that accepts StopTime, EndTime, ClientName, Representative, and DateOnCall as parameters and deletes the records you want. Then just call the procedure with the values from your spreadsheet.

...and look up the syntax and usage of the LIKE operator too. I suspect it needs wildcards, or at the very least is inappropriate for Date values (as Rudy said).

But I really think the problem is in your methodology, not your syntax...

DELETE rows in MSDE

Hi..

DELETE FROM table1 WHERE projektID=5

there are 500000 rows that has projektID=5.. and when i run the query the hardrive is working for a couple of minutes and then stops. and NOTHING has happened. not a single row has been deleted?.. cant the DELETE statement handle that many rows or?. or is there another way i can delete these rows?.yeah, it can handle that. I've deleted more than 500,000 rows before (sometimes by accident!)

DELETE table1 WHERE projektID = 5

are you running this from query analyzer or from ASP.NET?|||im running it from asp.net, there is no query analyser for MSDE.
but it doesnt happen anything when do it, except for my harddrive gets occupied for 10 min or so.|||> there is no query analyser for MSDE.

sort of. MSDE being the SQL engine, you can connect to it with SQL Server client tools, and many people do.

do me a favour. install the 180 day SQL Server trial - client tools only. then run it through QA

Delete recordsets with same Date and Line

Hi All!

I need help with a Statement!
I am working with an Access2000 DB.
I have the following Problem.

ChNrLinieDatum Code 39 Stckzahl BHL1 BHL2 BMRH
582-064L2.1008.03.2005 02:30:00FCAA14821701
582-064L2.1008.03.2005 02:30:00FCAA14871701
582-114L2.1208.03.2005 01:00:00FAC827501240
582-114L2.1208.03.2005 01:00:00FAC827441240
582-114L2.1208.03.2005 01:00:00FAC827501240
582-094L2.707.03.2005 19:45:00FAE74323481
582-094L2.707.03.2005 19:45:00FAE74489481
582-094L2.707.03.2005 19:45:00FAE74489481
581-294L2.807.03.2005 18:20:00FA8V2658221
581-294L2.807.03.2005 18:20:00FA8V2652221
581-294L2.807.03.2005 18:20:00FA8V2658221
582-114L2.1207.03.2005 17:45:00FAAR20721236
As you can see I have a few recordsets that are double. The Thing is, there
is an ID that makes them different.

I need a Statement that deletes the surplus records where 'Datum' and
'Linie' are identical to another record. 1 record has to remain of course.
I thought of something like this.

DELETE FROM tbAuswert
WHERE EXISTS(
SELECT *
FROM tbAuswert
WHERE (Linie AND Datum)
IN (
SELECT Linie AND Datum AS Suchkrit
FROM tbAuswert
GROUP BY Suchkrit
HAVING ((Count(Suchkrit)>1)
)
)

But I get an error:
You wanted to execute a Query that did not have the following expression
'Not Linie = 0 And Not Datum = 0' in its Aggregatefunction

Perhaps you ccan help me.

Thanks
Julia

--
Message posted via http://www.sqlmonster.comJulia Hrtfelder via SQLMonster.com (forum@.nospam.SQLMonster.com) writes:
> I need help with a Statement!
> I am working with an Access2000 DB.
>...
> I need a Statement that deletes the surplus records where 'Datum' and
> 'Linie' are identical to another record. 1 record has to remain of course.
> I thought of something like this.
> DELETE FROM tbAuswert
> WHERE EXISTS(
> SELECT *
> FROM tbAuswert
> WHERE (Linie AND Datum)
> IN (
> SELECT Linie AND Datum AS Suchkrit
> FROM tbAuswert
> GROUP BY Suchkrit
> HAVING ((Count(Suchkrit)>1)
> )
> )
> But I get an error:
> You wanted to execute a Query that did not have the following expression
> 'Not Linie = 0 And Not Datum = 0' in its Aggregatefunction
> Perhaps you ccan help me.

If you don't have a primary that uniquely identifies each row, then
this will be very difficult, as SQL is designed to operate only from the
data in the tables.

It's possible that you can add a column that gives you a unique ID. Had
you been using SQL Server, I could have showed you how. However, since
you are using Access, you are better off posting your question to a
forum for Access. There are considerable differences between the SQL in
SQL Server and Access.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Tue, 17 May 2005 12:09:04 GMT, Julia Hrtfelder via SQLMonster.com
wrote:

>Hi All!
>I need help with a Statement!
>I am working with an Access2000 DB.
>I have the following Problem.
(snip)
>As you can see I have a few recordsets that are double. The Thing is, there
>is an ID that makes them different.
>I need a Statement that deletes the surplus records where 'Datum' and
>'Linie' are identical to another record. 1 record has to remain of course.

Hi Julia,

The following will work for SQL Server. I'm not sure about Access though
(it deviates from the standard in other ways than SQL Server does <g>),
so test it first, and repost in an Access group if this doesn't work.

Note: I assume that the ID column is called "ID" and that you want to
retain the row with the lowest ID value.

DELETE FROM tbAuswert
WHERE EXISTS
(SELECT *
FROM tbAuswert AS a2
WHERE a2.Datum = tbAuswert.Datum
AND a2.Linie = tbAuswert.Linie
AND a2.ID > tbAuswert.ID)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||That one really worked out!

Thank you so much. You are great!

Julia

--
Message posted via http://www.sqlmonster.com

Delete records in destination table

Short Question: How do I delete all records from a destination table prior to appending new data to that table?

I am working with a SQL database that was migrated from MS Access. All relationships, primary keys, and identity columns have been set identically to the MS Access database values. The MS Access database is still being used as the database of record until the SQL database is fully functional with front-end, etc.

I want to delete the information stored in all the SQL tables, and then append the MS Access values to the SQL tables. I was able to write delete and append queries in MS Access to correctly transfer data to the SQL tables. However, I would prefer doing this through SSIS because I have several other sources of data to move to a SQL Server database and most of those other sources are not a MS Access database.

Due to relational entegrity settings, I need to delete the records from 8 tables in a specific order. I have tried independent control objects for each of the 8 tables with data flow objects of either "OLE DB Command" or "OLE DB Source" with the SQL command as "Delete From TableName". Results of the debug indicate everything is "green" but no records were deleted fromt the tables.

Maybe you could just generate scripts for the entire DB in Management Studio, and create a new database in SQLServer . You could use this new DB as the destination.

For the actual migration of data, you could use SSIS.

|||

Can't you just use an Execute SQL Task (or just use T-SQL through the management studio) and truncate the tables in the order of constraints?

Truncate Table a; Truncate Table b; etc...

|||

EWisdahl is correct. Use an Execute SQL to run the DELETE FROM statement, then your data flow after that.

Execute SQL -> DataFlow

DELETE Procedure. How to do this?

Hello,

I created a delete procedure which is working but I still have a
problem.

When I delete a localized content from dbo.by27_ContentLocalized given
a ContentName and ContentCulture I want to check if this is the only
record in ContentLocalized for that ContentName.

If it is then I also want to delete the record in dbo.by27_Content
which has that ContentName.

How can I do this?

Thanks,
Miguel

Here is my DELETE procedure:

-- Define the procedure parameters
@.ContentCulture NVARCHAR(5),
@.ContentName NVARCHAR(100)

AS

-- Allows @.@.ROWCOUNT and the return of number of records when
ExecuteNonQuery is used
SET NOCOUNT OFF;

-- Declare and define ContentId
DECLARE @.ContentId UNIQUEIDENTIFIER;
SELECT @.ContentId = ContentId FROM dbo.by27_Content WHERE ContentName =
@.ContentName

-- Check if ContentId is Not Null
IF @.ContentId IS NOT NULL
BEGIN

-- Check if ContentId is Null
IF @.ContentCulture IS NULL
BEGIN

-- Delete all localized contents from dbo.by27_ContentLocalized
DELETE
FROM dbo.by27_ContentLocalized
WHERE ContentId = @.ContentId

-- Delete content from dbo.by27_Content
DELETE
FROM dbo.by27_Content
WHERE ContentName = @.ContentName;

END
ELSE

-- Delete localized content from dbo.by27_ContentLocalized
DELETE
FROM dbo.by27_ContentLocalized
WHERE (ContentID = @.ContentID AND ContentCulture = @.ContentCulture)

END

shapper:

When I delete a localized content from dbo.by27_ContentLocalized given
a ContentName and ContentCulture I want to check if this is the only
record in ContentLocalized for that ContentName.

Can you explain what you mean by "only record in ContentLocalized for that ContentName". Do you mean check if there is only 1 record and delete it? or something else?

|||

I mean that when a record in by27_ContentLocalized is deleted it checks if it is the only one which is related with its parent in by27_Content.

If it is then it deletes also its parent in by27_Content, see?

Thanks,

Miguel

|||

How about adding something like this to the end of the procedure...

IF NOT EXISTS(SELECT * FROM by27_ContentLocalized WHERE ContentId = @.ContentID)
BEGIN
DELETE FROM by27_Content WHERE ContentID = @.ContentID
END

I hope this helps,

Steve

|||You can create a DELETE trigger on dbo.by27_ContentLocalized table, which will check to see whether the deleted row is the only record in ContentLocalized for that ContentName. For example:

CREATE?TRIGGER trg_CheckDeleteCL ON dbo.by27_ContentLocalized AFTER DELETE
AS
SELECT 1 FROM dbo.by27_ContentLocalized b,deleted d
WHERE b.ContentName=d.ContentName
IF(@.@.ROWCOUNT=0)
BEGIN
DECLARE @.msg NVARCHAR(2000)
DELETE dbo.by27_Content FROM dbo.by27_Content c, deleted d
WHERE c.ContentName=d.ContentName
SELECT @.msg='The record with ContentName='''+ContentName+'''deleted from dbo.by27_Content'
FROM deleted
PRINT @.msg
END
go

Delete 'Non-Unique' Rows Question (with DDL)

Hi all,
I'm working on a query which will ultimately be used in a stored proc
for a report. This report will quite simply list the components that
are unique to a particular customers goods. I've created a mechanism
for traversing our BOM structure, and getting a list of all components
used in any customers parts. I then intended to clear out all the
components that were used by more than one customer. Unfortunately,
after about 5 hours effort, I've come up short. Perhaps someone can
offer a suggestion.
The DDL is posted below. What I'm looking for, ultimately, is to get a
'selection' containing only the two "1000" partnumbers, as components
"1001" and "1002" are also used by customer "B". Also, Customer "A" has
two products which use the "1000" component, but its still unique to
them, so we want to keep it in the results.
You can see the DELETE statement I was trying, but it misses the mark
rather wildly. I'm open to tackling this problem a different way to, if
that would provide a clearer solution. In the meanwhile, I'll keep
plugging away at it, perhaps I can get lucky.
Thanks for your help,
Brian Ackermann
---
-- DDL
---
DROP TABLE zz_report
GO
CREATE TABLE zz_report
(
partNumber varchar(10)
, customer varchar(10)
)
INSERT zz_report VALUES ('1000', 'A')
INSERT zz_report VALUES ('1000', 'A')
INSERT zz_report VALUES ('1001', 'A')
INSERT zz_report VALUES ('1002', 'A')
INSERT zz_report VALUES ('1001', 'B')
INSERT zz_report VALUES ('1002', 'B')
INSERT zz_report VALUES ('1002', 'B')
DELETE zz_report FROM zz_report R
WHERE EXISTS
(
SELECT Z.partnumber
FROM zz_report Z
WHERE R.partnumber = Z.partnumber
GROUP BY Z.customer, Z.partnumber
HAVING count(*) <> 1
)
SELECT * FROM zz_report
GO
DROP TABLE zz_report
GOa shot in the dark:
DELETE zz_report FROM zz_report R
WHERE EXISTS
(
SELECT 1
FROM zz_report Z
WHERE R.partnumber = Z.partnumber
AND z.customer > r.customer
)
partNumber customer
-- --
1000 A
1000 A
1001 B
1002 B
1002 B|||Try using a select, without deleting anything...
Select a.Customer, a.partnumber
from zz_report as a
where not exists (select 1
from zz_report b
where b.partnumber = a.partnumber
and b.customer <> a.customer)
"Brian J. Ackermann" <brianj774@.allmail.net> wrote in message
news:gdudnZReWYKmavDZnZ2dnUVZ_tednZ2d@.gi
ganews.com...
> Hi all,
> I'm working on a query which will ultimately be used in a stored proc
> for a report. This report will quite simply list the components that
> are unique to a particular customers goods. I've created a mechanism
> for traversing our BOM structure, and getting a list of all components
> used in any customers parts. I then intended to clear out all the
> components that were used by more than one customer. Unfortunately,
> after about 5 hours effort, I've come up short. Perhaps someone can
> offer a suggestion.
> The DDL is posted below. What I'm looking for, ultimately, is to get a
> 'selection' containing only the two "1000" partnumbers, as components
> "1001" and "1002" are also used by customer "B". Also, Customer "A" has
> two products which use the "1000" component, but its still unique to
> them, so we want to keep it in the results.
> You can see the DELETE statement I was trying, but it misses the mark
> rather wildly. I'm open to tackling this problem a different way to, if
> that would provide a clearer solution. In the meanwhile, I'll keep
> plugging away at it, perhaps I can get lucky.
> Thanks for your help,
> Brian Ackermann
>
> ---
> -- DDL
> ---
> DROP TABLE zz_report
> GO
> CREATE TABLE zz_report
> (
> partNumber varchar(10)
> , customer varchar(10)
> )
>
> INSERT zz_report VALUES ('1000', 'A')
> INSERT zz_report VALUES ('1000', 'A')
> INSERT zz_report VALUES ('1001', 'A')
> INSERT zz_report VALUES ('1002', 'A')
> INSERT zz_report VALUES ('1001', 'B')
> INSERT zz_report VALUES ('1002', 'B')
> INSERT zz_report VALUES ('1002', 'B')
>
> DELETE zz_report FROM zz_report R
> WHERE EXISTS
> (
> SELECT Z.partnumber
> FROM zz_report Z
> WHERE R.partnumber = Z.partnumber
> GROUP BY Z.customer, Z.partnumber
> HAVING count(*) <> 1
> )
>
> SELECT * FROM zz_report
> GO
> DROP TABLE zz_report
> GO|||Jim Underwood wrote:
> Try using a select, without deleting anything...
> Select a.Customer, a.partnumber
> from zz_report as a
> where not exists (select 1
> from zz_report b
> where b.partnumber = a.partnumber
> and b.customer <> a.customer)
Thanks Jim!
This works, at least as far as the small dataset I provided. I'm
running it now on my real data. Unfortunately, its taking quite a long
time to process (about a minute and a half). I'll report back my
findings in a few.|||Brian J. Ackermann wrote
> Thanks Jim!
> This works, at least as far as the small dataset I provided. I'm
> running it now on my real data. Unfortunately, its taking quite a long
> time to process (about a minute and a half). I'll report back my
> findings in a few.
>
Well, ly, its not what I am needing. The very first part I checked
(part "000001") is used in just about every single finished product
produced here, and consequently, is used by nearly every customer. I'll
poke around with it some, because the idea seems good to me...
Brian|||Brian J. Ackermann wrote:
> Well, ly, its not what I am needing. The very first part I checked
> (part "000001") is used in just about every single finished product
> produced here, and consequently, is used by nearly every customer. I'll
> poke around with it some, because the idea seems good to me...
> Brian
Okay, I found a bit of garbage data (some customers with NULL values).
Removing that, I've found that the first hundred items are perfect. The
logic seems strong, so I'm going to go with it.
But, do you have any suggestions for picking up the pace a little bit.
That little query you provided me with takes about 00:01:05 to run. The
table is about 19,000 rows. Perhaps theres some kind of optimization we
can try on it?
Thanks again, I appreciate your help.
Brian Ackermann|||Well, if you post full DDL showing your actual table and indexes, someone
may be able to offer a suggestion.
If partnumber and customer form the primary key of this table (or have a
unique constraint) , then you might try something like...
Select customer, partnumber from zz_report
where partnumber in (
select partnumber
from zz_report
group by partnumber
having count(customer) = 1
)
Having an index on partnumber may help in either case. Note, this is an
index with partnumber as the first column, not the second.
"Brian J. Ackermann" <brianj774@.allmail.net> wrote in message
news:446E1199.8070504@.allmail.net...
> Brian J. Ackermann wrote:
> Okay, I found a bit of garbage data (some customers with NULL values).
> Removing that, I've found that the first hundred items are perfect. The
> logic seems strong, so I'm going to go with it.
> But, do you have any suggestions for picking up the pace a little bit.
> That little query you provided me with takes about 00:01:05 to run. The
> table is about 19,000 rows. Perhaps theres some kind of optimization we
> can try on it?
> Thanks again, I appreciate your help.
> Brian Ackermann|||Jim Underwood wrote:
> Well, if you post full DDL showing your actual table and indexes, someone
> may be able to offer a suggestion.
The DDL is pretty much as I gave it. Its just a temporary table, with
only those two fields. I'll try putting an index on it, and see if that
make a big difference. I expect it will.
Thanks again for your excellent help.
Brian.|||Do you really need the temporary table? If the data is coming from an
existing table, why not access it directly? Or is this data coming from an
application, and not the database?
"Brian J. Ackermann" <brianj774@.allmail.net> wrote in message
news:hbSdnfRYaMeOh_PZRVn-uw@.giganews.com...
> Jim Underwood wrote:
someone
> The DDL is pretty much as I gave it. Its just a temporary table, with
> only those two fields. I'll try putting an index on it, and see if that
> make a big difference. I expect it will.
> Thanks again for your excellent help.
> Brian.
>|||Jim Underwood wrote:
> Do you really need the temporary table? If the data is coming from an
> existing table, why not access it directly? Or is this data coming from a
n
> application, and not the database?
>
I am not sure if I _need_ it, but I can't really think of a better way
to get to it. All of that data comes out of our Bill of Material
Tables, which is a hierarchical data structure, and some of our parts
are only 2 levels deep, and some are 5 levels deep, and even that could
change in the future.
Presently, I am using a cursor to loop over all our 'non-obsolete'
parts, and then a UDF which navigates the BOM, pulling out all the
components along the way. The results of this UDF I put into the temp
table.
I'm quite certain there are other ways to tackle this problem, but that
seemed the most likely to generate easily understood results.
Brian

Delete matched query

I'm working with a legacy application that stores its data in a SQL Server 2
K
database. I want to write a delete query to delete items from table A, wher
e
there is no match in table B, where the match is based on matches of the two
PK fields.
I tried the following, but got an error: Incorrect syntax near the keyword
'LEFT'
DELETE FROM A
LEFT JOIN B
ON A.ExerciseID = B.ExerciseID
AND A.UserID = B.UserID
WHERE B.UserID IS NULL
Any help would be greatly appreciated.
DaleHow about this?
DELETE A
FROM A
INNER JOIN B
ON A.ExerciseID = B.ExerciseID
AND A.UserID = B.UserID
WHERE B.UserID IS NULL|||Try
DELETE A
From A LEFT JOIN B
ON A.ExerciseID = B.ExerciseID
AND A.UserID = B.UserID
WHERE B.UserID IS NULL
But beware, when you use this syntax, if you uswe Table Aliases, i.e., if
the Tables are actually name dTableA and TableB, then the First line has t
ouse the Alias, not the actual Table Name,. i.e.,
DELETE A
From TableA A Left Join TableB B
ON A.ExerciseID = B.ExerciseID
AND A.UserID = B.UserID
WHERE B.UserID IS NULL
A Much clearer way to code this is to use SQL that mirrors exactly what you
want
Delete TableA
Where Not Exists
(Select * From TableB
Where PK = TableA.PK)
"Dale Fye" wrote:

> I'm working with a legacy application that stores its data in a SQL Server
2K
> database. I want to write a delete query to delete items from table A, wh
ere
> there is no match in table B, where the match is based on matches of the t
wo
> PK fields.
> I tried the following, but got an error: Incorrect syntax near the keyword
> 'LEFT'
> DELETE FROM A
> LEFT JOIN B
> ON A.ExerciseID = B.ExerciseID
> AND A.UserID = B.UserID
> WHERE B.UserID IS NULL
> Any help would be greatly appreciated.
> Dale|||I meant
Delete TableA
Where Not Exists
(Select * From TableB
Where ExerciseID = TableA.ExerciseID
And UserID = TableA.UserID)
"CBretana" wrote:
> Try
> DELETE A
> From A LEFT JOIN B
> ON A.ExerciseID = B.ExerciseID
> AND A.UserID = B.UserID
> WHERE B.UserID IS NULL
> But beware, when you use this syntax, if you uswe Table Aliases, i.e., if
> the Tables are actually name dTableA and TableB, then the First line has t
> ouse the Alias, not the actual Table Name,. i.e.,
> DELETE A
> From TableA A Left Join TableB B
> ON A.ExerciseID = B.ExerciseID
> AND A.UserID = B.UserID
> WHERE B.UserID IS NULL
> A Much clearer way to code this is to use SQL that mirrors exactly what yo
u
> want
> Delete TableA
> Where Not Exists
> (Select * From TableB
> Where PK = TableA.PK)
> "Dale Fye" wrote:
>|||Try this
Delete From A
Where Not Exists(
Select * From B Where B.Col1=A.Col1 And B.Col2=A.Col2
)
Dmitriy
"Dale Fye" <dale.fye@.nospam.com> wrote in message
news:B16B3312-7EDD-4D85-A2D0-7806F2064BEE@.microsoft.com...
> I'm working with a legacy application that stores its data in a SQL Server
2K
> database. I want to write a delete query to delete items from table A,
where
> there is no match in table B, where the match is based on matches of the
two
> PK fields.
> I tried the following, but got an error: Incorrect syntax near the keyword
> 'LEFT'
> DELETE FROM A
> LEFT JOIN B
> ON A.ExerciseID = B.ExerciseID
> AND A.UserID = B.UserID
> WHERE B.UserID IS NULL
> Any help would be greatly appreciated.
> Dale|||Dale Fye wrote:
> I'm working with a legacy application that stores its data in a SQL Server
2K
> database. I want to write a delete query to delete items from table A, wh
ere
> there is no match in table B, where the match is based on matches of the t
wo
> PK fields.
> I tried the following, but got an error: Incorrect syntax near the keyword
> 'LEFT'
> DELETE FROM A
> LEFT JOIN B
> ON A.ExerciseID = B.ExerciseID
> AND A.UserID = B.UserID
> WHERE B.UserID IS NULL
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Try this:
DELETE FROM A
WHERE NOT EXISTS (SELECT * FROM B
WHERE B.ExerciseID = A.ExerciseID
AND B.UserID = A.UserID)
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQjX6lIechKqOuFEgEQIHQQCfe67DnoBuMAKw
lKtCX8+H7Wd9ANAAmwS2
sbyoMnwLgSk2DmyMUtxtgESf
=hYSb
--END PGP SIGNATURE--|||Thanks to all who responded. I've been working in Access so long, I forgot
about Exists and Not Exists.
"CBretana" wrote:
> Try
> DELETE A
> From A LEFT JOIN B
> ON A.ExerciseID = B.ExerciseID
> AND A.UserID = B.UserID
> WHERE B.UserID IS NULL
> But beware, when you use this syntax, if you uswe Table Aliases, i.e., if
> the Tables are actually name dTableA and TableB, then the First line has t
> ouse the Alias, not the actual Table Name,. i.e.,
> DELETE A
> From TableA A Left Join TableB B
> ON A.ExerciseID = B.ExerciseID
> AND A.UserID = B.UserID
> WHERE B.UserID IS NULL
> A Much clearer way to code this is to use SQL that mirrors exactly what yo
u
> want
> Delete TableA
> Where Not Exists
> (Select * From TableB
> Where PK = TableA.PK)
> "Dale Fye" wrote:
>