September 19, 2014 at 9:39 am
Hi all. I'm having trouble with a reference to a CTE not working in an update statement.
here are my basic tables and some basic data.
CREATE TABLE Clients
(
ClientRef INT,
ClientName VARCHAR(50),
BranchRef INT,
PRIMARY KEY (ClientRef)
);
CREATE TABLE ClientExtra
(
ClientRef INT,
FormerBranch VARCHAR(100) NOT NULL,
PRIMARY KEY (ClientRef)
);
CREATE TABLE Entities
(
EntityRef INT,
EntityName VARCHAR(50),
Type INT
PRIMARY KEY (EntityRef)
);
INSERT INTO Clients (ClientRef, ClientName, BranchRef) VALUES (1000001, 'Mr Bloggs', 1000003);
INSERT INTO Clients (ClientRef, ClientName, BranchRef) VALUES (1000002, 'Mr Jones', 1000003);
INSERT INTO ClientExtra (ClientRef, FormerBranch) VALUES (1000001, 'Testing');
INSERT INTO ClientExtra (ClientRef, FormerBranch) VALUES (1000002, 'Test');
INSERT INTO Entities (EntityRef, EntityName, Type) VALUES (1000001, 'Consultant', 1);
INSERT INTO Entities (EntityRef, EntityName, Type) VALUES (1000002, 'Supplier', 2);
INSERT INTO Entities (EntityRef, EntityName, Type) VALUES (1000003, 'Branch', 3);
INSERT INTO Entities (EntityRef, EntityName, Type) VALUES (1000004, 'New Branch', 3);
The BranchRef in the Clients table, is the EntityRef from the Entities table, where the "Type" is 3. I wish to update the "FormerBranch" column in the "ClientExtra" table, with the name of the "Entity" the clients currently have set as the BranchRef. The code I have is:
WITH ClientBranch AS
(
SELECT Entities.EntityName AS [EntityName], Clients.BranchRef AS [BranchRef]
FROM Clients LEFT JOIN Entities ON Clients.BranchRef = Entities.EntityRef AND Entities.Type = 3
)
UPDATE ClientExtra SET FormerBranch = CASE WHEN ClientBranch.EntityName IS NULL THEN ' ' ELSE ClientBranch.EntityName
I'm just getting a load of "Could not be bound" items in the SQL, which I don't understand. I've used this exact same syntax on SELECT and DELETE statements before and it works.
If I run the below all is well.
WITH ClientBranch AS
(
SELECT Entities.EntityName AS [EntityName], Clients.BranchRef AS [BranchRef]
FROM Clients LEFT JOIN Entities ON Clients.BranchRef = Entities.EntityRef AND Entities.Type = 3
)
SELECT * FROM ClientBranch
Is there something I'm doing wrong?
Regards
Steve
September 19, 2014 at 10:02 am
You're not referencing your CTE on your UPDATE statement. Additionally, you don't have anything to make a relationship (and that's caused by the previous reason).
You don't actually need a CTE, something as simple as a JOIN should work.
UPDATE ce SET
FormerBranch = CASE WHEN e.EntityName IS NULL
THEN ' '
ELSE e.EntityName
END
FROM ClientExtra ce
JOIN Clients c ON ce.ClientRef = c.ClientRef
LEFT
JOIN Entities e ON c.BranchRef = e.EntityRef AND e.Type = 3
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply