help with update using join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

help with update using join

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2003-09-08 : 17:56:53

For some odd reason I have never come across this before I dont think. Due to the database designs I have been working with being fairly simple.

I am trying to do an update statement, but one of the WHERE clauses requires a join. Here is what I have so far

Any help is greatly appreciated.

Thanks alot

Mike123

CREATE PROCEDURE [update_tblProducts_1]
(
@productID [int],
@userID [int],
@description [varchar],
@cost [money],
@durationDays [smallint]
)

AS SET NOCOUNT ON

UPDATE [tblProducts]

'not sure about this line
JOIN tblZones on tblZones.zoneID = tblProducts.zoneID

SET [description] = @description,
[cost] = @cost,
[durationDays] = @durationDays

WHERE
(
[tblZones.userID] = @userID AND [productID] = @productID
)
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-08 : 17:59:23

[code]

UPDATE tblProducts
SET [description] = @description,
cost = @cost,
durationDays = @durationDays
FROM tblProducts p
JOIN tblZones z on p.zoneID = z.zoneID
WHERE z.userID = @userID AND [productID] = @productID
GO
[/code]

Tara

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2003-09-08 : 18:16:16

thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources