Personal tools
You are here: Home SQL Server How To's How To Update Data In One Table Related To Another Table On SQL Server
Navigation
Log in


Forgot your password?
 
Document Actions

How To Update Data In One Table Related To Another Table On SQL Server

This tip describes how to update data in one table with data related to another table. It's not uncommon to perform this since the RDBMS link all the tables via primary keys and foreign keys

-- First, Let's Create The Tables

CREATE TABLE Customers (CustID INT NOT NULL, CustName VARCHAR(50))
CREATE TABLE Orders (OrderID INT NOT NULL, CustID INT, TotalOrder SMALLMONEY)

-- Next Step is Create The Constraints To Link The Tables

ALTER TABLE Customers ADD CONSTRAINT CustID PRIMARY KEY (CustID)
ALTER TABLE Orders ADD CONSTRAINT OrderID PRIMARY KEY (OrderID)
ALTER TABLE Orders ADD CONSTRAINT FK_Customers_Orders FOREIGN KEY
 (CustID) REFERENCES Customers (CustID)

-- And Then We populate the tables

INSERT INTO Customers VALUES (1, 'Ken')
INSERT INTO Customers VALUES (2, 'Kate')
INSERT INTO Customers VALUES (3, 'Lionel')
INSERT INTO Orders VALUES (1, 1, 40.00)
INSERT INTO Orders VALUES (2, 1, 30.00)
INSERT INTO Orders VALUES (3, 2, 10.00)
INSERT INTO Orders VALUES (4, 2, 40.00)
INSERT INTO Orders VALUES (5, 2, 80.00)

-- Let's say that We need to decrease 25% of the Order Total Column for Customer Kate
-- We Can use a subquery to perform this. It will something like that

UPDATE Orders SET TotalOrder = TotalOrder * 0.75
WHERE CustID = (SELECT CustID FROM Customers WHERE CustName = 'Kate')

-- Another option (more elegant and performatic is to use a JOIN)
-- This works fine, but remember that was not ANSI Compliance

UPDATE Orders SET TotalOrder = TotalOrder * 0.75
FROM Orders
INNER JOIN Customers ON Orders.CustID = Customers.CustID
WHERE Customers.CustName = 'Kate'

-- You can check the results (try ony option at time)

SELECT OrderID, CustID, TotalOrder FROM Orders
WHERE CustID = 2

-- Don't Forget To Delete Tables

DROP TABLE Orders
DROP TABLE Customers
Security Awareness
Would you like your company to implement gamification into your security awareness program?





Polls