Upsert help. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Upsert help.

Author

Topic

jak3f
Starting Member

33 Posts

Posted - 2009-11-05 : 13:44:17

I want to update fields including based on one field 'EFIN'

ALTER TRIGGER trg_duplicate_prevent
ON dbo.Customers
INSTEAD OF Insert
AS
BEGIN
UPDATE C SET
C.CustomerID = i.CustomerID
,C.Name = i.Name
,C.Address = i.Address
,C.City = i.City
,C.State = i.State
,C.ZipCode = i.ZipCode
,C.Phone = i.Phone
,C.Email = i.Email
,C.RegCode = i.RegCode
,C.AccountNumber = i.AccountNumber
,C.ChargeNumber = i.ChargeNumber
,C.ProductCode = i.ProductCode
,C.PurchaseDate = i.PurchaseDate
,C.PriorSoftware = i.PriorSoftware
,C.Bank = i.Bank
,C.BalanceDue = i.BalanceDue
,C.Renewed = i.Renewed
FROM inserted i
INNER JOIN Customers C
on C.EFIN = i.EFIN WHERE c.EFIN IS NOT NULL

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-05 : 14:54:07

well

you are trying to "change" an inserted row, based on the insert to the table itself?

And you know what the "right" value is how?

Because if it already exists, wouldn't you get a dup key?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-05 : 16:26:53

So it's basically and UPSERT

Where are these INSERTS Being done?

Are they all done in Sprocs, or do you have no control over it?

If it's in a sproc, you could do

IF EXISTS(SELECT * FROM Table Where key = @key)

UPDATE....

ELSE

INSERT....

If you have no control...then LET'EM Fail

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

jak3f
Starting Member

33 Posts

Posted - 2009-11-06 : 10:37:58

Ok change on plans,
I really want to UPDATE all the fields except the PK 'CustomerID' based on the field EFIN.
Right now my trigger looks like this:
ALTER TRIGGER trg_duplicate_prevent
ON dbo.Customers
INSTEAD OF Insert
AS
BEGIN
IF EXISTS(SELECT EFIN FROM Customers C WHERE EFIN IS NOT NULL)
UPDATE C SET
C.Name = i.Name
,C.Address = i.Address
,C.City = i.City
,C.State = i.State
,C.ZipCode = i.ZipCode
,C.Phone = i.Phone
,C.Email = i.Email
,C.EFIN = i.EFIN
,C.RegCode = i.RegCode
,C.AccountNumber = i.AccountNumber
,C.ChargeNumber = i.ChargeNumber
,C.ProductCode = i.ProductCode
,C.PurchaseDate = i.PurchaseDate
,C.PriorSoftware = i.PriorSoftware
,C.Bank = i.Bank
,C.BalanceDue = i.BalanceDue
,C.Renewed = i.Renewed
FROM inserted i
INNER JOIN Customers C
on C.EFIN = i.EFIN WHERE C.EFIN IS NOT NULL

Insert Customers SELECT i.CustomerID,
i.Name,
i.Address,
i.City,
i.State,
i.ZipCode,
i.Phone,
i.Email,
i.EFIN,
i.RegCode,
i.AccountNumber,
i.ChargeNumber,
i.ProductCode,
i.PurchaseDate,
i.PriorSoftware,
i.Bank,
i.BalanceDue,
i.Renewed
FROM inserted i
left outer join Customers c
on c.CustomerID = i.CustomerID
WHERE c.CustomerID is null
END

Its working by updating all customers with an efin, but it also makes all customers without an efin the same data, is there any way to only make this applicable to customers with an efin?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources