-- 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