locked
Update multiple rows with 2 inner joins RRS feed

  • Question

  • User-507786106 posted

    How do I update multiple rows with 2 inner joins?

    SELECT  Emp.ID , Emp.field2, TableB.field3, TableC.NOTES FROM  Employee as Emp

    INNER JOIN TableB ON Emp.ID =TableB.NewTechID

    INNER JOIN TableC on TableC.ComputerID =  TableB.NewTechID

    The above states returns 40 rows each must be updated but ONLY the 40 rows

    How do I create an update statement that uses both inner joins to update  TableC.NOTES = "*** what you say ****"

    Tuesday, September 18, 2018 7:52 PM

Answers

  • User77042963 posted

    Merge TableC tgt
    Using (
    SELECT  Emp.ID , Emp.field2, TableB.field3, TableB.NewTechID 
    FROM  Employee as Emp INNER JOIN TableB ON Emp.ID =TableB.NewTechID
    ) src on tgt.ComputerID=src.NewTechID
    WHEN matched then
    Update 
    Set NOTES='*** what you say ****';
     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 18, 2018 7:57 PM
  • User-1716253493 posted

    In query designer is very simple, simply change type from SELECT to UPDATE then set tablec.notes new value

    AFAIK SImpy change from SELECT .... to Update TableC set Notes = 'newvalue' or 

    Update TableC set Notes = 'newvalue' ...
    Update TableC set Notes = TableB.Fieldname

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 19, 2018 1:24 AM

All replies

  • User77042963 posted

    Merge TableC tgt
    Using (
    SELECT  Emp.ID , Emp.field2, TableB.field3, TableB.NewTechID 
    FROM  Employee as Emp INNER JOIN TableB ON Emp.ID =TableB.NewTechID
    ) src on tgt.ComputerID=src.NewTechID
    WHEN matched then
    Update 
    Set NOTES='*** what you say ****';
     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 18, 2018 7:57 PM
  • User-507786106 posted

    Thank you for this example but it did not work; let me clean up and try again.

    Tuesday, September 18, 2018 11:49 PM
  • User-1716253493 posted

    In query designer is very simple, simply change type from SELECT to UPDATE then set tablec.notes new value

    AFAIK SImpy change from SELECT .... to Update TableC set Notes = 'newvalue' or 

    Update TableC set Notes = 'newvalue' ...
    Update TableC set Notes = TableB.Fieldname

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 19, 2018 1:24 AM