UPDATE JOIN problem
-
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=@CourseIDAny help would be appreciated -
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=@CourseIDAny help would be appreciated -
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
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 = @CourseIDexactly the same error : "Incorrect syntax near 'INNER'" -
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=@CourseIDAny help would be appreciatedYou 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=@CourseIDAnd 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
-
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=@CourseIDAnd 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
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!