Using Inner Join In Insert To Verify Staus in Other Table

[Auguy]

I have done something like the following for an update. These examples re simplified.

UPDATE R 
SET R.status = '0' 
FROM dbo.ProductReviews AS R
INNER JOIN dbo.products AS P 
       ON R.pid = P.id 
WHERE R.id = '17190' 
  AND P.IsDiscontinued = 0;

But I'm confused as to how do this in an insert where I want to make sure the product is not discontinued just like the update. What am I missing here?

Insert Into R 
SET R.status = @Status, ReviewDate = @ReviewDate, etc.
FROM dbo.ProductReviews AS R
INNER JOIN dbo.products AS P 
       ON R.pid = P.id 
WHERE R.id = '17190' 
  AND P.IsDiscontinued = 0;

Auguy
Sylvania/Toledo Ohio

 

[gmmastros]

Insert Into R (Status, ReviewDate, etc...)
Select R.status, ReviewDate, etc.
FROM dbo.ProductReviews AS R
INNER JOIN dbo.products AS P 
       ON R.pid = P.id 
WHERE R.id = '17190' 
  AND P.IsDiscontinued = 0;

Make sure the list of columns on the first line (inside the parenthesis) matches the list fo columns in your select statement on the next line.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Auguy]

Sorry, still confused. I'm not selecting anything from R, all of the values I want to insert are parameters that will be passed to the stored procedure. One of these parameters will be R.id. I just want to make sure the product is not discontinued in the products table for the review that is being inserted. I don't think I need the FROM clause, but not sure how to make this work. Hope I'm making it more clear want I'm trying to achieve. Here is some pseudo code.

Insert Into Table A.Col1 = @Col1, Table A.Col2 = @Col2 Where Table B.Col2 = @Col2 AND Table B.SomeColumn = 0

Auguy
Sylvania/Toledo Ohio

 

[Auguy]

OK, It seems as though I can put this code before the my regular insert statement to make it work.

IF EXISTS (SELECT P.IsDiscontinued FROM dbo.products P Where P.IsDiscontinued = 0
[indent]My Regular Insert Statement Here[/indent]

Not sure if this is the best way, but it seems to work.

Auguy
Sylvania/Toledo Ohio