What kind of field is this? The IN operator cannot be used with a single field, but is meant to be used in subqueries or with predefined lists:

    -- subquery
    SELECT a FROM x WHERE x.b NOT IN (SELECT b FROM y);
    -- predefined list
    SELECT a FROM x WHERE x.b NOT IN (1, 2, 3, 6);

If you are searching a string, go for the LIKE operator (but this will be slow):

    -- Finds all rows where a does not contain &quot;text&quot;
    SELECT * FROM x WHERE x.a NOT LIKE &#39;%text%&#39;;

If you restrict it so that the string you are searching for has to start with the given string, it can use indices (if there is an index on that field) and be reasonably fast:

    -- Finds all rows where a does not start with &quot;text&quot;
    SELECT * FROM x WHERE x.a NOT LIKE &#39;text%&#39;;

`SELECT * FROM table WHERE field1 NOT LIKE &#39;%$x%&#39;;`   (Make sure you escape $x properly beforehand to avoid SQL injection)

Edit: `NOT IN` does something a bit different - your question isn&#39;t totally clear so pick which one to use.   `LIKE &#39;xxx%&#39;` can use an index.  `LIKE &#39;%xxx&#39;` or `LIKE &#39;%xxx%&#39;` can&#39;t.

How do I set up a shell script to execute from the Mac OSX dock? It seems that simply creating a shortcut will open the file in my editor. Is there  a flag I need to set somewhere to tell it to run instead of opening it for editing?

Executing Shell Scripts from the OS X Dock?

Sometimes whenever I write a program in Linux and it crashes due to a bug of some sort, it will become an uninterruptible process and continue running forever until I restart my computer (even if I log out).  My questions are:

 - What causes a process to become uninterruptible?
 - How do I stop that from happening?
 - This is probably a dumb question, but is there any way to interrupt it without restarting my computer?

What is an uninterruptible process?

I want to find an SQL query to find rows where field1 does not contain $x. How can I do this?

SQL Query Where Field DOES NOT Contain $x

<p>I want to find an SQL query to find rows where field1 does not contain $x. How can I do this?</p>


I know the statement:

    create table xyz_new as select * from xyz;

Which copies the structure and the data, but what if I just want the structure?


How can I create a copy of an Oracle table without copying the data?

I have been working with T-SQL in MS SQL for some time now and somehow whenever I have to insert data into a table I tend to use syntax:

    INSERT INTO myTable &lt;something here&gt;

I understand that keyword `INTO` is optional here and I do not have to use it but somehow it grew into habit in my case.

My question is: 

  - Are there any implications of using `INSERT` syntax versus `INSERT INTO`?
  - Which one complies fully with the standard?
  - Are they both valid in other implementations of SQL standard?



INSERT vs INSERT INTO

The UPSERT operation either updates or inserts a row in a table, depending if the table already has a row that matches the data:

    if table t has a row exists that has key X:
        update t set mystuff... where mykey=X
    else
        insert into t mystuff...

Since Oracle doesn&#39;t have a specific UPSERT statement, what&#39;s the best way to do this?

Oracle: how to UPSERT (update or insert into a table?)

I have seen various rules for naming stored procedures. 

Some people prefix the sproc name with usp_, others with an abbreviation for the app name, and still others with an owner name. You shouldn&#39;t use sp_ in SQL Server unless you really mean it.

Some start the proc name with a verb (Get, Add, Save, Remove). Others emphasize the entity name(s).

On a database with hundreds of sprocs, it can be very hard to scroll around and find a suitable sproc when you think one already exists. Naming conventions can make locating a sproc easier.

Do you use a naming convention? Please describe it, and explain why you prefer it over other choices.

**Summary of replies:**  

  - Everybody seems to advocate consistency of naming, that it might be more important for everyone to use the same naming convention than which particular one is used.
  - Prefixes: While a lot of folks use usp_ or something similar (but rarely sp_), many others use database or app name. One clever DBA uses gen, rpt and tsk to distinguish general CRUD sprocs from those used for reporting or tasks.
  - Verb + Noun seems to be slightly more popular than Noun + Verb. Some people use the SQL keywords (Select, Insert, Update, Delete) for the verbs, while others use non-SQL verbs (or abbreviations for them) like Get and Add. Some distinguish between singluar and plural nouns to indicate whether one or many records are being retrieved.
  - An additional phrase is suggested at the end, where appropriate. GetCustomerById, GetCustomerBySaleDate.
  - Some people use underscores between the name segments, and some avoid underscores. app_ Get_Customer vs. appGetCustomer -- I guess it&#39;s a matter of readability.
  - Large collections of sprocs can be segregated into Oracle packages or Management Studio (SQL Server) solutions and projects, or SQL Server schemas.
  - Inscrutable abbreviations should be avoided.

**Why I choose the answer I did:** There are SO many good responses. Thank you all! As you can see, it would be very hard to choose just one. The one I chose resonated with me. I have followed the same path he describes -- trying to use Verb + Noun and then not being able to find all of the sprocs that apply to Customer. 

