Database

23-May-08 3:56

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

<br />
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<br />

Any help would be appreciated

Ashfield23-May-08 4:01

23-May-08 4:01

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

Kevin Brydon23-May-08 4:10

23-May-08 4:10

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:

<br />
DELETE FROM Courses INNER JOIN Deliveries ON Courses.CourseID = Deliveries.CourseID WHERE Courses.CourseID = @CourseID

exactly the same error : "Incorrect syntax near 'INNER'"

Alsvha23-May-08 5:51

Alsvha

23-May-08 5:51

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

Kevin Brydon23-May-08 6:19

23-May-08 6:19

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!

updateing problem

asha_s23-May-08 2:31

23-May-08 2:31

hi every one

i have problem when i update my database my primry key is three column how can i write my sql statment
i am programing using access db and vb.net
when i write
sql="update [Section] set Cap=4 where Sectionid=123 and techid=123"
it give error and when i but sectionid only it also give error

Ashfield23-May-08 3:43

23-May-08 3:43

It would help if you said what error, but I'll hazard a guess that you are trying to create a duplicate key. Check that you don't already have a record where Cap=4 and Sectionid=123 and techid=123

Bob
Ashfield Consultants Ltd

asha_s23-May-08 4:17

23-May-08 4:17

yes, their will be duplicate because i can not but "and techid=123" in the sql it give error that it can not know what is after Sectionid=123 so i am asking about how can i write this

thanks

Kevin Brydon23-May-08 4:37

23-May-08 4:37

i think the real challenge is trying to figure out what your question is.

asha_s23-May-08 4:49

23-May-08 4:49

hi this is the error
Syntax error (missing operator) in query expression 'SectionID = 847 AND'.
and sectionid is number

Ashfield23-May-08 5:43

23-May-08 5:43

I suspect you have some charater datatypes, you need to encluse the values in quotes

sectionid = '123' etc

Bob
Ashfield Consultants Ltd

asha_s23-May-08 6:11

23-May-08 6:11

but sectionid of type number not text Confused | :confused:

Ashfield26-May-08 6:24

26-May-08 6:24

Post your table create script and then I'll see what I can do

Bob
Ashfield Consultants Ltd

asha_s26-May-08 7:21

26-May-08 7:21

thanks, but i found the solution

Ashfield26-May-08 21:11

26-May-08 21:11

asha_s wrote:
thanks, but i found the solution

which was?

Bob
Ashfield Consultants Ltd

Ashfield23-May-08 5:46