Updating records

[minli98]

Hi,

I have a simple table with two keys: ID and count. Now I have a list of id's with their corresponding count that I want to integrate into the table in the following manner:

If ID exists in the table, update the count by adding the count in the table with the new count.

If ID does not already exist, append this ID and its count to the table.

My question is whether there is a way I could avoid looping thru the list of id's to first check its existence in the table before doing either UPDATE or INSERT. It just seems that there should be a faster way of doing it.

Thanks all.

Regards,

 

[bborissov]

Trigger?
FOR INSERT/UPDATE/DELETE
But I am not so sure you want exactly. Could you please poset some example?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[minli98]

Hi Borislav,

Here's a simple illustration of the problem:

Table 1:
Id Count
***********
A 2
B 5
C 1

Table 2:
Id Count
***********
B 2
D 1

How do I update table 1 with data from table 2 such that the new table 1 is
Id Count
***********
A 2
B 7
C 1
D 1

The crude way is to go through table 2 and check whether each id exists in table 1, if so do UPDATE, else do INSERT. Is there a simpler and faster way since in reality my table 1 and table 2 are quite large.

Thanks.

Regards,
Min

 

[bborissov]

Create a TRIGGER in TAble2:

CREATE TRIGGER trg_Table2
   ON  Table2
   FOR INSERT,DELETE
AS 
BEGIN
    SET NOCOUNT ON;
    UPDATE Table1
           SET [Count] = [Count] + Tbl2.Cnt*Tbl2.Koef
    FROM Table1
    INNER JOIN (SELECT Id, COUNT(*) AS Cnt
                       -1 AS Koef
                       FROM DELETED 
                GROUP BY Id
                UNION ALL
                SELECT Id, COUNT(*) AS Cnt
                       1 AS Koef
                       FROM Inserted
                GROUP BY Id) Tbl1
         ON Talbe1.Id = Tbl1.Id


    INSERT INTO Table1 (Id, [Count])
    SELECT (SELECT INSERTED.Id, COUNT(*) AS Cnt
                   FROM INSERTED
            LEFT JOIN Table1 ON Tabl1.Id = INSERTED.Id
            WHERE Table1.Id IS NULL
            GROUP BY INSERTED.Id)
END

NOT TESTED

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[minli98]

Boris,

Ok thanks. I will give it a try.

Regards,
Min

 

[bborissov]

Make sure you have a GOOD BACKUP first


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP