ginuta ginuta - 1 month ago 12
SQL Question

.Net SQL updateScript

I am new to .net programming and I have a question.

I need to update table Route based on VehicleID.
The point is I don`t know how to make this query work.
This is what I have tried so far:

CREATE PROCEDURE UpdateRouteWithVendorIdAndVehicleTypeId
AS
(
SELECT
vh.VendorId,
vh.VehicleTypeId,
r.Id
FROM Vehicle vh WITH (NOLOCK)
INNER JOIN [dbo].Route r WITH (NOLOCK) ON r.VehicleId = vh.Id
)
UPDATE Route
SET VehicleTypeId = vh.VehicleTypeId, VendorId = vh.VendorId
WHERE Id = r.Id


How to make this part "
SET VehicleTypeId = vh.VehicleTypeId, VendorId = vh.VendorId
" to be seen outside the first query?

Thank you in advance.

Answer

Use the below script

 CREATE PROCEDURE UpdateRouteWithVendorIdAndVehicleTypeId
  AS
  BEGIN
   UPDATE r
  SET  r.VehicleTypeId = vh.VehicleTypeId
      , r.VendorId = vh.VendorId
  FROM Vehicle vh WITH (NOLOCK)
  INNER JOIN [dbo].Route r WITH (NOLOCK) ON r.VehicleId = vh.Id
  END