Update Where

• ringovski

  Hall of Fame

  Points: 3521

  More actions

  March 30, 2014 at 11:57 pm

  #306025

  Hi All,

  I have two tables users and usersproperties, users has all the users basic details and user properties hold there metadata. I'm to update one record in user properties based on username from users table.

  Table Users

  User_id (PK, int, not null)

  gen_username (nvarchar(50),not null)

  password (nchar(20), not null)

  gen_firstname (nvarchar(50),not null)

  gen_lastname (nvarchar(50),not null)

  Table Userproperties

  user_id (int,not null)

  gen_department (nvarchar(200),null)

  company_id(int, not null)

  so the one user_id, gen_username can be used across many different companies.

  update [userproperties]

  set [gen_department] = 'Non User'

  where USER_ID in(select USER_ID

  from users

  where gen_Username = 'PO' ) and company_id = 28

  The problem is the specifying the company_id it works OK without it, but will obviously update every department record for user 'PO' to 'non user'. When run it says 0 rows effected. Not sure how to get around this, thanks.

• Umer Akram

  SSC-Insane

  Points: 22209

  More actions

  March 31, 2014 at 12:16 am

  #1701469

  ringovski (3/30/2014)

  When run it says 0 rows effected.

  This means that you have no data with company_id = 28, for the User = 'PO'.