DO NOT USE this for questions targeting Microsoft SQL Server, use the tag sql-server instead. "SQL" is an acronym for "Structured Query Language", it is used in relational database management systems (RDBMS) to query, update, delete, and insert data as well as modify the structure of the database. ...

learn more… | top users | synonyms (1)

0
votes
1answer
153 views

SQL Query - Possibly the most poorly written query in the history of mankind

Having a major headache trying to tune this query and looking for help, a programmer who had no idea what they were doing messed with this query and it is currently being used in production and it ...
0
votes
1answer
88 views

MySQL - How to replace a given value from multiple columns when using SELECT?

I need to SELECT a set of rows of a given table whose structure looks like - 1º line is the name of columns A01 A02 A03 A04 A05 ... A200 99999 99999 10 753 99999 ...
0
votes
0answers
40 views

Cannot insert into table

I have created a table called users in my computer by the following method. <table name="users"> </table> Now I am trying to insert a record in it by the following method. ...
1
vote
0answers
34 views

Rolling back a SQL Server 2008R2 Database stored in SQL Server Express [closed]

We had an accident where a bunch of data was deleted from one of our products. The product removed a bunch of lines from various tables about 12 hours ago. There is no good backup and I try to ...
0
votes
0answers
57 views

SQL Server Timesheet Query Performance

I am working on a webapp for timesheet administration. I write a query to group all the time entry of an employee by week. And I also have to use paging. The query is already slow with something like ...
0
votes
1answer
59 views

MYSQL any way to improve this query's performance?

MYSQL version 5.6.23 CENTOS 7 Hi, I have the following query that is coming from an application that I am hoping to make more efficient from the mysql side of things. I have an index built on ...
0
votes
1answer
49 views

Any tips to watch out for when setting one of my databases to Read Only?

Our front end application uses\ pointed to a different database now in SQL Server 2008R2 in the same instance, so to prevent users from accidentally updating data in the old production database, is it ...
0
votes
1answer
55 views

GRANT permissions to all tables, views, procedures in SQL Server 2000

I was wondering if there was a fairly efficient way to use T-SQL with SQL Server 2000 syntax to GRANT SELECT, INSERT, UPDATE, DELETE, on all TABLES and VIEWS for a particular database while excluding ...
-5
votes
1answer
34 views

Increment the value of an entire column [closed]

I have a table and one of the columns, is of the data type int. I want to increase every tuple's value of that specific column by 100. I have not found a proper answer for this yet. Anybody?
0
votes
0answers
24 views

Best way to run a formula (segment) a large number of users with custom attributes

Here is my question. I have a table that stores a large number of people (80 mil). Each person has a GUID id. Then I have another table (or set of tables) that stores information about people. e.g. ...
2
votes
2answers
290 views

MySQL - How to check whether all values of a given SELECTed row are equals? [closed]

Using MySQL, how can i SELECT all rows whose values are equals ? For instance, suppose a SELECT of a given table as follows COLUMN_A COLUMN_B COLUMN_C ... COLUMN_N 1 2 4 ...
0
votes
1answer
22 views

sub and sub-sub item organization system [duplicate]

So I am looking to find the best database design for the following situation. I will refer to my entities as 'articles' as that would best/most-easily describe them. So the articles have already ...
0
votes
2answers
199 views

Storing IP addresses - varchar(45) vs varbinary(16)

I am going to create a table with two fields - ID as BIGINT and IPAddress as either varchar(45) or varbinary(16). The idea is to store all unique IP addresses and use a reference ID instead the actual ...
3
votes
3answers
139 views

How to interpret correlated subqueries?

EDIT: Both answers are great but I think I must clarify my question. NOTE: I must indicate that in the following, by executed, I mean logically. I understand that the actual execution made by the ...
2
votes
2answers
71 views

Sampling in PostgreSQL

I am looking for possible ways of random sampling in PostgreSQL. I found a couple of methods to do that with different advantages and disadvantages. The naive way to do that is: select * from ...
1
vote
1answer
63 views

updating same column as in the select query

When I try to do this, I get an error saying I cannot use the same table in where select as the column that I am updating. UPDATE table_name SET quantity = 19 WHERE productID = 148357 ...
0
votes
1answer
69 views

SQL update multiple columns matching a pattern

