1

I know similar questions has been before but I just couldn't quite understand them, still very new to SQL and wanting to get the solution as well as understanding why it wouldn't work originally.

sql = "UPDATE e " +
    "SET " +
    "e.Operator = '" + emp.Operator + "', " +
    "e.LoginName = '" + emp.Login + "', " +
    "e.Active = " + (emp.Active == true ? 1 : 0) + "," +
    "e.Position = '" + emp.Position + "', " +
    "p.Admin = " + (emp.Permission.Admin == true ? 1 : 0) + "," +
    "p.Manager = " + (emp.Permission.Manager == true ? 1 : 0) + "," +
    "p.Overtime = " + (emp.Permission.Overtime == true ? 1 : 0) + "," +
    "p.TimeInLieu = " + emp.Permission.TimeInLieu + " " +
    "FROM Employee e INNER JOIN Permissions p " +
    "ON e.PermissionID = p.PermissionID AND e.Operator = '" + employee + "'";

when trying to execute the command I get this error.

The multi-part identifier "p.Admin" could not be bound.

Any help would be greatly received.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
megabytes
  • 115
  • 2
  • 13
  • 2
    Why are you trying to update two tables at the same time? Your query will update the `Employee` table not the `Permissions` so you can't `set` any values `Permissions`. You can't update both in the same query, you will have to use two separate queries to update both tables. – Taryn Sep 06 '13 at 00:33
  • I don't understand why I can't - I could do it in access before I moved to SQLServer. Join both tables together as if they were 1 table then edit them at the same time. – megabytes Sep 06 '13 at 00:45
  • 2
    Access is sorcery. You can't update 2 tables in one update statement. However, you can do 2 updates in one transaction. Check out this question for reference: http://stackoverflow.com/questions/2044467/how-to-update-two-tables-in-one-statement-in-sql-server-2005 – supergrady Sep 06 '13 at 01:13
  • Thanks guys. I've done what you've suggested with the multiple transitions Supergrandy, works a treat. – megabytes Sep 06 '13 at 03:11

0 Answers0