Questions tagged [sql]
Structured Query Language (SQL) is a language for querying databases. Questions should include code examples, table structure, sample data, and a tag for the DBMS implementation (e.g. MySQL, PostgreSQL, Oracle, MS SQL Server, IBM DB2, etc.) being used. If your question relates solely to a specific DBMS (uses specific extensions/features), use that DBMS's tag instead. Answers to questions tagged with SQL should use ISO/IEC standard SQL.
671,329
questions
455
votes
25
answers
2.8m
views
SQL query to select dates between two dates
I have a start_date and end_date. I want to get the list of dates in between these two dates. Can anyone help me pointing the mistake in my query.
select Date,TotalAllowance
from Calculation
where ...
- 8,255
455
votes
6
answers
365k
views
How to use GROUP BY to concatenate strings in MySQL?
Basically the question is how to get from this:
foo_id foo_name
1 A
1 B
2 C
to this:
foo_id foo_name
1 A B
2 C
- 18.3k
455
votes
29
answers
805k
views
Is there a combination of "LIKE" and "IN" in SQL?
In SQL I (sadly) often have to use "LIKE" conditions due to databases that violate nearly every rule of normalization. I can't change that right now. But that's irrelevant to the question.
Further, I ...
- 15.7k
453
votes
12
answers
359k
views
You can't specify target table for update in FROM clause
I have a simple mysql table:
CREATE TABLE IF NOT EXISTS `pers` (
`persID` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(35) NOT NULL,
`gehalt` int(11) NOT NULL,
`chefID` int(11) DEFAULT ...
- 11k
451
votes
16
answers
331k
views
Is a view faster than a simple query?
Is a
select * from myView
faster than the query itself to create the view (in order to have the same resultSet):
select * from ([query to create same resultSet as myView])
?
It's not totally ...
- 49.6k
449
votes
20
answers
501k
views
Multiple Updates in MySQL
I know that you can insert multiple rows at once, is there a way to update multiple rows at once (as in, in one query) in MySQL?
Edit:
For example I have the following
Name id Col1 Col2
Row1 1 ...
- 110k
449
votes
17
answers
454k
views
How to create a MySQL hierarchical recursive query?
I have a MySQL table which is as follows:
id
name
parent_id
19
category1
0
20
category2
19
21
category3
20
22
category4
21
...
...
...
Now, I want to have a single MySQL query to which I simply ...
- 4,735
449
votes
8
answers
1.1m
views
Difference between numeric, float and decimal in SQL Server
What are the differences between numeric, float and decimal datatypes and which should be used in which situations?
For any kind of financial transaction (e.g. for salary field), which one is ...
- 26.2k
446
votes
16
answers
1.3m
views
How to SELECT FROM stored procedure
I have a stored procedure that returns rows:
CREATE PROCEDURE MyProc
AS
BEGIN
SELECT * FROM MyTable
END
My actual procedure is a little more complicated, which is why a stored procedure is ...
- 26.3k
445
votes
6
answers
565k
views
Update a column value, replacing part of a string
I have a table with the following columns in a MySQL database
[id, url]
And the URLs are like:
http://domain1.example/images/img1.jpg
I want to update all the URLs to another domain
http://domain2....
- 32.3k
445
votes
28
answers
473k
views
Remove duplicate rows in MySQL
I have a table with the following fields:
id (Unique)
url (Unique)
title
company
site_id
Now, I need to remove rows having same title, company and site_id. One way to do it will be using the ...
- 4,915
445
votes
24
answers
460k
views
Get record counts for all tables in MySQL database
Is there a way to get the count of rows in all tables in a MySQL database without running a SELECT count() on each table?
- 4,829
445
votes
17
answers
773k
views
What's the difference between VARCHAR and CHAR?
What's the difference between VARCHAR and CHAR in MySQL?
I am trying to store MD5 hashes.
- 13.4k
445
votes
10
answers
1.2m
views
How to set variable from a SQL query?
I'm trying to set a variable from a SQL query:
declare @ModelID uniqueidentifer
Select @ModelID = select modelid from models
where areaid = 'South Coast'
Obviously I'm not doing this right as it ...
- 5,813
445
votes
5
answers
187k
views
Entity Framework VS LINQ to SQL VS ADO.NET with stored procedures? [closed]
How would you rate each of them in terms of:
Performance
Speed of development
Neat, intuitive, maintainable code
Flexibility
Overall
I like my SQL and so have always been a die-hard fan of ADO.NET ...
- 13.3k
442
votes
29
answers
1.1m
views
Getting "Lock wait timeout exceeded; try restarting transaction" even though I'm not using a transaction
I'm running the following MySQL UPDATE statement:
mysql> update customer set account_import_id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
I'm not using a ...
- 44.4k
440
votes
14
answers
775k
views
SQL to find the number of distinct values in a column
I can select all the distinct values in a column in the following ways:
SELECT DISTINCT column_name FROM table_name;
SELECT column_name FROM table_name GROUP BY column_name;
But how do I get the row ...
- 49.1k
440
votes
5
answers
645k
views
Hibernate show real SQL [duplicate]
if I set
<property name="show_sql">true</property>
in my hibernate.cfg.xml configuration file in the console I can see the SQL.
But it's not real SQL... Can I see the SQL code that will ...
- 9,052
438
votes
37
answers
956k
views
Error Code: 2013. Lost connection to MySQL server during query
I got the Error Code: 2013. Lost connection to MySQL server during query error when I tried to add an index to a table using MySQL Workbench.
I also noticed that it appears whenever I run long query.
...
- 11k
437
votes
15
answers
371k
views
How to get the insert ID in JDBC?
I want to INSERT a record in a database (which is Microsoft SQL Server in my case) using JDBC in Java. At the same time, I want to obtain the insert ID. How can I achieve this using JDBC API?
- 8,246
436
votes
16
answers
560k
views
How can I search (case-insensitive) in a column using LIKE wildcard?
I looked around some and didn't find what I was after so here goes.
SELECT * FROM trees WHERE trees.`title` LIKE '%elm%'
This works fine, but not if the tree is named Elm or ELM etc...
How do I ...
- 9,174
436
votes
11
answers
909k
views
Get day of week in SQL Server 2005/2008
If I have a date 01/01/2009, I want to find out what day it was e.g. Monday, Tuesday, etc...
Is there a built-in function for this in SQL Server 2005/2008? Or do I need to use an auxiliary table?
aaaa
435
votes
6
answers
298k
views
How to perform OR condition in django queryset?
I want to write a Django query equivalent to this SQL query:
SELECT * from user where income >= 5000 or income is NULL.
How to construct the Django queryset filter?
User.objects.filter(...
- 6,975
434
votes
4
answers
549k
views
How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?
Can anyone explain how to implement one-to-one, one-to-many and many-to-many relationships while designing tables with some examples?
- 23.8k
434
votes
17
answers
985k
views
Selecting data from two different servers in SQL Server
How can I select data in the same query from two different databases that are on two different servers in SQL Server?
Don Of Qau
433
votes
8
answers
778k
views
What is the difference between Views and Materialized Views in Oracle?
What is the difference between Views and Materialized Views in Oracle?
- 81.1k
430
votes
22
answers
1.7m
views
Get all table names of a particular database by SQL query?
I am working on application which can deal with multiple database servers like "MySQL" and "MS SQL Server".
I want to get tables' names of a particular database using a general ...
- 18.4k
429
votes
35
answers
1.4m
views
How can I select the first day of a month in SQL?
How can one select the first day of the month of a given DateTime variable?
I know it's quite easy to do using this kind of code:
select CAST(CAST(YEAR(@mydate) AS VARCHAR(4))
+ '/' + CAST(MONTH(@...
- 32k
429
votes
9
answers
802k
views
Select statement to find duplicates on certain fields
Can you help me with SQL statements to find duplicates on multiple fields?
For example, in pseudo code:
select count(field1,field2,field3)
from table
where the combination of field1, field2, ...
- 8,030
426
votes
21
answers
1.2m
views
Error 1046 No database selected, how to resolve?
Error
SQL query:
--
-- Database: `work`
--
-- --------------------------------------------------------
--
-- Table structure for table `administrators`
--
CREATE TABLE IF NOT EXISTS `administrators` ...
- 4,277
426
votes
11
answers
336k
views
Best database field type for a URL
I need to store a url in a MySQL table. What's the best practice for defining a field that will hold a URL with an undetermined length?
- 7,936
422
votes
15
answers
814k
views
SQL Server - Return value after INSERT
I'm trying to get a the key-value back after an INSERT-statement.
Example:
I've got a table with the attributes name and id. id is a generated value.
INSERT INTO table (name) VALUES('bob');
...
- 12.1k
422
votes
19
answers
671k
views
Get table column names in MySQL?
Is there a way to grab the columns name of a table in MySQL using PHP?
- 6,248
421
votes
5
answers
580k
views
How to list active connections on PostgreSQL?
Is there a command in PostgreSQL to select active connections to a given database?
psql states that I can't drop one of my databases because there are active connections to it, so I would like to see ...
- 20.4k
421
votes
12
answers
486k
views
Best way to test if a row exists in a MySQL table
I'm trying to find out if a row exists in a table. Using MySQL, is it better to do a query like this:
SELECT COUNT(*) AS total FROM table1 WHERE ...
and check to see if the total is non-zero or is ...
- 6,477
421
votes
5
answers
275k
views
Getting the SQL from a Django QuerySet
How do I get the SQL that Django will use on the database from a QuerySet object? I'm trying to debug some strange behavior, but I'm not sure what queries are going to the database.
- 9,336
418
votes
16
answers
671k
views
Oracle: If Table Exists
I'm writing some migration scripts for an Oracle database, and was hoping Oracle had something similar to MySQL's IF EXISTS construct.
Specifically, whenever I want to drop a table in MySQL, I do ...
- 165k
417
votes
18
answers
414k
views
Reset auto increment counter in postgres
I would like to force the auto increment field of a table to some value, I tried with this:
ALTER TABLE product AUTO_INCREMENT = 1453
AND
ALTER SEQUENCE product RESTART WITH 1453;
ERROR: relation &...
- 4,641
417
votes
24
answers
867k
views
What is the equivalent of 'describe table' in SQL Server?
I have a SQL Server database and I want to know what columns and types it has. I'd prefer to do this through a query rather than using a GUI like Enterprise Manager. Is there a way to do this?
dbg
416
votes
8
answers
1.2m
views
MySQL query String contains
I've been trying to figure out how I can make a query with MySQL that checks if the value (string $haystack ) in a certain column contains certain data (string $needle), like this:
SELECT *
FROM `...
- 28.8k
416
votes
4
answers
491k
views
How can I copy data from one column to another in the same table?
Is it possible to copy data from column A to column B for all records in a table in SQL?
- 4,285
414
votes
5
answers
207k
views
Create unique constraint with null columns
I have a table with this layout:
CREATE TABLE Favorites (
FavoriteId uuid NOT NULL PRIMARY KEY,
UserId uuid NOT NULL,
RecipeId uuid NOT NULL,
MenuId uuid
);
I want to create a unique ...
- 90.2k
413
votes
14
answers
1.1m
views
SQL Server Insert if not exists
I want to insert data into my table, but insert only data that doesn't already exist in my database.
Here is my code:
ALTER PROCEDURE [dbo].[EmailsRecebidosInsert]
(@_DE nvarchar(50),
@_ASSUNTO ...
- 4,261
413
votes
5
answers
202k
views
What's the difference between CharField and TextField in Django?
The documentation says that CharField() should be used for smaller strings and TextField() should be used for larger strings.
Okay, but where is the line drawn between "small" and "...
- 4,637
412
votes
7
answers
338k
views
What datatype to use when storing latitude and longitude data in SQL databases? [duplicate]
When storing latitude or longitude data in an ANSI SQL compliant database, what datatype would be most appropriate? Should float be used, or decimal, or ...?
I'm aware that Oracle, MySql, and SQL ...
- 41.3k
409
votes
12
answers
358k
views
How can I make SQL case sensitive string comparison on MySQL?
I have a function that returns five characters with mixed case. If I do a query on this string it will return the value regardless of case.
How can I make MySQL string queries case sensitive?
- 4,125
406
votes
8
answers
1.5m
views
How do I see active SQL Server connections?
I am using SQL Server 2008 Enterprise. I want to see any active SQL Server connections, and the related information of all the connections, like from which IP address, connect to which database or ...
- 45.2k
406
votes
5
answers
1.3m
views
Combining "LIKE" and "IN" for SQL Server [duplicate]
Is it possible to combine LIKE and IN in a SQL Server-Query?
So, that this query
SELECT * FROM table WHERE column LIKE IN ('Text%', 'Link%', 'Hello%', '%World%')
Finds any of these possible matches:...
- 17.6k
403
votes
6
answers
1.6m
views
Convert INT to VARCHAR SQL
I am using Sybase and I am doing a select which returns me a column called "iftype", but its type is int and I need to convert into varchar. When I try to do the select without the convert function I ...
- 4,513
402
votes
10
answers
898k
views
How to replace a string in a SQL Server Table Column
I have a table (SQL Sever) which references paths (UNC or otherwise), but now the path is going to change.
In the path column, I have many records and I need to change just a portion of the path, ...
- 7,238