I have a table in a postgresql database in which I have multiple columns matching a pattern /\w*(?:_open|_close)/g. Those are timefields. I would like to substract 1 hour from all the rows on those ...
1
vote
1answer
45 views

Reference column alias in same SELECT list

I'm converting an old MS-Access-based system to PostgreSQL. In Access, fields that were made up in SELECTs could be used as parts of equations for later fields, like this: SELECT samples.id, ...
6
votes
1answer
775 views

SQL Query challenge

I have what seemed like an easy query to write, that really has me stumped - can anyone help me please? So this is really easy on paper, but I can't translate it into SQL. Lets say you have some ...
-1
votes
1answer
27 views

Prioritised table query

I'm looking for a way to query two tables at once but ... I want the query to first run against Table A and then if Table A returns no matching records to query Table B. There many well be instances ...
0
votes
2answers
65 views

Executing stored procedures asynchronously using tasks in SSIS

I have a stored procedure which is calling 20-30 stored procedures. I need these stored procedures to run asynchronously. From what I have read, it seems like executing those stored procedures using ...
3
votes
2answers
176 views

sql querying for a pattern

I was hoping someone could steer me in the right direction with this one. I have a table that has student grades. Create Table StudentGrades ( StudentID references Student(StudentID) int not null, ...
0
votes
1answer
71 views

How to merge two databases, both with primary keys

I have two databases, both with the same tables, but the data is out of sync. I need to add data from database A to database B, but not overwrite any data in B that exists in A, instead add it with a ...
0
votes
0answers
38 views

delete query performance issue on mysql

I have two tables table1, table2 where table2 is a subset of table1 (basically created with the query create table table2 as select * from table1 where .....) Now, I am trying to run the below query. ...
0
votes
1answer
21 views

Optimize Query in MySQL

I've following tables with respective fields 1) wi_individual_g(ind_id,ind_is_recepient,ind_deleted,ind_district_id,...) - Around 200K data 2) wi_individual_p(ind_id,prg_id,...) Around 250K data 3) ...
0
votes
0answers
42 views

Access 2010 delete and append queries run partially then give “Read Only” error

I have a number of append and delete queries written in Access 2010 that link to and modify tables on our SharePoint 2010 site. These queries have run without problem for months, but suddenly they've ...
1
vote
2answers
31 views

How can I map a login to a database using T-SQL (not SSMS)

I'm writing a program that requires me to assign all permissions and everything in code. I have gotten stuck in this part: I just want to do the equivalent of clicking the little box under "Map" ...
1
vote
3answers
66 views

Script to drop all databases except one not working

I'm trying to drop all databases but one on my test server. For this, I'm using the below script but it gives error: EXEC sp_MSforeachdb ' USE [?] IF (DB_ID(''?'') > 4 AND DB_NAME()!=''ABC'') ...
1
vote
0answers
54 views

Is there a way to improve this query further?

I've got a query that over time I've been improving. Originally it simply selected * from the records table, and the data was formatted on the webapp side. But that was slow and I wanted to make ...
0
votes
1answer
42 views

SQL select with unique_list - losing an item

I have an oracle database and I have the following problem: If I make an select like this: SELECT fk_proddes_id,fk_id_cod_deseu, LISTAGG (TO_CHAR (cod_eliminare), ',') WITHIN ...
0
votes
0answers
58 views

Why is MySQL not saving inserted data?

So I have the following insert statement that I run through MySQL Workbench: INSERT INTO PeakOnlineCount (time_stamp, LocationId, Count) values ('2015-03-26', 0, 1) If I run the following query: ...
0
votes
1answer
37 views

oracle database - sql for counting the columns with the same id with the one in current row

I have an oracle database and a few tables and with this query: select rap.DENUMIRE,a3.ID,op.FK_ID, (SELECT COUNT(*) FROM ANEXA3_TABEL1_ROWS) AS count from ANEXA3_TABEL1_ROWS a3 inner join ...
0
votes
0answers
21 views

DB space vs query complexity tradeoff

I have a dilemma - Right now I have a query that I do constantly - joining 5 tables on different columns, calculate stuff per row, distinct on that, and do that with offset and limit (pagination). I ...
2
votes
1answer
36 views

How to keep updates (diffs) of some entity in the database

What is the best way to keep updates (diffs) of the some entity in the database? At StackOverflow we can edit questions and answers. And then we can look at any revision of the question or answer we ...
3
votes
2answers
46 views

Getting Machine name from where stored procedure was last executed

Is there any way to find out machine IP or machine name from where a particular stored procedure is executed in SQL Server 2005? We have a client server architecture with .NET as a front end and SQL ...
0
votes
0answers
38 views

difference between phpmyadmin and php PDO

I'm running a query in Phpmyadmin and also execute the same query with php PDO but results are not equal. What could be wrong ? This is query : SELECT dim_date_key as date ,dim_jdate_key as jdate, ...
0
votes
1answer
28 views

Postgres - count of GROUP BY

I am trying to determine the total number of contributors in an image database who have contributed at least five images. The following query will give me counts of images by contributor, but that's ...
0
votes
0answers
41 views

trying to a lag a column in SQLite

Sorry if this is too basic for this forum, but I have had no luck in StackOverflow. I am a newbie to SQL. I am using SQLite. I have a table where rows represent individuals in a point in time. I am ...
0
votes
0answers
22 views

Why is my Make Table query pasting 0 rows?

I have 3 tables that I am trying to merge into 1 table using a make table query. They have thousands of rows of information. They all have multiple columns, but the first column on each will contain ...
-2
votes
2answers
32 views

SQL SUM of different product groups in one query?

I have a table with the following columns: PRODUCT_ID | PRODUCT_NAME | GROUP_ID | GROUP_NAME | PRICE GROUP_ID and GROUP_NAME are not UNIQUE. Can I make a selection which just returns each GROUP_ID ...
1
vote
1answer
184 views

How to set IN values on an update (SQL Server)? [closed]

Quick question, I did try to google but couldn't find an answer. I have 3 separate queries here, I want to do SET IN ('price1', 'price2', 'price3') but when I tried I got an error. How would I do ...
0
votes
0answers
11 views

Query to get the values of a field from all tables

I have an Access database with several tables. Some tables have a column called Action. For some of my records, the Action is blank, others have text in it. How can I make a list of all records in ...
6
votes
2answers
92 views

What is an Oracle ASSEMBLY?

What is an Oracle ASSEMBLY, in the context of Oracle SQL? It's listed in GV$SQLCOMMAND. Although GV$SQLCOMMAND is not a perfect guide to "real" SQL statements - for example it has UPSERT instead of ...
1
vote
1answer
136 views

How to return values from “upsert” query in case when UPDATE has been performed?

WITH upsert AS (UPDATE tbl SET a = 2 WHERE a = 1 RETURNING tbl.*) INSERT INTO tbl (a) SELECT 1 WHERE NOT EXISTS( SELECT * FROM upsert ) RETURNING * This "upsert" statement works however I would like ...
2
votes
1answer
31 views

Search for authors whose records are separated by at least X days

I am using Postgres and have a table which contains metadata about several million images - many of which have the same author. I am looking for contributors who add content regularly - not people ...
0
votes
1answer
45 views

Oracle can't drop index with . in name?

I am trying to drop an index that has a dot(.) in its name and can't seem to do it (I'm logged in as the SYSTEM user, which owns the index). Any ideas? SQL> select index_name,owner,status from ...
0
votes
0answers
20 views

Oracle Linked Server on SQL partial insert

I am using OpenQuery to insert into OracleLinkedServer, inside a while loop from while ( @id <= @cnt) Begin INSERT INTO OPENQUERY ( MY_LINKED_SERVER,'SELECT COL1, ...
1
vote
1answer
42 views

db schema for a object with multiple foreign key tables

I have a question table which contains question_id, question_text and choice_type_id ( say freetext, binary choice and multiple choices ) Now, the question table (which has question_id, ...
1
vote
2answers
109 views

How to enforce data consistency across one-to-one relationship in an inheritance hierarchy

Consider this design for a typical school database: Person: ----------------- FirstName LastName SocialSecurityNumber Phone Email Student: ----------------- Grade Teacher: ----------------- ...
2
votes
1answer
86 views

Updating table of versioned rows with historical records in PostgreSQL

I have a master table of versioned rows: CREATE TABLE master ( id SERIAL PRIMARY KEY, rec_id integer, val text, valid_on date[], valid_during daterange ); INSERT INTO master ...