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

Question

What statement can I use to INSERT INTO tableA a new row ONLY IF a specific column in tableB equals some value, where 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.

Answer 1

Hi,

If you can use TSQL (TransactSQL) use the IF sentence and put inside the INSERT INTO and DELETE statements.

Hope that helps,

Answer 2

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

Answer 3

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'

Answer 4

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.

Answer 5

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'

Answer 6

Here is how I solved it:

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