Djabarov, Robert
Robert.Djabarov at usaa.com
Thu Apr 1 10:11:14 CST 2004
UPDATE jc set Selected = 1 >From tblPersonnelJobCategories jc INNER JOIN tblPersonnelCategoriesSelected cs ON (jc.CategoryID = cs.CategoryID AND jc.PayrollNo = cs.PayrollNo) WHERE jc.UserID= @UserId And Sander, INNER/LEFT OUTER/RIGHT JOINs are the preferred method of joining tables. This way you make it clear what you join vs. what you filter out in your WHERE clause, which is what the WHERE clause is for. Robert Djabarov SQL Server & UDB Sr. SQL Server Administrator Phone: (210) 913-3148 Pager: (210) 753-3148 9800 Fredericksburg Rd. San Antonio, TX 78288 www.usaa.com -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of paul.hartland at fsmail.net Sent: Thursday, April 01, 2004 7:27 AM To: dba-sqlserver at databaseadvisors.com Subject: Re: Re: [dba-SQLServer] Stored Procedure Help (SQL Server & VB6) Thanks for the response, I ended up cheating. I created a view to select the records I needed to update then use an SP to update them..... Paul Message date : Apr 01 2004, 01:38 PM >From : "S D" To : dba-sqlserver at databaseadvisors.com Copy to : Subject : Re: [dba-SQLServer] Stored Procedure Help (SQL Server & VB6) Hi paul, nice to see you here also. Personally I do NOT use the INNER JOIN stuff. I just use plain SQL SELECT a.x, b.y FROM table1 a, table2 b WHERE a.a = b.a AND .... That would result in: UPDATE tblPersonnelJobCategories SET tblPersonnelJobCategories.Selected = True WHERE tblPersonnelJobCategories.UserID= @UserId AND tblPersonnelJobCategories.CategoryID = tblPersonnelCategoriesSelected.CategoryID AND tblPersonnelJobCategories.PayrollNo = tblPersonnelCategoriesSelected.PayrollNo A tip for you're query: does it run when you hardcode the UserID? Did you try to run: SELECT tblPersonnelJobCategories.UserID, tblPersonnelJobCategories.Selected FROM tblPersonnelJobCategories, tblPersonnelCategoriesSelected WHERE tblPersonnelJobCategories.UserID= @UserId AND tblPersonnelJobCategories.CategoryID = tblPersonnelCategoriesSelected.CategoryID AND tblPersonnelJobCategories.PayrollNo = tblPersonnelCategoriesSelected.PayrollNo Success. Sander paul.hartland at fsmail.net wrote: To all, I have a Visual Basic 6 front-end together with a SQL Server 7.0 back-end, and have tried writing a stored procedure to do the following: I pass a UserID into the SP, then I want the SP to UPDATE tblPersonnelJobCategories to TRUE where tblPersonnelJobCategories.UserID is equal to the UserID that I passed to the SP but I only want the UPDATE to take place where the PayrollNo & CategoryID in tblPersonnelJobCategories matches the PayrollNo & CategoryID in tblPersonnelCategoriesSelected I tried using the follwing SQL statement in my SP but it doesn't like the Inner Join: UPDATE tblPersonnelJobCategories INNER JOIN tblPersonnelCategoriesSelected ON (tblPersonnelJobCategories.CategoryID = tblPersonnelCategoriesSelected.CategoryID) AND (tblPersonnelJobCategories.PayrollNo = tblPersonnelCategoriesSelected.PayrollNo) SET tblPersonnelJobCategories.Selected = True WHERE tblPersonnelJobCategories.UserID= @UserId Any help would be greatly appreciated, as I'm fairly new to SP's and thought an INNER JOIN would be ok. Thanks in advance for any help Freeserve AnyTime - HALF PRICE for the first 3 months - Save £7.50 a month www.freeserve.com/anytime _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com Do you Yahoo!? Yahoo! Mail - More reliable, more storage, less spam _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com Freeserve AnyTime - HALF PRICE for the first 3 months - Save £7.50 a month www.freeserve.com/anytime _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com