Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • World
  • Users
  • Groups
Skins
  • Light
  • Cerulean
  • Cosmo
  • Flatly
  • Journal
  • Litera
  • Lumen
  • Lux
  • Materia
  • Minty
  • Morph
  • Pulse
  • Sandstone
  • Simplex
  • Sketchy
  • Spacelab
  • United
  • Yeti
  • Zephyr
  • Dark
  • Cyborg
  • Darkly
  • Quartz
  • Slate
  • Solar
  • Superhero
  • Vapor

  • Default (No Skin)
  • No Skin
Collapse
Code Project
  1. Home
  2. Database & SysAdmin
  3. Database
  4. UPDATE JOIN problem

UPDATE JOIN problem

Scheduled Pinned Locked Moved Database
helpdatabasesql-serversysadminquestion
5 Posts 3 Posters 0 Views 1 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • K Offline
    K Offline
    Kevin Brydon
    wrote on last edited by
    #1

    Hey. I understand that in sql server 2005 you can write statements to update several tables at once using join. Is that correct? I'm trying to this but keep getting an "Incorrect syntax near 'INNER'" error. My SQL is UPDATE Courses INNER JOIN Deliveries ON Courses.CourseID = Deliveries.CourseID SET Courses.IsDeleted=1, Deliveries.IsDeleted=1, Courses.DeletedOn=@DeletedOn, Deliveries.DeletedOn=@DeletedOn WHERE Courses.CourseID=@CourseID Any help would be appreciated

    A A 2 Replies Last reply
    0
    • K Kevin Brydon

      Hey. I understand that in sql server 2005 you can write statements to update several tables at once using join. Is that correct? I'm trying to this but keep getting an "Incorrect syntax near 'INNER'" error. My SQL is UPDATE Courses INNER JOIN Deliveries ON Courses.CourseID = Deliveries.CourseID SET Courses.IsDeleted=1, Deliveries.IsDeleted=1, Courses.DeletedOn=@DeletedOn, Deliveries.DeletedOn=@DeletedOn WHERE Courses.CourseID=@CourseID Any help would be appreciated

      A Offline
      A Offline
      Ashfield
      wrote on last edited by
      #2

      I've never seen this done, as far as I know you can only update one table in an update statment. Your error message suggests that it won't work either.

      Bob Ashfield Consultants Ltd

      K 1 Reply Last reply
      0
      • A Ashfield

        I've never seen this done, as far as I know you can only update one table in an update statment. Your error message suggests that it won't work either.

        Bob Ashfield Consultants Ltd

        K Offline
        K Offline
        Kevin Brydon
        wrote on last edited by
        #3

        Ok. It would be nice if you could do it though. how about DELETE JOIN? I'm sure you can do that forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2541925&SiteID=1 (its a little slow today) But for some reason my sql isnt working for that either. ive tried numerous combinations with this being the most sensible i think: DELETE FROM Courses INNER JOIN Deliveries ON Courses.CourseID = Deliveries.CourseID WHERE Courses.CourseID = @CourseID exactly the same error : "Incorrect syntax near 'INNER'"

        1 Reply Last reply
        0
        • K Kevin Brydon

          Hey. I understand that in sql server 2005 you can write statements to update several tables at once using join. Is that correct? I'm trying to this but keep getting an "Incorrect syntax near 'INNER'" error. My SQL is UPDATE Courses INNER JOIN Deliveries ON Courses.CourseID = Deliveries.CourseID SET Courses.IsDeleted=1, Deliveries.IsDeleted=1, Courses.DeletedOn=@DeletedOn, Deliveries.DeletedOn=@DeletedOn WHERE Courses.CourseID=@CourseID Any help would be appreciated

          A Offline
          A Offline
          Alsvha
          wrote on last edited by
          #4

          You can - as far as I know - only update one base table at the time. You can however use INNER JOINS for updateable queries - but still only when they affect one table such . The problem in your syntax is not that you try to update two tables at once, but that you have the wrong syntax for updating using a join. The easy way would be something like:

          UPDATE Courses
          SET Courses.IsDeleted=1, Courses.DeletedOn=@DeletedOn
          FROM Cources
          INNER JOIN Deliveries ON Courses.CourseID = Deliveries.CourseID
          WHERE Courses.CourseID=@CourseID

          And then run it a second time for updates in the Deliveries table. If you even need to use a JOIN for your update when you can't do both tables at once, of course.

          --------------------------- Blogging about SQL, Technology and many other things

          K 1 Reply Last reply
          0
          • A Alsvha

            You can - as far as I know - only update one base table at the time. You can however use INNER JOINS for updateable queries - but still only when they affect one table such . The problem in your syntax is not that you try to update two tables at once, but that you have the wrong syntax for updating using a join. The easy way would be something like:

            UPDATE Courses
            SET Courses.IsDeleted=1, Courses.DeletedOn=@DeletedOn
            FROM Cources
            INNER JOIN Deliveries ON Courses.CourseID = Deliveries.CourseID
            WHERE Courses.CourseID=@CourseID

            And then run it a second time for updates in the Deliveries table. If you even need to use a JOIN for your update when you can't do both tables at once, of course.

            --------------------------- Blogging about SQL, Technology and many other things

            K Offline
            K Offline
            Kevin Brydon
            wrote on last edited by
            #5

            Thanks for your advice. At the moment my code is pretty inefficient so i can see how the update join may speed things up and make my life a little easier!

            1 Reply Last reply
            0
            Reply
            • Reply as topic
            Log in to reply
            • Oldest to Newest
            • Newest to Oldest
            • Most Votes


            • Login

            • Don't have an account? Register

            • Login or register to search.
            • First post
              Last post
            0
            • Categories
            • Recent
            • Tags
            • Popular
            • World
            • Users
            • Groups