Microsoft SQL Server trigger updating a table row with change from another table columns

Question

I have two tables zipTbl and locationTBl with four common columns

zipCode
City
stateCode
salesTaxRate

If the zipCode changes in the locationTbl say from '91311' to '10172' I want the city, stateCode, salesTaxRate in the locationTBL to be updated with the same values in the zipTbl where the zipCode is the new value '10172'

zipCode is the primary key for the zipTbl and a foreign key in the locationTbl.

I use the following to create a trigger but it only updates from the first row in the zipTBL not the row with the new zipCode

CREATE TRIGGER trg_update_city_state on dbo.locationTbl
AFTER Update
AS 
BEGIN
     UPDATE locationTbl
     SET locationTbl.city = zipTbl.city,
         LocationTbl.stateCode = zipTbl.stateCode,
         locationTbl.salesTaxRate = zipTbl.salesTaxRate 
     FROM zipTbl
END

I have tried several combinations of adding "where locationTbl.zipCode = zipTbl.zipcode," but I keep getting syntax errors

Robert McKee · Accepted Answer · 2015-10-28 20:33:51Z

1

I think it's like this:

UPDATE locationTbl
SET locationTbl.city=zipTbl.city,
    LocationTbl.stateCode=zipTbl.stateCode,
    locationTbl.salesTaxRate=zipTbl.salesTaxRate
FROM locationTbl t1
JOIN inserted i
  ON t1.zipCode=i.zipCode
JOIN zipTbl
  ON i.zipCode=zipTbl.zipCode

answered Oct 28, 2015 at 20:33

Robert McKee

21.5k1 gold badge48 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

wogsland · Accepted Answer · 2015-10-28 21:37:22Z

0

Have you tried something like

CREATE TRIGGER trg_update_city_state on dbo.locationTbl AFTER Update 
AS 
BEGIN 
  UPDATE locationTbl 
  SET locationTbl.city = INSERTED.city, 
  LocationTbl.stateCode = INSERTED.stateCode, 
  locationTbl.salesTaxRate = INSERTED.salesTaxRate 
  FROM INSERTED
  WHERE locationTbl.zipCode = INSERTED.zipcode

edited Oct 28, 2015 at 21:37

answered Oct 28, 2015 at 20:32

wogsland

9,53121 gold badges63 silver badges99 bronze badges

2 Comments

Sean Lange Over a year ago

You need a join between locationTbl and INSERTED. As posted this doesn't work.

Pete Watters Chatsworth Over a year ago

It is the locationTbl where the zipCode is changed and I want to update the city, state, taxrate in the locationTbl from the zipCodeTbl

Collectives™ on Stack Overflow

Microsoft SQL Server trigger updating a table row with change from another table columns

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related