So this select query displays all the entries I want to see:

select *
FROM [crv].[dbo].[CRV_UsageLog]
	INNER JOIN crv.dbo.CRV_Rooms
	on CRV_UsageLog.RoomID=CRV_Rooms.RoomID
	Inner JOIN crv.dbo.CRV_TreeRoomMap
	on CRV_UsageLog.RoomID=CRV_TreeRoomMap.RoomID
where CRV_TreeRoomMap.TreeNodeID='dec8d04f-b01e-4c26-be8f-3e67f74414d6'
and CRV_UsageLog.Data2='Button Press'

delete throwawayVariable
FROM [crv].[dbo].[CRV_UsageLog] throwawayVariable
	INNER JOIN crv.dbo.CRV_Rooms
	on CRV_UsageLog.RoomID=CRV_Rooms.RoomID
	Inner JOIN crv.dbo.CRV_TreeRoomMap
	on CRV_UsageLog.RoomID=CRV_TreeRoomMap.RoomID
where CRV_TreeRoomMap.TreeNodeID='dec8d04f-b01e-4c26-be8f-3e67f74414d6'
and CRV_UsageLog.Data2='Button Press'

11/26/2012 3:41:29 PM

That should be fine. "Throwawayvariable" isn't really a throw away variable; it's a table alias. You're telling the query what side of the join you want the records deleted from.

(Edit: I'm not guaranteeing that your criteria syntax is correct and that you're not going to delete records you're not intending. use at your own risk. )

[Edited on November 26, 2012 at 3:47 PM. Reason : .]

11/26/2012 3:46:01 PM

I would try something like this:

delete
from usagelog
where roomid in (
select usagelog.roomid
FROM [crv].[dbo].[CRV_UsageLog]
INNER JOIN crv.dbo.CRV_Rooms
on CRV_UsageLog.RoomID=CRV_Rooms.RoomID
Inner JOIN crv.dbo.CRV_TreeRoomMap
on CRV_UsageLog.RoomID=CRV_TreeRoomMap.RoomID
where CRV_TreeRoomMap.TreeNodeID='dec8d04f-b01e-4c26-be8f-3e67f74414d6'
and CRV_UsageLog.Data2='Button Press')


[Edited on November 26, 2012 at 3:53 PM. Reason : assuming you're trying to delete rows based on roomid, use at own risk ]

11/26/2012 3:46:36 PM

^^ I get this error, fyi:

Msg 4104, Level 16, State 1, Line 4
The multi-part identifier "CRV_UsageLog.RoomID" could not be bound.
Msg 4104, Level 16, State 1, Line 6
The multi-part identifier "CRV_UsageLog.RoomID" could not be bound.
Msg 4104, Level 16, State 1, Line 8
The multi-part identifier "CRV_UsageLog.Data2" could not be bound.

11/26/2012 3:51:26 PM

You're aliasing that table as "Throwawayvariable" in that query, try referencing them as such in the criteria as well.

11/26/2012 3:53:51 PM

yeah i just figured out...

I got this error now:

Msg 9002, Level 17, State 2, Line 1
The transaction log for database 'crv' is full due to 'LOG_BACKUP'. 

11/26/2012 3:55:28 PM

Why would you set hard limits on your transaction log size? Pretty sure Autogrowth is enabled by default.

[Edited on November 26, 2012 at 4:01 PM. Reason : ]

11/26/2012 4:00:06 PM

The db was configured by a 3rd party vendor web app, i'm trying to do some maintenance that can't be done from the front end.

I'm not sure why they would set those limits too...

Also, the query you posted seems to select a lot more than I need it to (if I replace the delete with a select *).

11/26/2012 4:02:19 PM

I was mainly posting that for structure and not logic. Get your logic right with a select, then instead of select * get only the primary key from the records to be deleted, then use that as a subquery like this:

delete
from table
where primary_key in(
select primary_key from ...)

11/26/2012 4:05:14 PM

gotcha, i'll still probably run into the transaction log size though right? I guess i need to solve that problem first...

11/26/2012 4:12:47 PM

Do you have sysadmin permissions on the database?

11/26/2012 5:54:25 PM

Yep.

11/26/2012 8:26:52 PM

I always mess with these settings via the GUI

http://www.simple-talk.com/sql/database-administration/sql-server-database-growth-and-autogrowth-settings/

11/27/2012 12:12:28 AM

SELECT 
	log.*
FROM 
	[crv].[dbo].[CRV_UsageLog] log
	INNER JOIN crv.dbo.CRV_Rooms rooms
		on log.RoomID=rooms.RoomID
	INNER JOIN crv.dbo.CRV_TreeRoomMap trm
		on log.RoomID=trm.RoomID
WHERE
	trm.TreeNodeID='dec8d04f-b01e-4c26-be8f-3e67f74414d6'
	AND log.Data2='Button Press'

DELETE FROM
	log
FROM 
	[crv].[dbo].[CRV_UsageLog] log
	INNER JOIN crv.dbo.CRV_Rooms rooms
		on log.RoomID=rooms.RoomID
	INNER JOIN crv.dbo.CRV_TreeRoomMap trm
		on log.RoomID=trm.RoomID
WHERE
	trm.TreeNodeID='dec8d04f-b01e-4c26-be8f-3e67f74414d6'
	AND log.Data2='Button Press'

11/27/2012 12:46:29 AM

also, who's been pressing buttons they shouldn't???

11/27/2012 12:47:35 AM

paging nOOb

11/27/2012 12:57:24 AM

you should also always make sure you have a good backup before deleting data. tbh you really shouldnt ever delete data at all.

11/27/2012 10:07:50 AM

We have multiple layers of backups for this system, and it's not mission critical.

I'm still learning about this stuff (just for my own curiosity really-- it's not in my job description) and I don't want to be that guy that hoses something then have to run to the network admin to restore from the backup...

11/27/2012 1:10:27 PM

be the fuck careful, especially since it isn't your job

12/1/2012 11:00:21 PM