SQL Update on multiple tables
Posted on 2006-03-21
I have and asp.net application where I have a gridview that displays employee info based on a user search. It pulls the info from 2 seperate tables using a join. I have the edit button enabled and I'm trying to take the info entered from the edit boxes and pass them back to the SQL DB.
Here's what I've got:
SELECT dbo.Users.FullName, dbo.Positions.Phone, dbo.Users.[e-mail], dbo.Positions.Title, dbo.Users.ID, dbo.Positions.ID FROM dbo.Users
INNER JOIN dbo.Positions ON dbo.Users.ID = dbo.Positions.ID
WHERE (dbo.Users.FullName LIKE '%' + @search + '%') OR (dbo.Positions.Phone LIKE '%' + @search + '%') OR (dbo.Users.[e-mail] LIKE '%' + @search + '%') OR (dbo.Positions.Title LIKE '%' + @search + '%')
Then I need somthing like:
UPDATE dbo.Users SET FullName = @FullName WHERE (ID = @selected) but I need to perform an update on each of the fields from the select statement which are in 2 seperate tables - dbo.users and dbo.positions
Is it possible to do that in one statement? I've read some articles online that suggest using a trigger but I'm using parameters and my understanding is you can't perform a trigger with a parameter.
Any help is appreciated