Delete statement with condition from joined table

[dolodolo]

Hi Guys,

I need help with the following statement. I am trying to delete records in one table (unitbut_amenity) based on a value in a joined table (unit):

delete from unitbut_amenity
inner join (select u.hproperty hproperty, u.hmy hunit from unit u) as main
on main.hunit = unitbut_amenity.hcode
where unitbut_amenity.notes1 = 'community-wide amenity' and main.hproperty = 983;

And getting the following errors:

Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'inner'.
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'as'.

Any help is appreciated.

Thanks

 

[baran121]

delete unitbut_amenity from unitbut_amenity
inner join (select u.hproperty hproperty, u.hmy hunit from unit u) as main
on main.hunit = unitbut_amenity.hcode
where unitbut_amenity.notes1 = 'community-wide amenity' and main.hproperty = 983;

 

[dolodolo]

Tahnks so much!