Reseeding the Identity column for new records only (SQL)
I have data in a table, but am working on data loading. I want to reseed all new inserts for testing. What would be the line to set all new inserts to a certain seed value? ...
sql
Oracle sql: using bind variable for dates..
Here is a simple working query without bind variables: select * from table1 where time_stamp > sysdate - INTERVAL '1' day; where time_stamp is of type DATE. I should be able to input any number of days in the above query using bind variable. So I tried the following and does not seem to work: select * from table1 where time_stamp > ...
To get data from table in script from sql server 2005
Hi Experts, I am using sql server 2005 I have a table [say tblHistory] and this table contains 100 rows. I have created the same table at the server, but the table doesn't have the data, I want data from tblHistory to convert into INSERT INTO tblHistory ------ so that I could run the script on the server to fill the database. Pleas...
Connecting Excel to Mysql Update PROBLEM
i am connecting to a mysql table through vba in excel and i am updating it: Set cn = New ADODB.Connection cn.Open "DRIVER={MySQL ODBC 5.1 Driver};" & _ "SERVER=localhost;" & _ "DATABASE=employees;" & _ "USER=root;" & _ "PASSWORD=M1llen;" & _ "Option=3" 'lets get the batch info ' ' open a recordset Set rs = New ADODB....
Not sure how to use Decode, NVL, and/or isNull (or something else?) in this situation
I have a table of orders for particular products, and a table of products that are on sale. (It's not ideal database structure, but that's out of my control.) What I want to do is outer join the order table to the sale table via product number, but I don't want to include any particular data from the sale table, I just want a Y if the jo...
what does rs.Fields(0) mean? (ADODB) VBA
dim rs As ADODB.Recordset ... ... ... capture_id = rs.Fields(0) what does .Fields(0) mean? ...
Django: Determining if a user has voted or not
I have a long list of links that I spit out using the below code, total votes, submitted by, the usual stuff but I am not 100% on how to determine if the currently logged in user has voted on a link or not. I know how to do this from within my view but do I need to alter my below view code or can I make use of the way templates work to ...
Oracle: How to update master with newest row from detail table?
We have two tables: Vehicle: Id RegistrationNumber LastAllocationUserName LastAllocationDate LastAllocationId Allocations: Id VehicleId UserName Date What is the most efficient (easiest) way to update every row in Vehicle table with newest allocation? In SQL Server I would use UPDATE FROM and join every Vehicle with newest All...
How to write a "Not In" SQL query in LINQ?
How would I translate the following SQL query in to a comparable LINQ query? select * from Dept where Id not in ( Select Id from Employee where Salary > 100); ...
Use SQL to clone a tree structure represented in a database
Given a table that represents a hierarchical tree structure and has three columns ID (Primary Key, not-autoincrementing) ParentGroupID SomeValue I know the lowest most node of that branch, and I want to copy that to a new branch with the same number of parents that also need to be cloned. I am trying to write a single SQL INSERT IN...
Fixing an SQL where statement that is ugly and confusing
I am directly querying the back-end MS SQL Server for a software package. The key field (vehicle number) is defined as alpha though we are entering numeric value in the field. There is only one exception to this, we place an "R" before the number when the vehicle is being retired (which means we sold it or the vehicle is junked). Assumin...
Enforcing a query in MySql to use a specific index
Hi, I have large table. consisting of only 3 columns (id(INT),bookmarkID(INT),tagID(INT)).I have two BTREE indexes one for each bookmarkID and tagID columns.This table has about 21 Million records. I am trying to run this query: SELECT bookmarkID,COUNT(bookmarkID) AS count FROM bookmark_tag_map GROUP BY tagID,bookmarkID HAVING tagID IN ...
Effects of order of columns, while defining an index on them, on the performance of the queries
Hi, Does order of the columns in an Index definition for a table in a database has any effect on the performance? for e.g. are these two queries different ? CREATE INDEX xxx ON tablex(col1,col2) CREATE INDEX xxx ON tablex(col2,col1) what about the in case that I use a BTREE index? I am using Mysql. thanks ...
Stop invalid data in a attribute with foreign key constraint using triggers?
How to specify a trigger which checks if the data inserted into a tables foreign key attribute, actually exists in the references table. If it exist no action should be performed , else the trigger should delete the inserted tuple. Eg: Consider have 2 tables R(A int Primary Key) and S(B int Primary Key , A int For...
A SELECT statement for Mysql
I have this table: id,bookmarkID,tagID I want to fetch the top N bookmarkIDs for a given list of tags. Does anyone know a very fast solution for this? the table is quite large(12 million records) I am using MySql ...
Best optimizing for a large SQL Server table (100-200 Mil records)
What are the best options/recommendations and optimizations that can be performed when working with a large SQL Server 2005 table that contains anywhere from 100-200 Million records? ...
Copying contents of a MySQL table to a table in another (local) database
I have two MySQL databases for my site - one is for a production environment and the other, much smaller, is for a testing/development environment. Both have identical schemas (except when I am testing something I intend to change, of course). A small number of the tables are for internationalisation purposes: TransLanguage - non-Englis...
Why does Linq to Entities generate Left Outer Joins?
I was wondering if anyone knew why linq to entities always seems to generate left outer joins. I would understand it on an optional relationship but it doesn't make good sense when the relationship is mandatory. Does anyone have any idea how to make it generate inner joins instead? ...
Sub-total and total columns
In Visual Foxpro 9 I am trying to write an sql with a product "subtotal" column and a report "total" column. The sql code that works is as follows, but when I insert the commented out "Case" code I get errors that seem to increase as I correct the preceeding error. Can any one tell me in which place I should insert the "case" and what is...
Mysql : Count Posts and Group by date
Hello , I am not very good at sql , generally I use php to do my complicated tasks , But in this task , there are lots of data , so using php for counting posts is very slow. So I want a sql which counts post by date , but my date column in table is php's time stamp (int).I will crate post number x date chart ...