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,302
questions
399
votes
9
answers
582k
views
Find records from one table which don't exist in another
I've got the following two tables (in MySQL):
Phone_book
+----+------+--------------+
| id | name | phone_number |
+----+------+--------------+
| 1 | John | 111111111111 |
+----+------+--------------+...
- 131k
396
votes
12
answers
603k
views
How to DROP multiple columns with a single ALTER TABLE statement in SQL Server?
I would like to write a single SQL command to drop multiple columns from a single table in one ALTER TABLE statement.
From MSDN's ALTER TABLE documentation...
DROP { [CONSTRAINT] constraint_name | ...
- 44.3k
394
votes
22
answers
499k
views
SQL Server - stop or break execution of a SQL script
Is there a way to immediately stop execution of a SQL script in SQL server, like a "break" or "exit" command?
I have a script that does some validation and lookups before it starts doing inserts, and ...
- 87.4k
393
votes
8
answers
815k
views
Extract date (yyyy/mm/dd) from a timestamp in PostgreSQL
I want to extract just the date part from a timestamp in PostgreSQL.
I need it to be a postgresql DATE type so I can insert it into another table that expects a DATE value.
For example, if I have ...
- 3,997
392
votes
6
answers
237k
views
What does SQL clause "GROUP BY 1" mean?
Someone sent me a SQL query where the GROUP BY clause consisted of the statement: GROUP BY 1.
This must be a typo right? No column is given the alias 1. What could this mean? Am I right to assume ...
- 21.9k
392
votes
6
answers
495k
views
What is the difference between SQL, PL-SQL and T-SQL?
What is the difference between SQL, PL-SQL and T-SQL?
Can anyone explain what the differences between these three are, and provide scenarios where each would be relevantly used?
- 13.4k
390
votes
33
answers
593k
views
What's the difference between TRUNCATE and DELETE in SQL
What's the difference between TRUNCATE and DELETE in SQL?
If your answer is platform specific, please indicate that.
- 51.9k
387
votes
10
answers
479k
views
How can I return pivot table output in MySQL?
If I have a MySQL table looking something like this:
company_name
action
pagecount
Company A
PRINT
3
Company A
PRINT
2
Company A
PRINT
3
Company B
EMAIL
Company B
PRINT
2
Company B
PRINT
2
...
- 5,053
387
votes
8
answers
495k
views
What represents a double in sql server?
I have a couple of properties in C# which are double and I want to store these in a table in SQL Server, but noticed there is no double type, so what is best to use, decimal or float?
This will store ...
- 46.1k
386
votes
23
answers
1.3m
views
how do I query sql for a latest record date for each user
I have a table that is a collection entries as to when a user was logged on.
username, date, value
--------------------------
brad, 1/2/2010, 1.1
fred, 1/3/2010, 1.0
bob, 8/4/2009,...
- 6,009
386
votes
2
answers
901k
views
Cast from VARCHAR to INT - MySQL
My Current Data for
SELECT PROD_CODE FROM `PRODUCT`
is
PROD_CODE
2
5
7
8
22
10
9
11
I have tried all the four queries and none work. (Ref)
SELECT CAST(PROD_CODE) AS INT FROM PRODUCT;
SELECT CAST(...
- 22.6k
386
votes
10
answers
1.1m
views
Altering column size in SQL Server
How to change the column size of the salary column in the employee table from numeric(18,0) to numeric(22,5)
- 7,299
386
votes
24
answers
189k
views
Are there any disadvantages to always using nvarchar(MAX)?
In SQL Server 2005, are there any disadvantages to making all character fields nvarchar(MAX) rather than specifying a length explicitly, e.g. nvarchar(255)? (Apart from the obvious one that you aren't ...
- 6,453
383
votes
9
answers
237k
views
Difference between "read commited" and "repeatable read" in SQL Server
I think the above isolation levels are so alike. Could someone please describe with some nice examples what the main difference is?
- 5,986
383
votes
4
answers
237k
views
In Postgresql, force unique on combination of two columns
I would like to set up a table in PostgreSQL such that two columns together must be unique. There can be multiple values of either value, so long as there are not two that share both.
For instance:
...
- 39.4k
383
votes
7
answers
168k
views
T-SQL Cast versus Convert
What is the general guidance on when you should use CAST versus CONVERT? Is there any performance issues related to choosing one versus the other? Is one closer to ANSI-SQL?
- 70.5k
382
votes
15
answers
374k
views
SQL how to make null values come last when sorting ascending
I have a SQL table with a datetime field. The field in question can be null. I have a query and I want the results sorted ascendingly by the datetime field, however I want rows where the datetime ...
- 17.3k
382
votes
6
answers
174k
views
Dynamic SELECT TOP @var In SQL Server
How can I have a dynamic variable setting the amount of rows to return in SQL Server? Below is not valid syntax in SQL Server 2005+:
DECLARE @count int
SET @count = 20
SELECT TOP @count * FROM ...
- 34.4k
382
votes
10
answers
586k
views
Not equal <> != operator on NULL
Could someone please explain the following behavior in SQL?
SELECT * FROM MyTable WHERE MyColumn != NULL (0 Results)
SELECT * FROM MyTable WHERE MyColumn <> NULL (0 Results)
SELECT * FROM ...
- 46.6k
381
votes
9
answers
849k
views
Best way to do multi-row insert in Oracle?
I'm looking for a good way to perform multi-row inserts into an Oracle 9 database. The following works in MySQL but doesn't seem to be supported in Oracle.
INSERT INTO TMP_DIM_EXCH_RT
(EXCH_WH_KEY, ...
- 5,131
381
votes
12
answers
1.6m
views
SQL Inner-join with 3 tables?
I'm trying to join 3 tables in a view; here is the situation:
I have a table that contains information of students who are applying to live on this College Campus. I have another table that lists the ...
- 4,327
379
votes
13
answers
507k
views
How do I obtain a Query Execution Plan in SQL Server?
In Microsoft SQL Server how can I get a query execution plan for a query / stored procedure?
- 85.8k
378
votes
10
answers
373k
views
Connect different Windows User in SQL Server Management Studio (2005 or later)
Is there a way in SQL Server Management Studio 2005 (or later) to change the Windows Authentication user (as you could in SQL Server 2000 and older)?
This is the general connection properties dialog(...
- 3,781
377
votes
16
answers
1.1m
views
Update statement with inner join on Oracle
I have a query which works fine in MySQL, but when I run it on Oracle I get the following error:
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 - "SQL command not properly ...
- 4,187
376
votes
8
answers
1.1m
views
How to get first character of a string in SQL?
I have a SQL column with a length of 6. Now want to take only the first char of that column. Is there any string function in SQL to do this?
- 32.5k
375
votes
12
answers
194k
views
Simulating group_concat MySQL function in Microsoft SQL Server 2005?
I'm trying to migrate a MySQL-based app over to Microsoft SQL Server 2005 (not by choice, but that's life).
In the original app, we used almost entirely ANSI-SQL compliant statements, with one ...
- 7,117
374
votes
10
answers
286k
views
Update multiple rows in same query using PostgreSQL
I'm looking to update multiple rows in PostgreSQL in one statement. Is there a way to do something like the following?
UPDATE table
SET
column_a = 1 where column_b = '123',
column_a = 2 where ...
- 17.6k
374
votes
4
answers
1.1m
views
Reason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause [duplicate]
I got an error -
Column 'Employee.EmpID' is invalid in the select list because it is
not contained in either an aggregate function or the GROUP BY clause.
select loc.LocationID, emp.EmpID
from ...
- 5,853
374
votes
19
answers
492k
views
Select n random rows from SQL Server table
I've got a SQL Server table with about 50,000 rows in it. I want to select about 5,000 of those rows at random. I've thought of a complicated way, creating a temp table with a "random number" column, ...
- 19.2k
368
votes
16
answers
429k
views
Equals(=) vs. LIKE
When using SQL, are there any benefits of using = in a WHERE clause instead of LIKE?
Without any special operators, LIKE and = are the same, right?
- 12.2k
367
votes
22
answers
957k
views
How to select date without time in SQL
When I select date in SQL it is returned as 2011-02-25 21:17:33.933. But I need only the Date part, that is 2011-02-25. How can I do this?
- 8,255
366
votes
25
answers
388k
views
Entity Framework. Delete all rows in table
How can I quickly remove all rows in the table using Entity Framework?
I am currently using:
var rows = from o in dataDb.Table
select o;
foreach (var row in rows)
{
dataDb.Table.Remove(...
- 4,049
366
votes
17
answers
318k
views
SET NOCOUNT ON usage
Inspired by this question where there are differing views on SET NOCOUNT...
Should we use SET NOCOUNT ON for SQL Server? If not, why not?
What it does Edit 6, on 22 Jul 2011
It suppresses the "xx ...
365
votes
22
answers
1.1m
views
How do I check if a column is empty or null in MySQL?
I have a column in a table which might contain null or empty values. How do I check if a column is empty or null in the rows present in a table?
(e.g. null or '' or ' ' or ' ' and ...)
- 25.8k
365
votes
11
answers
531k
views
What's the fastest way to do a bulk insert into Postgres? [closed]
I need to programmatically insert tens of millions of records into a Postgres database. Presently, I'm executing thousands of insert statements in a single query.
Is there a better way to do this, ...
- 25.1k
362
votes
26
answers
568k
views
MySQL Cannot Add Foreign Key Constraint
So I'm trying to add Foreign Key constraints to my database as a project requirement and it worked the first time or two on different tables, but I have two tables on which I get an error when trying ...
- 3,921
362
votes
9
answers
470k
views
How to find duplicate records in PostgreSQL
I have a PostgreSQL database table called "user_links" which currently allows the following duplicate fields:
year, user_id, sid, cid
The unique constraint is currently the first field called "id", ...
- 6,747
357
votes
10
answers
946k
views
How to check for Is not Null And Is not Empty string in SQL server?
How can we check in a SQL Server WHERE condition whether the column is not null and not the empty string ('')?
- 3,723
357
votes
8
answers
339k
views
Best practices for SQL varchar column length [closed]
Every time is set up a new SQL table or add a new varchar column to an existing table, I am wondering one thing: what is the best value for the length.
So, lets say, you have a column called name of ...
- 10.8k
355
votes
3
answers
411k
views
Can I use multiple "with"?
Just for example:
With DependencedIncidents AS
(
SELECT INC.[RecTime],INC.[SQL] AS [str] FROM
(
SELECT A.[RecTime] As [RecTime],X.[SQL] As [SQL] FROM [EventView] AS A
CROSS ...
- 33.2k
355
votes
20
answers
395k
views
Get records with max value for each group of grouped SQL results
How do you get the rows that contain the max value for each grouped set?
I've seen some overly-complicated variations on this question, and none with a good answer. I've tried to put together the ...
- 179k
355
votes
7
answers
370k
views
Can table columns with a Foreign Key be NULL?
I have a table which has several ID columns to other tables.
I want a foreign key to force integrity only if I put data in there. If I do an update at a later time to populate that column, then it ...
- 3,746
354
votes
9
answers
292k
views
Postgresql GROUP_CONCAT equivalent?
I have a table and I'd like to pull one row per id with field values concatenated.
In my table, for example, I have this:
TM67 | 4 | 32556
TM67 | 9 | 98200
TM67 | 72 | 22300
TM99 | 2 | 23009
TM99 ...
- 10.3k
353
votes
12
answers
402k
views
MySQL LIKE IN()?
My current query looks like this:
SELECT * FROM fiberbox f WHERE f.fiberBox LIKE '%1740 %' OR f.fiberBox LIKE '%1938 %' OR f.fiberBox LIKE '%1940 %'
I did some looking around and can't find anything ...
- 10.5k
351
votes
8
answers
422k
views
Postgres manually alter sequence
I'm trying to set a sequence to a specific value.
SELECT setval('payments_id_seq'), 21, true;
This gives an error:
ERROR: function setval(unknown) does not exist
Using ALTER SEQUENCE doesn't seem ...
- 27.3k
351
votes
7
answers
468k
views
INSERT with SELECT
I have a query that inserts using a SELECT statement:
INSERT INTO courses (name, location, gid)
SELECT name, location, gid
FROM courses
WHERE cid = $cid
Is it possible to only select "name, ...
- 3,521
347
votes
16
answers
704k
views
How to check if a stored procedure exists before creating it
I have a SQL script that has to be run every time a client executes the "database management" functionality. The script includes creating stored procedures on the client database. Some of these ...
- 3,555
347
votes
7
answers
374k
views
SQL keys, MUL vs PRI vs UNI
What is the difference between MUL, PRI and UNI in MySQL?
I'm working on a MySQL query, using the command:
desc mytable;
One of the fields is shown as being a MUL key, others show up as UNI or PRI....
- 14k
347
votes
19
answers
861k
views
Cannot simply use PostgreSQL table name ("relation does not exist")
I'm trying to run the following PHP script to do a simple database query:
$db_host = "localhost";
$db_name = "showfinder";
$username = "user";
$password = "password";
$dbconn = pg_connect("host=$...
- 5,100
347
votes
8
answers
356k
views
How can I add a column that doesn't allow nulls in a Postgresql database?
I'm adding a new, "NOT NULL" column to my Postgresql database using the following query (sanitized for the Internet):
ALTER TABLE mytable ADD COLUMN mycolumn character varying(50) NOT NULL;
Each ...
- 16k