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 and Updating from Gridview

Hi,

I made a gridview, and I am trying to make it so when the user deletes a row, values in other tables update. I used the following source code:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
DeleteCommand="DELETE FROM [Transactions] WHERE [TransactionID] = @.TransactionIDAND UPDATE [items] SET Quantityavailable, numtaken VALUES Quantityavailable + 1, numtaken - 1 WHERE ([Itemid] = @.Itemid) "

It gives the error that Quantityavailable is not a SET type?

Thanks if you can suggest a remedy!

Jon

jbear123:

UPDATE [items] SET Quantityavailable, numtaken VALUES Quantityavailable + 1, numtaken - 1 WHERE ([Itemid] = @.Itemid

That is not valid syntax for an UPDATE statement.

Try this

UPDATE itemsSET Quantityavailable = Quantityavailable + 1, numtaken = numtaken - 1WHERE ItemId = @.ItemId

|||

Thanks!

Delete Update Behavior of the SSEv Engine

Can someone on the team tell me if a delete or update orphans a row in the SQL Server Everywhere database that must be compacted out to free the space? I understand that Compact runs automatically when the last connection is closed but does this create another copy of the database as it does with JET or is this an update-in-place. Is this an opportunity to corrupt the database if the process is interrupted by power-failure or a truck running over the device from right-to-left?

Hmm..I think there is some misunderstanding here. When the last connection to database is closed COMPAT IS NOT RUN automatically. There is a similar thing called 'Auto Shrink' that runs if the space available (and sparse) is more than a threshold. COMPACT need to be run explicitly. When you run COMPACT, you can specify the destination database. The destination database can also be same as the source database file. In any case, the compaction first happens into a temp database file, the original file deleted, the temp files gets renamed.

Thanks,

Laxmi

Delete Update Behavior of the SSEv Engine

Can someone on the team tell me if a delete or update orphans a row in the SQL Server Everywhere database that must be compacted out to free the space? I understand that Compact runs automatically when the last connection is closed but does this create another copy of the database as it does with JET or is this an update-in-place. Is this an opportunity to corrupt the database if the process is interrupted by power-failure or a truck running over the device from right-to-left?

Hmm..I think there is some misunderstanding here. When the last connection to database is closed COMPAT IS NOT RUN automatically. There is a similar thing called 'Auto Shrink' that runs if the space available (and sparse) is more than a threshold. COMPACT need to be run explicitly. When you run COMPACT, you can specify the destination database. The destination database can also be same as the source database file. In any case, the compaction first happens into a temp database file, the original file deleted, the temp files gets renamed.

Thanks,

Laxmi

delete triggers

sql2k sp3
I assumed this would work as easily as the Insert and Update Triggers I
wrote did:
create trigger DelAuthors
on authors
for delete
as
begin
set nocount on
delete tmp_authors
where tmp_authors.au_id = deleted.au_id
end
Server: Msg 107, Level 16, State 2, Procedure DelAuthors, Line 7
The column prefix 'deleted' does not match with a table name or alias name
used in the query.
Heres the schema for tmp_authors(and Authors):
CREATE TABLE [dbo].[tmp_authors] (
[au_id] [id] NOT NULL ,
[au_lname] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[au_fname] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[phone] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[address] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[city] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[state] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[zip] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[contract] [bit] NOT NULL
) ON [PRIMARY]
GO
Help is appreciated.
TIA, ChrisRChange your DELETE statement to:
DELETE FROM tmp_authors
WHERE EXISTS
(SELECT *
FROM deleted
WHERE tmp_authors.au_id = deleted.au_id)
That's the ANSI Standard update syntax. SQL Server also supports Microsoft
own proprietary version of the DELETE statement using joins and which,
bizarely, requires an extra FROM clause to do what you are attempting.
DELETE
FROM tmp_authors
FROM deleted
WHERE tmp_authors.au_id = deleted.au_id
David Portas
SQL Server MVP
--|||Thanks David.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:ZLWdnU8W65uGbv_fRVn-tw@.giganews.com...
> Change your DELETE statement to:
> DELETE FROM tmp_authors
> WHERE EXISTS
> (SELECT *
> FROM deleted
> WHERE tmp_authors.au_id = deleted.au_id)
> That's the ANSI Standard update syntax. SQL Server also supports Microsoft
> own proprietary version of the DELETE statement using joins and which,
> bizarely, requires an extra FROM clause to do what you are attempting.
> DELETE
> FROM tmp_authors
> FROM deleted
> WHERE tmp_authors.au_id = deleted.au_id
> --
> David Portas
> SQL Server MVP
> --
>