INSERT INTO and DELETE... IF/WHERE a column in different table

What statement can I use to INSERT INTO tableA a new row ONLY IF a specific column in tableB equals some value, where 开发者_JAVA百科tableB contains "itemid" and tableA includes "itemid" as well. I thought of INNER JOIN but is that possible somehow?

I also need exactly the same approach for DELETE.

It sounds like what you want is this:

insert into items(projectid,description)
select P.ProjectId, '@descriptionval'
from Projects P
where P.Active = 1 and P.ProjectID = '@projectidval'

If I understand your schema correctly, you can't do an INNER JOIN, because tableA doesn't have the matching row yet.

Now for the delete, you do have both row now, so you will do the join:

DELETE FROM Items I 
inner join Projects P on I.ProjectId = P.ProjectId 
where P.Active = 1 and I.ProjectID = @ProjId

UPDATED based on OP's comment to question and his own answer. This should allows a 1 to many releationship between Projects & Items.

INSERT INTO table (col1, col2, col3,col4)

SELECT table2.col1, ect FROM
table1 table INNER JOIN table2 table2 ON table.col1= table2.col WHERE table2.value=> 'something'

INSERT INTO tableA (col1, col2)
SELECT col1, col2
FROM tableA a INNER JOIN tableB b ON a.itemid= b.itemid
AND b.somevaluecol = 'somevalue'

DELETE FROM tableA a
INNER JOIN tableB b ON a.ItemId = b.ItemId
WHERE b.somevaluecol = 'somevalue'

if exists(select * from TableA,TableB where TableA.itemID = TableB.itemID and TableB.Col = somevalue)
begin
    insert into ....
end 

Do the same for delete but change the insert statement to a delete statement

Here is how I solved it:

IF (SELECT active FROM projects WHERE projectid='@projectidval')<>1 INSERT INTO items (projectid,description) VALUES ('@projectidval','@descriptionval')