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
– M.Ali, Commented Jun 7, 2014 at 15:38
Ok sorry for this. I did have a select statement but overlooked a changed TeamID on the select (291) - doh!!! — amun1000
– amun1000, Commented Jun 7, 2014 at 15:42

billrichards · Accepted Answer · 2014-06-07 15:42:50Z

0

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

answered Jun 7, 2014 at 15:42

billrichards

2,0804 gold badges25 silver badges37 bronze badges

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

1 Comment

Rajesh Over a year ago

The Update Query specified by you will work in PL/SQL only it will throw up error in SQL SERVER(T-SQL)

amun1000 · Accepted Answer · 2014-06-07 15:43:09Z

0

My mistake a typo on the select to check the update

answered Jun 7, 2014 at 15:43

amun1000

5731 gold badge8 silver badges18 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:20:57Z

0

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

edited May 23, 2017 at 12:20

CommunityBot

11 silver badge

answered Jun 9, 2014 at 7:26

Abdulrahman_88

5601 gold badge5 silver badges16 bronze badges

Collectives™ on Stack Overflow

Update Particular Column in SQL

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related