Correct sql syntax for this Update Procedure

I have three tables:

LandingDetails with fields LandingId, ProductId and UnitPrice

LandingHeaders with fields LandingId and LandingDate

Products with fields ProductId and CostPrice

I wish to udate the LandingDetails UnitPrice from the costprice in Products based on the landingdate in landingHeaders falling between two dates.

On the first attempt I came up with the following, which might be true to the concept of the goal but obviously misses table joins.

    CREATE PROCEDURE dbo.UpdateAndPriceLandings
@StartDate DATE,
@EndDate  DATE
  AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

UPDATE LandingDetails
  SET UnitPrice = (SELECT p.CostPrice FROM Products p WHERE p.ProductId = LandingDetails.ProductId AND 
  dbo.LandingHeaders.LandingId = LandingId AND dbo.LandingHeaders.LandingDate1 BETWEEN @StartDate AND @EndDate)
GO

Version two as follows but I suspect that the joins are incorrect.

CREATE PROCEDURE dbo.UpdateAndPriceLandings
@StartDate DATE,
@EndDate  DATE
  AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

UPDATE dbo.LandingDetails
  SET UnitPrice = (SELECT p.CostPrice FROM     dbo.LandingDetails ld INNER JOIN
                         dbo.LandingHeaders lh ON ld.LandingId = lh.LandingId INNER JOIN
                         dbo.Products P ON ld.ProductId = p.ProductId
 WHERE 

  ld.LandingId = lh.LandingId AND lh.LandingDate BETWEEN @StartDate AND @EndDate)

GO

Can anyone point out what I suspect is a stupid syntactical error on my part?

Solution

Your query in the second SP should look like:

UPDATE
    dbo.LandingDetails
SET 
    UnitPrice=p.CostPrice
FROM
    dbo.LandingDetails ld
    INNER JOIN dbo.LandingHeaders lh ON 
        lh.LandingId=ld.LandingId
    INNER JOIN dbo.Products P ON 
        P.ProductId=ld.ProductId
 WHERE 
    lh.LandingDate BETWEEN @StartDate AND @EndDate