You can use the [CONCAT][1] function to do that:

    UPDATE tbl SET col=CONCAT(&#39;test&#39;,col);

If you want to get cleverer and only update columns which don&#39;t already have test prepended, try

    UPDATE tbl SET col=CONCAT(&#39;test&#39;,col)
    WHERE col NOT LIKE &#39;test%&#39;;

  [1]: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat

    UPDATE tablename SET fieldname = CONCAT(&quot;test&quot;, fieldname) [WHERE ...]

Many string update functions in MySQL seems to be working like this:
If one argument is `null`, then concatenation or other functions return `null` too.
So, to update a field with `null` value, first set it to a non-null value, such as `&#39;&#39;`

For example:

    update table set field=&#39;&#39; where field is null;
    update table set field=concat(field,&#39; append&#39;);


That&#39;s a simple one

    UPDATE YourTable SET YourColumn = CONCAT(&#39;prependedString&#39;, YourColumn);

- UPDATE table_name SET Column1 = CONCAT(&#39;newtring&#39;, table_name.Column1) where 1
- UPDATE table_name SET Column1 = CONCAT(&#39;newtring&#39;, table_name.Column2) where 1
- UPDATE table_name SET Column1 = CONCAT(&#39;newtring&#39;, table_name.Column2, &#39;newtring2&#39;) where 1

We can concat same column or also other column of the table.


I always wondered why automatic setting of the pointer to NULL after *delete* is not part of the standard. If this gets taken care of then many of the crashes due to an invalid pointer would not occur. But having said that I can think of couple of reasons why the standard would have restricted this:

&gt;    1. Performance:
&gt;
&gt;       An additional instruction could slow down the `delete` performance. 
&gt;    
&gt;    2. Could it be because of `const` pointers. 
&gt;
&gt;       Then again standard could have done something for this special case I guess. 

Does anyone know exact reasons for not allowing this? 




Why doesn&#39;t delete set the pointer to NULL?

Start with these simple classes...
----------------------------------
Let&#39;s say I have a simple set of classes like this: 

    class Bus
    {
        Driver busDriver = new Driver();
    }

    class Driver
    {
        Shoe[] shoes = { new Shoe(), new Shoe() };
    }

    class Shoe
    {
        Shoelace lace = new Shoelace();
    }

    class Shoelace
    {
        bool tied = false;
    }

A `Bus` has a `Driver`, the `Driver` has two `Shoe`s, each `Shoe` has a `Shoelace`. All very silly.  

Add an IDisposable object to Shoelace
------------------------------------
Later I decide that some operation on the `Shoelace` could be multi-threaded, so I add an `EventWaitHandle` for the threads to communicate with. So `Shoelace` now looks like this:

    class Shoelace
    {
        private AutoResetEvent waitHandle = new AutoResetEvent(false);
        bool tied = false;
        // ... other stuff ..
    }

Implement IDisposable on Shoelace
---------------------------------
But now [Microsoft&#39;s FxCop][1] will complain: *&quot;Implement IDisposable on &#39;Shoelace&#39; because it creates members of the following IDisposable types: &#39;EventWaitHandle&#39;.&quot;*

Okay, I implement `IDisposable` on `Shoelace` and my neat little class becomes this horrible mess:

    class Shoelace : IDisposable
    {
        private AutoResetEvent waitHandle = new AutoResetEvent(false);
        bool tied = false;
        private bool disposed = false;

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        ~Shoelace()
        {
            Dispose(false);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (!this.disposed)
            {
                if (disposing)
                {
                    if (waitHandle != null)
                    {
                        waitHandle.Close();
                        waitHandle = null;
                    }
                }
                // No unmanaged resources to release otherwise they&#39;d go here.
            }
            disposed = true;
        }
    }

Or (as pointed out by commenters) since `Shoelace` itself has no unmanaged resources, I might use the simpler dispose implementation without needing the `Dispose(bool)` and Destructor:

    class Shoelace : IDisposable
    {
        private AutoResetEvent waitHandle = new AutoResetEvent(false);
        bool tied = false;

        public void Dispose()
        {
            if (waitHandle != null)
            {
                waitHandle.Close();
                waitHandle = null;
            }
            GC.SuppressFinalize(this);
        }
    }


Watch in horror as IDisposable spreads
--------------------------------------
Right that&#39;s that fixed. But now FxCop will complain that `Shoe` creates a `Shoelace`, so `Shoe` must be `IDisposable` too. 

And `Driver` creates `Shoe` so `Driver` must be `IDisposable`.
And `Bus` creates `Driver` so `Bus` must be `IDisposable` and so on.

Suddenly my small change to `Shoelace` is causing me a lot of work and my boss is wondering why I need to checkout `Bus` to make a change to `Shoelace`.

The Question
------------
How do you prevent this spread of `IDisposable`, but still ensure that your unmanaged objects are properly disposed?


  [1]: https://en.wikipedia.org/wiki/FxCop

How do you prevent IDisposable from spreading to all your classes?

I need a SQL update statement for updating a particular field of all the rows with a string &quot;test&quot; to be added in the front of the existing value.

For example, if the existing value is &quot;try&quot; it should become &quot;testtry&quot;.

How to prepend a string to a column value in MySQL?

<p>I need a SQL update statement for updating a particular field of all the rows with a string "test" to be added in the front of the existing value.</p>
<p>For example, if the existing value is "try" it should become "testtry".</p>


How do you roll back a failed rails migration? I would expect that `rake db:rollback` would undo the failed migration, but no, it rolls back the previous migration (the failed migration minus one). And `rake db:migrate:down VERSION=myfailedmigration` doesn&#39;t work either. I&#39;ve ran into this a few times and it&#39;s very frustrating. Here&#39;s a simple test I made to duplicate the problem:

    class SimpleTest &lt; ActiveRecord::Migration
      def self.up
        add_column :assets, :test, :integer
        # the following syntax error will cause the migration to fail
        add_column :asset, :test2, :integer
      end
    
      def self.down
        remove_column :assets, :test
        remove_column :assets, :test2
      end
    end

result:

&lt;pre&gt;
==  SimpleTest: migrating =====================================================
-- add_column(:assets, :test, :integer)
   -&gt; 0.0932s
-- add_column(:asset, :error)
rake aborted!
An error has occurred, all later migrations canceled:

wrong number of arguments (2 for 3)
&lt;/pre&gt;

ok, lets roll it back:

&lt;pre&gt;
$ rake db:rollback
==  AddLevelsToRoles: reverting ===============================================
-- remove_column(:roles, :level)
   -&gt; 0.0778s
==  AddLevelsToRoles: reverted (0.0779s) ======================================
&lt;/pre&gt;

huh? that was my last migration before SimpleTest, not the failed migration. (And oh, it would be nice if the migration output included the version number.)

So lets try running the down for the failed migration SimpleTest:

&lt;pre&gt;
$ rake db:migrate:down VERSION=20090326173033
$
&lt;/pre&gt;

Nothing happens, and no output either. But maybe it ran the migration anyway? So lets fix the syntax error in the SimpleTest migration, and try to run it again. 

&lt;pre&gt;
$ rake db:migrate:up VERSION=20090326173033
==  SimpleTest: migrating =====================================================
-- add_column(:assets, :test, :integer)
rake aborted!
Mysql::Error: Duplicate column name &#39;test&#39;: ALTER TABLE `assets` ADD `test` int(11)
&lt;/pre&gt;

Nope. Obviously the migrate:down didn&#39;t work. It&#39;s not failing, it&#39;s just not executing.

No way to get rid of that duplicate table other than manually going into the database and removing it, and then running the test. There&#39;s got to be a better way than that.


Rolling back a failed Rails migration

I have a table with a varchar column, and I would like to find all the records that have duplicate values in this column. What is the best query I can use to find the duplicates?

Finding duplicate values in MySQL

I have the following table of counters:

    CREATE TABLE cache (
        key text PRIMARY KEY,
        generation int
    );

I would like to increment one of the counters, or set it to zero if the corresponding row doesn&#39;t exist yet. Is there a way to do this without concurrency issues in standard SQL? The operation is sometimes part of a transaction, sometimes separate.

The SQL must run unmodified on SQLite, PostgreSQL and MySQL, if possible.

A search yielded several ideas which either suffer from concurrency issues, or are specific to a database:

 - Try to `INSERT` a new row, and `UPDATE` if there was an error. Unfortunately, the error on `INSERT` aborts the current transaction.

 - `UPDATE` the row, and if no rows were modified, `INSERT` a new row.

 - MySQL has an `ON DUPLICATE KEY UPDATE` clause.


EDIT: Thanks for all the great replies. It looks like Paul is right, and there&#39;s not a single, portable way of doing this. That&#39;s quite surprising to me, as it sounds like a very basic operation.

How do I UPDATE a row in a table or INSERT it if it doesn&#39;t exist?

For a project we having a bunch of data that always have the same structure and is not linked together.
There are two approaches to save the data:

 - Creating a new database for every pool (about 15-25 tables)
 - Creating all the tables in one database and differ the pools by table names.

Which one is easier and faster to handle for MySQL?

**EDIT:** I am not interessed in issues of database design, I am just interessed in which of the two possibilities is faster.

**EDIT 2:** I will try to make it more clear. As said we will have data, where some of the date rarely belongs together in different pools. Putting all the data of one type in one table and linking it with a pool id is not a good idea:

 - It is hard to backup/delete a specific pool (and we expect that we are running out primary keys after a while (even when use big int))

So the idea is to make a database for every pool or create a lot of tables in one database. 50% of the queries against the database will be simple `inserts`. 49% will be some simple `selects` on a primary key.

The question is, what is faster to handle for `MySQL`? Many tables or many databases?

MySQL: Many tables or many databases?

How can I access the MySQL command line with XAMPP for Windows?

I tried :

    UPDATE closure JOIN item ON ( item_id = id ) 
    SET checked = 0 
    WHERE ancestor_id = 1

And:

    UPDATE closure, item 
    SET checked = 0 
    WHERE ancestor_id = 1 AND item_id = id

Both works with MySQL, but those give me a syntax error in SQLite.

How can I make this UPDATE / JOIN works with SQLite version 3.5.9 ?


How do I make an UPDATE while joining tables on SQLite?

I have two tables, and want to update fields in T1 for all rows in a LEFT JOIN.  

For an easy example, update all rows of the following result-set:  

    SELECT T1.* FROM T1 LEFT JOIN T2 ON T1.id = T2.id WHERE T2.id IS NULL  

The [MySQL manual][1] states that:  

&gt; Multiple-table UPDATE statements can use any type of join allowed in SELECT statements, such as LEFT JOIN.  

But I cannot find the proper syntax for doing that in the documented multiple-tables UPDATE.  

What is the proper syntax?


  [1]: http://dev.mysql.com/doc/refman/5.6/en/update.html

UPDATE multiple tables in MySQL using LEFT JOIN

I have a PL/SQL function (running on Oracle 10g) in which I update some rows. Is there a way to find out how many rows were affected by the UPDATE? When executing the query manually it tells me how many rows were affected, I want to get that number in PL/SQL.

Number of rows affected by an UPDATE in PL/SQL

I have to update a field with a value which is returned by a join of 3 tables.

Example: 

    select
        im.itemid
        ,im.sku as iSku
        ,gm.SKU as GSKU
        ,mm.ManufacturerId as ManuId
        ,mm.ManufacturerName
        ,im.mf_item_number
        ,mm.ManufacturerID
    from 
        item_master im, group_master gm, Manufacturer_Master mm 
    where
        im.mf_item_number like &#39;STA%&#39;
        and im.sku=gm.sku
        and gm.ManufacturerID = mm.ManufacturerID
        and gm.manufacturerID=34

I want to update the `mf_item_number` field values of table `item_master` with some other value which is joined in the above condition.

How can I do this in MS SQL Server?


SQL update query using joins

I want to do a large update on a table in PostgreSQL, but I don&#39;t need the transactional integrity to be maintained across the entire operation, because I know that the column I&#39;m changing is not going to be written to or read during the update.  I want to know if there is an easy way *in the psql console* to make these types of operations faster. 

For example, let&#39;s say I have a table called &quot;orders&quot; with 35 million rows, and I want to do this: 

    UPDATE orders SET status = null;

To avoid being diverted to an offtopic discussion, let&#39;s assume that all the values of status for the 35 million columns are currently set to the same (non-null) value, thus rendering an index useless.

The problem with this statement is that it takes a very long time to go into effect (solely because of the locking), and all changed rows are locked until the entire update is complete.    This update might take 5 hours, whereas something like 

    UPDATE orders SET status = null WHERE (order_id &gt; 0 and order_id &lt; 1000000);

might take 1 minute.   Over 35 million rows, doing the above and breaking it into chunks of 35 would only take 35 minutes and save me 4 hours and 25 minutes.

I could break it down even further with a script (using pseudocode here):

    for (i = 0 to 3500) {
      db_operation (&quot;UPDATE orders SET status = null
                     WHERE (order_id &gt;&quot; + (i*1000)&quot;
                 + &quot; AND order_id &lt;&quot; + ((i+1)*1000) &quot; +  &quot;)&quot;);
    }

This operation might complete in only a few minutes, rather than 35.  


So that comes down to what I&#39;m really asking.  I don&#39;t want to write a freaking script to break down operations every single time I want to do a big one-time update like this.   Is there a way to accomplish what I want entirely within SQL?


Content Type	Original Author	Original Content on Stackoverflow
Question	santanu	View Question on Stackoverflow
Solution 1 - Mysql	Paul Dixon	View Answer on Stackoverflow
Solution 2 - Mysql	Ferdinand Beyer	View Answer on Stackoverflow
Solution 3 - Mysql	bvidinli	View Answer on Stackoverflow
Solution 4 - Mysql	soulmerge	View Answer on Stackoverflow
Solution 5 - Mysql	user3419778	View Answer on Stackoverflow

How to prepend a string to a column value in MySQL?

Mysql Problem Overview

Mysql Solutions

Solution 1 - Mysql

Solution 2 - Mysql

Solution 3 - Mysql

Solution 4 - Mysql

Solution 5 - Mysql

How do you prevent IDisposable from spreading to all your classes?

Why doesn't delete set the pointer to NULL?

Attributions