Help with update query

• McSQL

  SSCarpal Tunnel

  Points: 4936

  More actions

  November 28, 2008 at 9:47 am

  #75375

  Hey all,

  Should be a simple one, but it's bugging me I can;t get the correct syntax.

  Here's an overview of what I'm looking to do:

  I have 2 tables in the same database "CutoverTransfer" and "EmployeeMasterHoldingArea"

  I want to run an update on the EmployeeMasterHoldingArea table to set a field value (transferflag) to '1' where the field empcode on the same table matches an entry in the cutovertrabsfer table on a field of the same description called "Employee_ID"

  So I have tried to run:

  update employeemasterholdingarea

  set empcode = '1'

  where employeemasterholdingarea.empcode = cutovertransfer.employee_ID

  but obviously the table/alias has not been called at any point in the query to reference both tables.

  I have played about with a few case statements and select statements, after the set command, but haven;t had much success.

  Must be because it is that time on a Friday 🙂

  Any help would be greatly appreciated.

  Thanks

• CrazyMan

  SSChampion

  Points: 12560

  More actions

  November 28, 2008 at 9:57 am

  #904883

  update employeemasterholdingarea

  set empcode = '1'

  where employeemasterholdingarea.empcode = cutovertransfer.employee_ID

  Try

  Update a

  Set EmpCode=1

  From

  EmployeeMasterHoldingArea a

  Inner Join

  CutoverTransfer b On a.Empcode=b.Employee_Id

  Hope this is what you are looking for 🙂

• McSQL

  SSCarpal Tunnel

  Points: 4936

  More actions

  November 28, 2008 at 10:00 am

  #904885

  Apologies it should have read "set transferflag = '1' ", not empcode...

  But yeah, I think I should be able to figure it out from that.

  Thanks for your help 🙂

• CrazyMan

  SSChampion

  Points: 12560

  More actions

  November 28, 2008 at 10:08 am

  #904890

  np Ian 😀