sql

How can I count the non-unique combinations of values in MySQL?

I have a table with some legacy data that I suspect may be a little messed up. It is a many-to-many join table. LIST_MEMBERSHIPS ---------------- list_id address_id I'd like to run a query that will count the occurrences of each list_id-address_id pair and show the occurrence count for each from highest to lowest number of occurrences...

Why am I causing a Clustered Index Update?

I'm executing the following statement: UPDATE TOP(1) dbo.userAccountInfo SET Flags = Flags | @AddValue WHERE ID = @ID; The column 'ID' is an INT PRIMARY KEY with IDENTITY constraints. Flags is a BIGINT NOT NULL. The execution path indicates that a Clustered Index Update is occurring. A very expensive operation. The...

Linq version of SQL "IN" statement

I have the following 3 tables as part of a simple "item tagging" schema: ==Items== ItemId int Brand varchar Name varchar Price money Condition varchar Description varchar Active bit ==Tags== TagId int Name varchar Active bit ==TagMap== TagMapId int TagId int (fk) ItemId int (fk) Active bit I want to writ...

How do I project to a dictionary with a custom type using Linq to SQL?

Hi, I am attempting to use Linq to project each row from a DB query into a dictionary that has a custom type as its value. I am unsure of the LINQ syntax to do this? This is my current attempt (which doesn't compile, but should demonstrate what I am trying to do). I am having trouble with the 'select new...' part. public class MyClas...

Regex in Postgres - not doing what I'm trying to do (newbie question)

I have this regex in a query in postgres and I cannot figure out why it is not matching anything after the text specified in the regex; The idea is removing the last part, including the separator characters between. I have records like these to match: Villa hermosa, Pilar, PCIA. BS. AS. Esmeralda - Pilar - BUENOS AIRES. San Ma...

SQL Server 2005 - Table Functions in a Select statement

I have a table called PurchaseOrderDetail. TABLE PurchaseOrderDetail PurchaseOrderDetail int, Comments nvarchar(500) In the Comments field of each item I have a ‘;’ separated list that stores A Contract Name, Contract No, License Name, License Version. i.e. PurchaseOrderDetail Comments 1 'Microsoft Offic...

Extracting time zone from Oracle JDBC TIMEZONETZ object

Is there a way to extract timezone information directly from an oracle.sql.TIMESTAMPTZ object (selected from a TIMESTAMP WITH TIME ZONE column)? Ideally, I'd like to be able to pull the time zone information directly out of the object without jumping through potentially expensive or fragile hoops (like converting things into strings and...

Want to select records due to their status in the table using mysql

good morning guys, i have a problem of selecting all records which are inactive or active in a database, but if i select all estates and all status it returns all the estates and their different status, active or inactive, but if i select all estates and active status it returns all the estates and display that they are all active, same ...

SQL Batched Delete

Hello, I have a table in SQL Server 2005 which has approx 4 billion rows in it. I need to delete approximately 2 billion of this rows, but if I try and do it in a single transaction, the transaction log fills up and it fails. I don't have any extra space to make the transaction log bigger, so I assume the best way forward is to batch up...

Select million+ records while huge insert is running

I am trying to extract application log file from a single table. The select query statement is pretty straightforward. select top 200000 * from dbo.transactionlog where rowid>7 and rowid <700000 and Project='AmWINS' The query time for above select is above 5 mins. Is it considered long? While the select is running, the bulk insert...

RESTRICTED_USER

Before changing database schema I issue: ALTER DATABASE SET RESTRICTED_USER On completion: ALTER DATABASE SET MULTI_USER I understand that a running transaction will be permitted to continue until completion. Q: Is there any way to wait till all regular users are off the database? Q: Can the regular users issue more transactions?...

Date format in VB6

I am using VB6 for my application. I've populated Excel with the RecordSet obtained from a SQL query. One column called Time_period has values like "2/31/2006" "12/29/2000" etc. I need to pass these inputs to another SQL query for processing. I am little confused with the formats, as Oracle accepts inputs of type "23-Jul-2009", "02-...

SQL magic - query shouldn't take 15 hours, but it does

Ok, so i have one really monstrous MySQL table (900k records, 180 MB total), and i want to extract from subgroups records with higher date_updated and calculate weighted average in each group. The calculation runs for ~15 hours, and i have a strong feeling i'm doing it wrong. First, monstrous table layout: category element_id date_upd...

Updating a SQL database table with a webservice

Hi I have an issue and I have looked long and hard over the Internet for an answer but cant find anything. I have a little app that sucks in a web service. It then passes this web services results another applications via its own web service and stores the request in a table. What I am trying to do is quickly import the results to ...

MS-ACCESS: deleting all rows execpt for top 1 and updating a table from a query.

I'm almost done with this, just a few last hiccups. I now need to delete all records from a table except for the top 1 where readings_miu_id is the "DISTINCT" column. In other words words i need to delete all records from a table other than the first DISTINCT readings_miu_id. I am assuming all i need to do is modify the basic delete stat...

SQL: grouping 2 tables as 1 with join, union, and then?

Here i am again :) I have 5 tables: customers id - name p_orders id - id_customer - code - date p_items id - id_order - description - price and h_orders and h_items, that are exactly the copy of p_orders and p_items. When the p_ tables reach a big amount of rows, i move the oldest to the h_ tables.. they due as history. So, my pro...

How can I model this Jagged data?

I am in charge of modeling a 3 floor warehouse, where boxes of documents will be stored. There are rows, columns and shelves in the equation. in addition: some floor/row/column/shelf combinations store 2 boxes, some 3. some rows don't have the normal amount of columns. They want my application to auto-increment to print labels (20 ...

How to implement a SQL like 'LIKE' operator in java?

I need a comparator in java which has the same semantics as the sql 'like' operator. For example: myComparator.like("digital","%ital%"); myComparator.like("digital","%gi?a%"); myComparator.like("digital","digi%"); should evaluate to true, and myComparator.like("digital","%cam%"); myComparator.like("digital","tal%"); should evaluate...

Insert value from one table to another by changing the flag

I have to insert value from one table to another table which both having two fields first field name is ID and and second field name is Flag. While inserting if the ID value already exists in any of the destination table rows it will insert the new row in source table as same ID and Flag as NEW if the ID value is not matching in any of...

How to select columns from table in random order in TSQL

I have a table with 5 columns in it, what's the easiest way to select all rows, but where each column is individually randomised? All I can think of is to select each column separately, along with row_number over ( order by newid()) as lookup and then join each column back together on lookup. Is there an easier way? Thanks ...