Hi all--Given a query that returns ~557K rows on SQL Server 2005 SP1:
SELECT distinct(a.[Import_Date]),b.[Import_Date],
a.[OS_USERNAME],b.[OS_USERNAME],a.[USERNAME],b.[USERNAME],
a.[TIMESTAMP],b.[TIMESTAMP]
FROM [DBA_AUDIT_SESSION] a, [DBA_AUDIT_SESSION] b
where a.[OS_USERNAME]=b.[OS_USERNAME]
and a.[USERNAME]=b.[USERNAME]
and a.[TIMESTAMP]=b.[TIMESTAMP]
and a.[Import_Date]<b.[Import_Date]
I would like to delete duplicate data from this table and keep only the data with the latest Import_Date. I came up with the following delete statement:
delete FROM [DBA_AUDIT_SESSION]
FROM [DBA_AUDIT_SESSION] a,
inner join [DBA_AUDIT_SESSION] b
on a.[TIMESTAMP]=b.[TIMESTAMP]
where a.[Import_Date]<b.[Import_Date];
The command parses successfully, but I get the following runtime error:
'Table DBA_AUDIT_SESSION is ambiguous.'
Does anyone have suggestions on how to fix this delete statement?
I have the following sp:@TABLE_NAME varchar(255), @CONTROL_ID int
AS DECLARE @sql varchar(4000) SELECT @sql = 'DELETE FROM [' + @TABLE_NAME + '] WHERE CONTROL_ID = 5'SELECT @sql = 'DELETE FROM [' + @TABLE_NAME + '] WHERE CONTROL_ID = ' + @CONTROL_ID + '' EXEC (@sql) When I use the the first SELECT line, it works great.When I use the second dynamic select, SQL raises an error: Syntax error converting the varchar value 'DELETE FROM [TABLE_SETTINGS] WHERE CONTROL_ID = ' to a column of data type int.What is wrong?
this is my Delete Query NO 1 alter table ZT_Master disable trigger All Delete ZT_Master WHERE TDateTime> = DATEADD(month,DATEDIFF(month,0,getdate())-(select Keepmonths from ZT_KeepMonths where id =1),0) AND TDateTime< DATEADD(month,DATEDIFF(month,0,getdate()),0) alter table ZT_Master enable trigger All
I have troble in Delete Query No 2 here is a select statemnt , I need to delete them select d.* from ZT_Master m, ZT_Detail d where (m.Prikey=d.MasterKey) And m.TDateTime> = DATEADD(month,DATEDIFF(month,0,getdate())-(select Keepmonths from ZT_KeepMonths where id =1),0) AND m.TDateTime< DATEADD(month,DATEDIFF(month,0,getdate()),0) I tried modified it as below delete d.* from ZT_Master m, ZT_Detail d where (m.Prikey=d.MasterKey) And m.TDateTime> = DATEADD(month,DATEDIFF(month,0,getdate())-(select Keepmonths from ZT_KeepMonths where id =1),0) AND m.TDateTime< DATEADD(month,DATEDIFF(month,0,getdate()),0) but this doesn't works..
can you please help? and can I combine these 2 SQL Query into one Sql Query? thank you
I'm using SqlDataSource and an Access database. Let's say I got two tables:user: userID, usernamemessage: userID, messagetextLet's say a user can register on my website, and leave several messages there. I have an admin page where I can select a user and delete all of his messages just by clicking one button.What would be the best (and easiest) way to make this?Here's my suggestion:I have made a "delete query" (with userID as parameter) in MS Access. It deletes all messages of a user when I type in the userID and click ok.Would it be possible to do this on my ASP.net page? If yes, what would the script look like?(yes, it is a newbie question)
I am struggling figuring out the token from a CMDEXEC job (as opposed to TSQL Job). It is not an option to execute the command by enabling the executing CMDs via TSQL, which is why I am using the agent. I have seen the Microsoft Site on tokens but all examples seem to be oriented to TSQL Job Type.
I am trying to delete a particular trace file and at same time keeping the SQL Directory dynamic.Taking it a step further is adding in "deleting if file exist".
I need to run a DELETE query based on 2 tables. I can't use JOIN with delete queries, so how do I do this?
What I initially tried to do was:
Code:
DELETE FROM tblProductState JOIN tblProduct ON tblProduct.id_Product = tblProductState.id_Product WHERE tblProductState.id_State = 54 AND tblProduct.id_ProductType = 1
Basically, I need to delete FROM tblProductState, WHERE tblProductState.id_State = 54 AND tblProduct.id_ProductType = 1
How can I do this without using JOIN. Use a sub-query? How?
Hi all. I'm currently encountering a problem in attempting to find a solution for a dynamic or on the fly query. I understand how you can make a static query and return it as a dataset; for example, say you have a field where a user enters a name on the front end and the db returns the results: Dim queryString As String = "SELECT [Table].* FROM [Table] WHERE ("& _ "[Table].[Name] = @Name)" But what if I have multiple items I would like query based upon what the user picks. For example, say you have five fields: Name, ID Number, Date, Address, and State. Say the user wants to pick all data with a date between Jan. 06 to April 06, with the name of Tom, and in the state of CA. But then next time, the user only wants all data with the name of Susan. So the query is always changing and I am not sure exactly how to go about it. I guess I sorta want similiar functionality as that of the Custom Auto Filter in Excel. I've been reading a couple of the forums and I think people are using a string to pass to query the database. But I am still vague on how to approach this. Any help would be greatly appreciated!
I am having trouble wrapping my head around some dynamic queries. I have x number of queries stored in a table. Each row in this table has a From, Join, and Where column. So, for x=3, I can run the query from each row so that I may have Q1: (1, 2, 4, 5, 7, 8) Q2: (1, 3, 5, 7, 9) Q3: (2, 4, 6, 8, 10) I need to use these queries to generate a shared table: |-------------------| | ID | Q1 | Q2 | Q3 | | 1 | 1 | 1 | 0 | | 2 | 1 | 0 | 1 | | 3 | 0 | 1 | 0 | | 4 | 1 | 0 | 1 | | 5 | 1 | 1 | 0 | | 6 | 0 | 0 | 1 | | 7 | 1 | 1 | 0 | | 8 | 1 | 0 | 1 | | 9 | 0 | 1 | 0 | | 10 | 0 | 0 | 1 | |-------------------| I'm not sure the best way to do this. I think that doing it on the asp.net side will be easier than in t-sql, although I am exploring both possibilities. Any suggestions?
Hi, I have basic design question regarding dynamic query, When we have to build a dynamic query (which has table name also as an input parameter), ->Is it better to write a stored procedure ..? or ->directly specify the dynamic query and set the command type as text in .NET code...?
I think,since dynamic queries may not have the advantage of precompliation, it may not yield any performance in using SP's in such case..
I have an array list of Branch_ID's; i need to select all records from 2 tables that have any of the branch id's in that array associated with them. How would I go about doing this?
I have two options two write queries one Static & one Dynamic. I wanted to know which one will be good.
Example
if @x = 1 Select * From Customers order by CustomerID Else IF @x = 2 Select * From Customers Order by CustomerName Else IF @X = 3 Select * From Customers Order by city else.. ...
Against this Set @Strsql = "Select * From Customers order by " + @Orderby Exec (@Strsql)
Which will have better performance ? Is SQL Stores query plans for static queries with all if statements clause or it remains as good as dynamic ?
I'm the new guy and I could use a hand. Any insight would be appreciated.
Basically my task is to create a dynamic way to merge columns from multiple rows. Way the table is set up data is imported and one entry may be up to 3 rows one column from each row can be merged to form a long description, I would like to create a view that would allow you to dynamically query this data and have the description be merged in the result set.
row1 x y z row2 x b z row3 x m z
results should look like :: x, (y + b + m) , z
Thank you in advance for any help you can provide!
Hi I am new to sql. I have to run a query in a manner that I can see customer name and their photo dynamically? I already have table created where names and customers images are there.Please help.
create procedure up_CrossTab (@SelectStatement varchar(1000), @PivotColumn varchar(100), @Summary varchar(100), @GroupbyField varchar(100), @OtherColumns varchar(100) = Null) AS /* Inputs are any 1000 character or less valid SELECT sql statement, the name of the column to pivot (transform to rows), the instructions to summarize the data, the field you want to group on, and other fields returned as output. 1 */ set nocount on set ansi_warnings off
declare @Values varchar(8000); set @Values = '';
set @OtherColumns= isNull(', ' + @OtherColumns,'') /* An 8000 varchar variable called @values is created to hold the [potentially filtered] values in the pivot column. @Values is initiated to an empty string. Then, a temporary table is created to hold each unique value. After the table is created, its rows are loaded into the variable @values. It's usefullness completed, the temporary table is destroyed. 2 */ create table #temp (Tempfield varchar(100))
insert into #temp exec ('select distinct convert(varchar(100),' + @PivotColumn + ') as Tempfield FROM (' + @SelectStatement + ') A')
select @Values = @Values + ', ' + replace(replace(@Summary,'(','(CASE WHEN ' + @PivotColumn + '=''' + Tempfield + ''' THEN '),')[', ' END) as [' + Tempfield ) from #Temp order by Tempfield
drop table #Temp /* Finally, a dynamic sql select statement is executed which takes the GroupByField, and OtherColumns, passed into the procedure, and each of the Values from the Pivot Column from the passed in SELECT statement . 3 */ exec ( 'select ' + @GroupbyField + @OtherColumns + @Values + ' from (' + @SelectStatement + ') A GROUP BY ' + @GroupbyField)
set nocount off set ansi_warnings on GO
And then my sql query is as like
EXEC up_CrossTab 'SELECT ProdId, GrnDate,Quantity FROM inteacc..IcGrnD IcGrnD INNER JOIN inteacc..IcProduct IcProduct ON (IcGrnD.ProdId=IcProduct.ProdId) ', 'Year(GrnDate)', 'sum(Quantity)[]', 'ProdId'
error occurring
ambiguous column name ‘ProdId’
But when I compile this query
EXEC up_CrossTab 'SELECT grnNo,GrnDate,Quantity FROM inteacc..IcGrnD IcGrnD INNER JOIN inteacc..IcProduct IcProduct ON (IcGrnD.ProdId=IcProduct.ProdId) ', 'Month(GrnDate)', 'sum(Quantity)[]','GrnNo'
Ok.I must write a SP and it gets a parameter,say @param. if @param=1 then in the select statement I will select Col1,if @param=2 then I will select Col2 and so on.
Good Day to all, I just started using SQL Server 2005 and didn't have any experience with other DB... Is it possible to make a dynamic query? I would like to make a stored procedure where the table name from where it will select/insert/update data is user inputed... if it's possible, can someone please guide me.. thanks in advance...
Hi I need some help on a query. I need to delete some records from a table, this table has a dependency to another table Table 1: dbo.Accounts and Table 2: dbo.AccountsToUser In the dbo.Accounts table there are AccountId and OwnedByAccountId 54708002 54708001 54708003 54708001 65708002 65708001 65708003 65708001 54708001 2233440165708001 NULL In the dbo.AccountsToUser there are AccountId and UserId 65708002 10065708003 10165708003 10465708003 10654708001 19465708002 199 What I need is to delete every record from dbo.AccountsToUser that has an account connection to an account in the dbo.Accounts that has OwnedByAccount like NULL So in the example above I should delete from dbo.AccountsToUser 65708002 10065708003 10165708003 10465708003 106 Since they are connected to 65708001 which has OwnedByAccountStatus like NULL I could delete the records manually since the table is still hand able, but I need this to be a daily job so all help would be very nice Thanks!
Can any one please correct this query.Shall i write like this.Please correct this query. Delete globalDocs.dbo.gdoc_File set IsTrue=1 where FileID='abc'
Hi friends, I want to delete more than record using id.i pass group of id(with string concat) like 10|11|20|25. in stored procedure i want to delete corresponding record.10112025 any idea?Thanks,Durai
Hoping someone can help me out with my query query!
I'm trying to write a script to do the following: delete everything from table1 where column A and B (of table1) does not match column A and B of table2
DELETE FROM tblStkAdjDetail WHERE (SELECT ItemStorageID FROM tblStkAdjDetail WHERE Status='NEW' AND ItemStorageID NOT IN (SELECT ItemStorageId FROM tblTempTableForRecvPacking) )
I am beginner ot SQL Server. I want to know that when we delete a row or a set of Rows from a table, it'll only make the space available for subsequent inserts into it or will the Delete also free the memory used by the table.
Suppose I am inserting customer records in the details table when the customer comes into the system. If i make a logic to delete the customer record from the table and insert into the backup table when it leaves the system(As the data inserted is quite large and my application queries into this table at each transaction). Will it help in optimizing the SQL Queries or it is useless to do so.
I have part of a stored proc that I need help with. I need to figure out how to delete infro from the tblManifest using the @ssnDelete variable. I Have multiple records ffor the given above variable.
Would this need to loop in order to work and that is the solution for this.
I get ther following error when I run the full stored proc. ------------------------------
Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. ------------------------------
Here is the code fro part of the proceedure. Thanks in advance.
I have part of a stored proc that I need help with. I need to figure out how to delete infro from the tblManifest using the @ssnDelete variable. I Have multiple records ffor the given above variable.
Would this need to loop in order to work and that is the solution for this.
I get ther following error when I run the full stored proc. ------------------------------
Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. ------------------------------
Here is the code fro part of the proceedure. Thanks in advance.
I am so sorry if I posted this to the wrong forum, here is my dilemma and I am hoping someone can point me.I have a SQL query such as this... SELECT tblNode, tblCorp, tblService FROM tblName WHERE tblNode = @tblNode AND tblCorp = @tblCorp AND tblService = @tblServiceThe @tblNode, @tblCorp, @tblService are all pulling from 3 separate drop down controls. Ok easy enough.However, I want the drop downs to default to "ALL" and I want all records pulled. Or if only tblCorp and tblNode is done, I want all tblServices.Does this make sense? How can I do this with 1 single SQL String without having to build 27 separate queries and a case statement to get the same result?I guess I am looking to see if a wild card can be used so tblNode = % or something. The server is MSSQLThank you
