How to Update a column in Table from the results of joining 2 other tables

• gary.singleton-906905

  Mr or Mrs. 500

  Points: 527

  More actions

  October 10, 2008 at 10:56 am

  #103600

  Sorry new to sql and dropped in at the deep end.

  Hi I need to update a column in a table from the results of joining 2 other tables.

  basically i have

  Table 1

  StaffID, LogonStaffNo

  OtherDatabase.Table 2

  RowID,StaffID

  OtherDatabase.Table 3

  SSo_RowID,LogonStaffNo

  Table2.Rowid and Table3.SSoRowId are foreign keys

  I need to update Table1 LogonStaffNo with Table3 LogonStaffNo where Table1 StaffID = Table2 StaffID

  Phew Sorry if it doesn't make sense

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  October 10, 2008 at 11:09 am

  #883559

  Give this a try in a test environment first:

  update dbo.table1 set

  LogonStaffNo = t3.LogonStaffNo

  from

  dbo.table1 t1

  inner join dbo.table2 t2

  on (t1.StaffID = t2.StaffID)

  inner join dbo.table3 t3

  on (t2.RowID = t3.SSo_RowID);

  😎

• Lowell

  SSC Guru

  Points: 323518

  More actions

  October 10, 2008 at 11:13 am

  #883563

  abstract examples are harder to help wiht, but here's an example:

  --does this return data?

  SELECT

  OtherDatabaseT2.RowID,

  OtherDatabaseT2.StaffID,

  OtherDatabaseT3.SSo_RowID,

  OtherDatabaseT3.LogonStaffNo

  FROM Otherdatabase.dbo.[Table 2] OtherDatabaseT2

  INNER JOIN Otherdatabase.dbo.[Table 3] OtherDatabaseT3

  ON OtherDatabaseT2.RowID = OtherDatabaseT3.SSo_RowID

  UPDATE [Table 1]

  SET [Table 1].LogonStaffNo = SubSelectAlias.LogonStaffNo

  FROM (

  SELECT

  OtherDatabaseT2.RowID,

  OtherDatabaseT2.StaffID,

  OtherDatabaseT3.SSo_RowID,

  OtherDatabaseT3.LogonStaffNo

  FROM Otherdatabase.dbo.[Table 2] OtherDatabaseT2

  INNER JOIN Otherdatabase.dbo.[Table 3] OtherDatabaseT3

  ON OtherDatabaseT2.RowID = OtherDatabaseT3.SSo_RowID

  ) SubSelectAlias

  WHERE [Table 1].StaffId = SubSelectAlias.StaffID

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• gary.singleton-906905

  Mr or Mrs. 500

  Points: 527

  More actions

  October 10, 2008 at 11:43 am

  #883573

  Hi That worked great,

  I forgot that i need to also put where Table1.AccRv='RM'

  Table1.AccRv this is another column in the table that i left out of the original code.

  Can this be added