I have a table with a "SortID" column that is numbered using consecutive numbers. Whenever a row is deleted, it leaves a gap. Is there a way using pure SQL to update the rows with their row number? Something like this: UPDATE tbl SET SortID={rowindex} ORDER BY SortID (I realize this isn't valid SQL, that's why I'm asking for help) Th...
I found something that works with updating one field at here: http://www.karlrixon.co.uk/articles/sql/update-multiple-rows-with-different-values-and-a-single-sql-query/ UPDATE person SET name = CASE id WHEN 1 THEN 'Jim' WHEN 2 THEN 'Mike' WHEN 3 THEN 'Precious' END WHERE id IN (1,2,3) My question is how...
I'd like to count and group rows by specific values. This seems fairly simple, but I can't seem to do it. I have a table set up similar to this: Table: Ratings id pID uID rating 1 1 2 7 2 1 7 7 3 1 5 4 4 1 1 1 id is the primary key, piD and uID are foreign-keys. Rating contains values between 1 and 10, and...
I have a table that catalogs selected files from multiple sources. I want to record whether a file is a duplicate of a previously cataloged file at the time the new file is cataloged. I have a column in my table (“primary_duplicate”) to record each entry as ‘P’ (primary) or ‘D’ (duplicate). I would like to provide a Default Binding for...
Follow is my trigger of Sybase ASA 8.0 script: Create trigger dba.test after insert,delete,update order 1 on DBA.tb_press referencing old as _old new as _new for each row begin --How to detect deleteOperate,updateOperate? if deleteOperate then insert into syncLog(tableName,keyId,version,operate) select tb_press,_o...
the old query works, the new on doesnt. the android logcat gives me error as: Failure 1: no such column abcname. abcname is the value of a editview that im trying to get from a popup in android. i know that the first query will insert those values in the fields, as they are given in single inverted commas. the same query, i typed in the...
I'm not even sure how to even phrase this as it sounds weird conceptually, but I'll give it a try. Basically I'm looking for a way to create a query that is essentially a WHERE IN LIKE SELECT statement. As an example, if I wanted to find all user records with a hotmail.com email address, I could do something like: SELECT UserEmail FR...
Hi guys I'm working on my web based ordering system and we would like to maintain a kind of task history for each of our orders. A hsitory in the sense that we would like to maintain a log of who did what on an order like lets say an order has been entered - we would like to know if the order was acknowledged for an example. Or lets say ...
Hi, I need advice. I have asp.net web service and winforms client app. Client call this web method and get dataset. 1. [WebMethod] 2. public DataSet GetSecureDataSet(string id) 3. { 4. 5. 6. SqlConnection conn = null; 7. SqlDataAdapter da = null; 8. DataSet ds; 9. try ...
Hello All... I have a following scenario in my SQL Server 2005 database. zipcodes table has following fields and value (just a sample): zipcode latitude longitude ------- -------- --------- 65201 123.456 456.789 65203 126.546 444.444 and place table has following fields and value : id name zip ...
i have two tables table1 fields fid,fname,fage a ,abc ,20 b ,bcv ,21 c ,cyx ,19 table2 fields rcno,fid,status 1 ,a ,ok 2 ,c ,ok 3 ,a ,ok 4 ,b ,ok 5 ,a ,ok i want to display rectors like this fid from table1 , count(recno) from table 2 and fage from ta...
I was reading over the tutorial here: http://www.1keydata.com/sql/sql-running-totals.html and it all made sense until it suddenly got extremely ridiculously unbelievably complicated when it got to rank, median, running totals, etc. Can somebody explain in plain English how that query results in a running total? Thanks! ...
Hi Team, I am using SQL Server 2005. I have a table as given below. There can be multiple cancellations for each FundingID. I want to select the FundingCancellationReason corrersponding to minimum date for each funding. I wrote a query as follows. It is an SQL error 1) Could you please help me to avoid the SQL Error? 2) Is there any b...
I know that NoSQL databases are very new. I am also new in this point. But exist already tools/libraries to make the life easer like for SQL databases? I think on tools for managing, maintaining, viewing or reporting of the data. There can also be libraries for easer working with the database or an abstract database layer to change the...
When performing many inserts into a database I would usually have code like this: using (var connection = new SqlConnection(connStr)) { connection.Open(); foreach (var item in items) { var cmd = new SqlCommand("INSERT ...") cmd.ExecuteNonQuery(); } } I now want to shard the database and therefore need to choose the c...
users table +-----+-----------+ | id | username | +-----+-----------+ | 1 | tom | | 2 | jelly | | 3 | foo | | 4 | bar | +-----+-----------+ groups table +----+---------+-----------------------------+ | id | user_id | title | +----+---------+-----------------------------+ | 2 | ...
I'm trying to get the result of a COUNT as a column in my view. Please see the below query for a demo of the kind of thing I want (this is just for demo purposes) SELECT ProductID, Name, Description, Price, (SELECT COUNT(*) FROM ord WHERE ord.ProductID = prod.ProductID) AS TotalNumberOfOrders FROM tblProducts p...
Hi all, I've decided to develop a database driven web app, but I'm not sure where to start. The end goal of the project is three-fold: to learn new technologies and practices, deliver an unsolicited demo to management that would show how information that the company stores as office documents spread across a cumbersome network fold...
Please look at this image here is 3 tables , and out i want is uid from table1 industry from table 3 of same uid count of fid from table 2 of same uid like in the sample example output will be 2 records Thanks ...
I have first names stored in one table and last names stored in another. I know this is silly but I am trying out different things as I'm just starting with MySQL. Anyway is it possible to select the first name from one table and the last name from another in one query? And put the result inside a PHP variable? ...