|
Hi,
I need to do update operation for a set of ids, for this i need to use comma separator how can do it
I am giving like this
update Emp_Tbl set empstatus=1 where empid in (@empids)
is it enough or any other methods
|
|
|
|
|
This will work 
================
DECLARE @empids varchar(100)
SET @empids= '66696, 66695, 66694, 66693, 66692'
exec('update Emp_Tbl set empstatus=1 where empid in (' + @empid + ')')
Look where you want to go not where you don't want to crash.
Bikers Bible
-- modified at 6:46 Tuesday 23rd May, 2006
|
|
|
|
|
1)OLEDB Connection Pooling is handled by OLEDB.Net Provider and SQLClient Connection pooling is handled by____
2)If the DSN (Data Source Name) has to be used with SQL Server, which namespace has to be imported?
3)If the DSN (Data Source Name) has to be used with SQL Server, which namespace has to be imported?
4)Which object in ADO.Net is similar to the Recordset object of ADO?
plz let me konw the ans
witn regards
d.ravi kiran reddy
|
|
|
|
|
hi,
This is Dayakar
You need to add following namespaces for the specified data providers:
Data Provider Namespace
MS SQL Server System.Data.SqlClient
Oracle Database System.Data.OracleClient
OLE DB Databases System.Data.OleDBClient
ODBC Data Sources System.Data.ODBCClient
|
|
|
|
|
Hi,
I want to make one column as a index in one table. i.e how to make one column as a primary key at run time
|
|
|
|
|
ALTER TABLE dbo.TableName ADD CONSTRAINT
PK_KeyName PRIMARY KEY CLUSTERED
(
FieldName
) ON [PRIMARY]
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
Is there a way to view the results of an action query without committing the changes to the database in SQL Server 2000? Any suggestions or tips will be greatly appreciated.
-Garrett
|
|
|
|
|
I'm not really sure how wise this is but this:
SELECT * FROM [Order Details]
BEGIN TRANSACTION
DELETE FROM [Order Details]
SELECT * FROM [Order Details]
ROLLBACK TRANSACTION
SELECT * FROM [Order Details]
This shows the state of the Order Details table, then it shows the result of the delete operation, but after the transaction is rolled back the state of the database reverts.
Is this the kind of thing you are looking for?
[ADDITION]
I should point out that this does actually make changes to the database and it could impact performance of other things running in the database.
[/ADDITION]
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
-- modified at 12:51 Monday 22nd May, 2006
|
|
|
|
|
Pretty much. Thanks for the help.
|
|
|
|
|
I have a sql problem.
suppose we have two tables ex. A and B which have A1,A2,A3 column and B has B1,B2,B3 Column A1 and B1 are primary key and B2 is foreign key of A(A1) Now B3 has some values in it which i wanted to map with column A3.
means i wanted to update column A3 as same as B3 where we have A1=B2 relationship. can we write this in a single sql statement. what i am doing is writing a cursor on table A which one by one retrieve value from table B(B3) and update table A(A3). But problem is that this process is very slow and lock the table at the time it perform operation so all other request will have to wait until this process terminate. otherwise some times sqlserver detects it as a deadlock condition and unconditionally terminate process. So my aim is improve performace of all such processes.
Error :
Transaction (Process ID 773) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
I wanted some thing like as easy as
update A set A.a3= B.b3 where A.a1 =B.b2
which does not work in this case.
Table has bulk amount of data i. e. around 10-15 lacks records. I need some alternative solution for many of such process in which i have used same logic am working on. also the database is of prouction server so I cannot kept it busy for long time.
Pallav Deshmukh
|
|
|
|
|
something like this ???
update a set a.a3 = (SELECT top 1 b.b3 FROM b INNER JOIN a ON a.a1 = b.b1)
My second computer is your linux box.
|
|
|
|
|
Thanks Colin Angus Mackay & woudwijk
I am not having good experience of database thanks for help it works.
Pallav Deshmukh
|
|
|
|
|
Something like this:
UPDATE A
SET A3 = B.B3
FROM A
INNER JOIN B ON A.A1 = B.B2
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
Thanks Colin Angus Mackay & woudwijk
I am not having good experience of SQL. thanks for help!! it works.
Pallav Deshmukh
|
|
|
|
|
I'm trying to make a simple database manager program that allows a user to select a database file, see a list of the tables and each column in the table, and to write and execute SQL to alter the database. I am able to find a list of tables in a .mdf file by using this sql statement:
"SELECT TABLE_NAME FROM information_schema.tables ORDER BY TABLE_NAME"
But now I want to get my program to work for an access database file (.mdb) and this does not work. Does anyone know the name of and how to access a schema or metadata table in an access database?
Any help would be appreciated.
|
|
|
|
|
SELECT DISTINCT MSysObjects.Name
FROM MSysObjects
WHERE MSysObjects.Type=1
ORDER BY MSysObjects.Name
I think adding "AND MSysObjects.Flags=0" will exclude system tables, but I'm not 100% on whether or not that is all that will be excluded.
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
EricDV wrote:
I think adding "AND MSysObjects.Flags=0" will exclude system tables, but I'm not 100% on whether or not that is all that will be excluded.
Yes, it does
Paul
|
|
|
|
|
I decided to go another way and use the oledb connection object's GetSchema function. So, for example this bit of code will return a datatable with all of my tables in it:
Dim strRestrictions As String() = New String() {Nothing, Nothing, Nothing, "TABLE"}<br />
dtTables = myConnection.GetSchema("tables", strRestrictions)
and this bit of code returns the columns within a table:
Dim strRestrictions = New String() {Nothing, Nothing, "myTableName", Nothing}<br />
dtColumns = myConnection.GetSchema("Columns", strRestrictions)
my new problem is that I can't find out which columns are primary keys. Can anyone point me in the right direction?
|
|
|
|
|
I've a table where ia save the date when user have subscreibed.
How can i query the number of subscriber signed in the 20 April 2006 or all the users signed since to 12 may 2006?
Or between 14 april and 21 may?
|
|
|
|
|
if you save dates in the integer format like this: YYYYMMDD then you should be able to do something like this to get a count of subscribers on April 20, 2006:
SELECT COUNT(*) FROM Subscribe_Table WHERE Date_Column = 20060420
To get a count of subscribers since May 12, 2006:
SELECT COUNT(*) FROM Subscribe_Table WHERE Date_Column > 20060512
To get a count of subscribers between April 14 and May 21 of 2006:
SELECT COUNT(*) FROM Subscribe_Table WHERE Date_Column > 20060414 AND Date_Column < 20060521
|
|
|
|
|
Uhm... the value is a DateTime. What can i do without change the database?
|
|
|
|
|
|
Okay, I'm assuming you are using SQL Server, and the column is a DATETIME or SMALLDATETIME.
For a single day you can use this:
SELECT * FROM MyTable WHERE [date] >= '2006-04-20 00:00:00' AND [date] <= '2006-04-20 23:59:59' If you are only storing the date element without the time then you can do a simpler [date] = '2006-04-20' instead.
The same works for longer date ranges. e.g.
SELECT * FROM MyTable WHERE [date] >= '2006-04-14 00:00:00' AND [date] <= '2006-05-21 23:59:59'
Does this help?
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
Does this work?
on April 20th 2006
SELECT COUNT(*) AS totalsubscribers FROM subscribers WHERE CONVERT(VARCHAR, datesubscribed,101) = '04/20/2006'
since May 12th 2006
SELECT COUNT(*) AS totalsubscribers FROM subscribers WHERE CONVERT(VARCHAR, datesubscribed,101) >= '05/12/2006'
between April 14th and May 21st
SELECT COUNT(*) AS totalsubscribers FROM subscribers WHERE CONVERT(VARCHAR, datesubscribed,101) BETWEEN '04/14/2006' AND '05/21/2006'
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
That would do a string based comparison, so you would be better using an ISO format where it puts YYYY-DD-MM (i.e. most significant digits first) - It also keeps it culture neutral.
You don't need to convert the DATETIME or SMALLDATETIME into varchars either because a string literal will be implicity converted to a date if the format is appropriate.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