Being able to locate an existing sproc, or to determine if one even exists, is very important. Serious problems can arise if someone inadvertently creates a duplicate sproc with another name. 

Since I generally work on very large apps with hundreds of sprocs, I have a preference for the easiest-to-find naming method. For a smaller app, I might advocate Verb + Noun, as it follows the general coding convention for method names.

He also advocates prefixing with app name instead of the not very useful usp_. As several people pointed out, sometimes the database contains sprocs for multiple apps. So, prefixing with app name helps to segregate the sprocs AND helps DBAs and others to determine which app the sproc is used for.

What is your naming convention for stored procedures?

I have a table that has a `processed_timestamp` column -- if a record has been processed then that field contains the datetime it was processed, otherwise it is null.

I want to write a query that returns two rows:

    NULL        xx -- count of records with null timestamps
    NOT NULL    yy -- count of records with non-null timestamps

Is that possible?

**Update:** The table is quite large, so efficiency is important. I could just run two queries to calculate each total separately, but I want to avoid hitting the table twice if I can avoid it.

How do I return my records grouped by NULL and NOT NULL?

I am writing a stored procedure where I have an input parameter called *my_size* that is an *INTEGER*. I want to be able to use it in a `LIMIT` clause in a `SELECT` statement. Apparently this is not supported, is there a way to work around this?

    # I want something like:
    SELECT * FROM some_table LIMIT my_size;
    
    # Instead of hardcoding a permanent limit:
    SELECT * FROM some_table LIMIT 100;

Using variable in a LIMIT clause in MySQL

I&#39;m not sure how password hashing works (will be implementing it later), but need to create database schema now.

I&#39;m thinking of limiting passwords to 4-20 characters, but as I understand after encrypting hash string will be of different length.

So, how to store these passwords in the database?

What data type to use for hashed password field and what length?

How do I take an efficient simple random sample in SQL? The database in question is running MySQL; my table is at least 200,000 rows, and I want a simple random sample of about 10,000.

The &quot;obvious&quot; answer is to:

    SELECT * FROM table ORDER BY RAND() LIMIT 10000

For large tables, that&#39;s too slow: it calls `RAND()` for every row (which already puts it at O(n)), and sorts them, making it O(n lg n) at best. Is there a way to do this faster than O(n)?

*Note*: As Andrew Mao points out in the comments, If you&#39;re using this approach on SQL Server, you should use the T-SQL function `NEWID()`, because RAND() [may return the same value for all rows](http://msdn.microsoft.com/en-us/library/ms177610.asp).

*EDIT: 5 YEARS LATER*

I ran into this problem again with a bigger table, and ended up using a version of @ignorant&#39;s solution, with two tweaks:

 * Sample the rows to 2-5x my desired sample size, to cheaply `ORDER BY RAND()`
 * Save the result of `RAND()` to an indexed column on every insert/update. (If your data set isn&#39;t very update-heavy, you may need to find another way to keep this column fresh.)

To take a 1000-item sample of a table, I count the rows and sample the result down to, on average, 10,000 rows with the the frozen_rand column:


    SELECT COUNT(*) FROM table; -- Use this to determine rand_low and rand_high

      SELECT *
        FROM table
       WHERE frozen_rand BETWEEN %(rand_low)s AND %(rand_high)s
    ORDER BY RAND() LIMIT 1000

(My actual implementation involves more work to make sure I don&#39;t undersample, and to manually wrap rand_high around, but the basic idea is &quot;randomly cut your N down to a few thousand.&quot;)

While this makes some sacrifices, it allows me to sample the database down using an index scan, until it&#39;s small enough to `ORDER BY RAND()` again.

Simple Random Samples from a Sql database

I acquired a database from another developer.  He didn&#39;t use auto_incrementers on any tables.  They all have primary key ID&#39;s, but he did all the incrementing manually, in code.

Can I turn those into Auto_incrementers now?

---

Wow, very nice, thanks a ton. It worked without a hitch on one of my tables. But a second table, i&#39;m getting this error...Error on rename of &#39;.\DBNAME\#sql-6c8_62259c&#39; to &#39;.\DBNAME\dealer_master_events&#39;

make an ID in a mysql table auto_increment (after the fact)

I would like to construct a query that displays all the results in a table, but is offset by 5 from the start of the table. As far as I can tell, MySQL&#39;s `LIMIT` requires a limit as well as an offset. Is there any way to do this?

Content Type	Original Author	Original Content on Stackoverflow
Question	zuk1	View Question on Stackoverflow
Solution 1 - Sql	Vegard Larsen	View Answer on Stackoverflow
Solution 2 - Sql	Greg	View Answer on Stackoverflow

SQL Query Where Field DOES NOT Contain $x

Sql Problem Overview

Sql Solutions

Solution 1 - Sql

Solution 2 - Sql

What is an uninterruptible process?

Executing Shell Scripts from the OS X Dock?

Attributions