Delete rows from SQL Server with WHERE statement from different tables

Question

I need to delete some rows from a table, based on a mixed where statement from two tables.

I tried this:

delete from tblI t1, tblS t2 
where t2.rcode = 'ALA' and t1.sid > 5

but I get a syntax error. Please help me figure this out

Changed it to JOINS:

delete from tblI
inner join tblS
on tblI.sourceid = tblS.sourceid
where tblS.rcode = 'ALA' and tblI.sourceid > 5

but something is still wrong, please help.

First stop using implied syntax that is a very poor programming technique! In fact you havea cross join as it stands, so likely the query would have been bad even if it worked. — HLGEM
– HLGEM, Commented Aug 12, 2011 at 15:09
by "implied syntax" I believe he means the implicit join syntax, i.e. comma-separated list of tables instead of JOIN ... ON or JOIN ... USING. — Hammerite
– Hammerite, Commented Aug 12, 2011 at 15:11
@HLGEM - with a name that is an acronym, an avatar of swirly colors, and no info in your profile we have no way of knowing if you are a lady — JNK
– JNK, Commented Aug 12, 2011 at 15:14

HLGEM · Accepted Answer · 2011-08-12 15:12:14Z

24

You have to tell it which table to delete from.

delete t1
from tblI t1 
join tblS t2  on t1.sid = t2.sid
where t2.rcode = 'ALA' 
and  t1.sid > 5

answered Aug 12, 2011 at 15:12

HLGEM

97k15 gold badges120 silver badges191 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1