Update Particular Column in SQL

Question

I have four tables (simplified version)

Clients:

    ClientID
    ManagerID
    UserID
    VolunteerID

Managers

    ManagerID
    TeamID

Users

  UserID

Teams

  TeamID

I need to to update the user ID (Client.UserID) on a particular Client for a particular Team

I have come up with the following but it doesnt seem to be working - the user ID on the client is not being altered:

    UPDATE C
    SET C.UserID = 28 
    FROM vTeams T 
    INNER JOIN Vmanagers M ON T.TeamID = M.TeamID
    INNER JOIN dbo.vClients C ON M.ManagerID = C.ManagerID
    INNER JOIN vUsers U ON C.UserID = U.UserID
    WHERE 
    T.TeamID = 251

TIA

Have you tried Selecting data from these joins and where clause? does it actually return any data? because the update statement looks fine to me. — M.Ali, Jun 07 '14 at 15:38
Ok sorry for this. I did have a select statement but overlooked a changed TeamID on the select (291) - doh!!! — amun1000, Jun 07 '14 at 15:42

score 0 · Answer 1 · answered Jun 07 '14 at 15:42

EDIT: this answer applies to MySQL but I don't think it works for SQL Server...sorry..I'll leave it though in case it's helpful to anyone

I haven't tested this, but there's no FROM in an UPDATE clause, and the SET comes after the JOINs. Try something like this:

UPDATE dbo.vClients C
    INNER JOIN Vmanagers M ON M.ManagerID = C.ManagerID
    INNER JOIN Team T ON T.TeamID = M.TeamID
#    INNER JOIN vUsers U ON C.UserID = U.UserID
# not sure if you'll need the above join, based on the example
    SET C.UserID = 28 
    WHERE 
    T.TeamID = 251

The Update Query specified by you will work in **PL/SQL** only it will throw up error in **SQL SERVER(T-SQL)** — Rajesh, Jun 07 '14 at 19:06

score 0 · Answer 2 · answered Jun 07 '14 at 15:43

0

My mistake a typo on the select to check the update

answered Jun 07 '14 at 15:43

amun1000

453
1
8
18

score 0 · Answer 3 · edited May 23 '17 at 12:20

If your column is an identity table try to set it off then run your script then return it on like:

SET IDENTITY_INSERT C ON
    UPDATE C
SET C.UserID = 28 
FROM vTeams T 
INNER JOIN Vmanagers M ON T.TeamID = M.TeamID
INNER JOIN dbo.vClients C ON M.ManagerID = C.ManagerID
INNER JOIN vUsers U ON C.UserID = U.UserID
WHERE 
T.TeamID = 251

SET IDENTITY_INSERT C OFF

OR you can do multiple where statement separated by AND like:

UPDATE C
SET C.UserID = 28 
WHERE
(M.TeamID =251
AND
C.ManagerID = 251
AND
U.UserID = 251
AND
T.TeamID = 251)

OR this may help you: SQL update query syntax with inner join

Update Particular Column in SQL

3 Answers3