`MySQL` supports `IF` statement.

    UPDATE 	abbonamento
    SET		punti = IF(tipo = &#39;punti&#39;, punti - 1, punti),
    		bonus = IF(tipo &lt;&gt; &#39;punti&#39;, bonus - 1, bonus)
    WHERE	id = 17

or you can also use `CASE`

    UPDATE 	abbonamento
    SET		punti = CASE WHEN tipo = &#39;punti&#39; THEN punti - 1 ELSE punti END,
    		bonus = CASE WHEN tipo &lt;&gt; &#39;punti&#39; THEN bonus - 1 ELSE bonus END
    WHERE	id = 17

I want to add dynamic number of elements to a string array from inside a for loop.


How to create an string array of an undefined length?


How to dynamically add elements to String array?

I would like to see all stashes in `git log` output. Does anyone know if there is a way to do that?

Edit: I want to see all commits in the log -- *including* stash commits. I tried the command:

    git log --date-order --all

But it returns only the top most stash. I would like to see commits that represent other stashes too.

Show all stashes in git log

It seems I have big problems with *conditional queries*.

I have to do a *conditional update*. I write here what I would like to do:

     IF(SELECT tipo FROM abbonamento WHERE idU = 17) = &#39;punti&#39; THEN
         UDPATE abbonamento SET punti = punti - 1 
     ELSE
         UPDATE abbonamento SET bonus = bonus - 1

Obviously this doesn&#39;t work.  
Any idea?

Update MySQL with if condition

It seems I have big problems with conditional queries.
I have to do a conditional update. I write here what I would like to do:
<pre><code class="hljs language-sql"> IF(SELECT tipo FROM abbonamento WHERE idU = 17) = 'punti' THEN
 UDPATE abbonamento SET punti = punti - 1 
 ELSE
 UPDATE abbonamento SET bonus = bonus - 1
</code></pre>
Obviously this doesn't work. 
Any idea?

I recently tried installing MySQL with homebrew (`brew install mysql`) and when I try to run it I get the following error:

&gt;ERROR 2002 (HY000): Can&#39;t connect to local MySQL server through socket &#39;/tmp/mysql.sock&#39; (2)

There is no `/tmp/mysql.sock` nor a `/var/lib/mysql.sock`.

I&#39;ve searched and haven&#39;t found any `mysql.sock` file.

How can I fix this?

Can&#39;t connect to local MySQL server through socket homebrew

I am using this Where  Condition in One Of my query with MySql Database.My Problem is that i have one displaytime  column in my table but that table column shows the data in UTC Time.and i want to convert that displaytime column in the Local Time Zone.so how can i provide this facility from query itself.

I have goggled the things and by that i knew that something like `SELECT CONVERT_TZ()` will work for that.but its not working for me.

Here is my query in which i need to convert displaytime to local time zone...so can anyone please guide me?

&lt;!-- language: lang-sql --&gt;

    WHERE displaytime &gt;= &#39;2012-12-01 00:00:00&#39;
      AND displaytime &lt;=&#39;2013-02-22 23:59:59&#39;
      AND ct.organizationId IN (
        SELECT t.organizationId
          FROM organization_ AS t
          JOIN organization_ AS p ON t.treePath LIKE CONCAT(p.treePath, &#39;%&#39;)
         WHERE p.organizationId = 10707

![enter image description here][1]
SAmple DAta

![enter image description here][2]


  [1]: http://i.stack.imgur.com/46STa.jpg
  [2]: http://i.stack.imgur.com/Lgu0u.jpg

How to Convert UTC Date To Local time Zone in MySql Select Query

I have a small PHP application storing data in a [MySQL][1] database. Currently username / password are hard-coded in the PHP code. A situation I do not really like, for example, since the code is also available in a repository.

The best idea I have is to move the data from the code to a configuration file (excluded from the repository), and somehow encode it, so is not directly readable (obfuscation). Is there any better and easy to use way to solve the issue?

    $link = mysql_connect(&#39;localhost&#39;, &#39;mysql_user&#39;, &#39;mysql_password&#39;);
    if (!$link) { 
        die(&#39;Could not connect: &#39; . mysql_error());
    }
    mysql_select_db(&#39;mydb&#39;);

Scope: I want to established a robust, but also easy-to-use solution. I want reasonable security, but I am not dealing with highly confidential data here.

Remark: It is no longer recommended to use the `mysql_connect` functions, see Stack Overflow question *[Why shouldn&#39;t I use mysql_* functions in PHP?][2]*. I could have changed the code example, but since some comments refer to this, I did not. However, the original nature of the question remains valid.

  [1]: http://en.wikipedia.org/wiki/MySQL
  [2]: https://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php


PHP and MySQL - how to avoid password in source code?

I&#39;m new to using Heroku and I have a Ruby/Sinatra app in which I plan on using a MySQL database as the main data store. 

However, I do not want to write results directly to the database on Heroku. Instead, I want to write the results to a local database, and then be able to easily deploy/update/copy my local DB to the &quot;production&quot; database on Heroku.

How do I do this?

Thanks.

How to deploy local MySQL database to Heroku

I asked a question and got this reply which helped.

       UPDATE TABLE_A a JOIN TABLE_B b
       ON a.join_col = b.join_col AND a.column_a = b.column_b
       SET a.column_c = a.column_c + 1

Now I am looking to do this if there are three tables involved something like this.

        UPDATE tableC c JOIN tableB b JOIN tableA a

My question is basically... is it possible to do three table joins on an `UPDATE` statement? And what is the correct syntax for it?

Do I do the following?

     JOIN tableB, tableA
     JOIN tableB JOIN tableA





How can I do three table JOINs in an UPDATE query?

In my stored procedure, I have three insert statements.

On duplicate key value insertion first two queries generate the error 

&gt; *Violation of PRIMARY KEY constraint*

and third query runs as usual.

Now I want that if any query generates any exception, everything should get rolled back.

If there isn&#39;t any exception generate by any query, it should get committed.


    declare @QuantitySelected as char
        set @QuantitySelected = 2
        
        declare @sqlHeader as varchar(1000)
        declare @sqlTotals as varchar(1000)
        declare @sqlLine as varchar(1000)
        
        select @sqlHeader = &#39;Insert into tblKP_EstimateHeader &#39;
        select @sqlHeader = @sqlHeader + &#39;(CompanyID,CompanyName,ProjectName,EstimateID,EstimateHeader,QuoteDate,ValidUntil,RFQNum,Revision,Contact,Status,NumConfigurations) &#39;
        select @sqlHeader = @sqlHeader + &#39; select CompanyID,CompanyName,ProjectName,EstimateID,EstimateHeader,QuoteDate,ValidUntil,RFQNum,Revision,Contact,Status,NumConfigurations &#39;
        select @sqlHeader = @sqlHeader +  &#39;from V_EW_Estimate_Header where EstimateID = 2203&#39;
        
        
        
        select @sqlTotals = &#39;Insert into tblKP_Estimate_Configuration_Totals &#39;
        select @sqlTotals = @sqlTotals + &#39;(ConfigRecId,RecId,SellQty,ConfigNum,ConfigDesc,SortOrder,OptionsInMainPrice,MarkupPctQty,&#39;
        select @sqlTotals = @sqlTotals + &#39; SellPriceQty,RubberStamp,OptPriceQty,StatusRecid,LastUpdate_Date,LastUpdate_User,TotalCost,QuantityBracketSelected)&#39;
        select @sqlTotals = @sqlTotals + &#39; select ConfigRecId,RecId,SellQty&#39; + @QuantitySelected + &#39;,ConfigNum,ConfigDesc,SortOrder,OptionsInMainPrice&#39;
        select @sqlTotals = @sqlTotals + &#39; ,MarkupPctQty&#39; + @QuantitySelected + &#39;,SellPriceQty&#39; + @QuantitySelected + &#39;,RubberStamp,OptPriceQty&#39; + @QuantitySelected + &#39;,StatusRecid,LastUpdate_Date,LastUpdate_User,TotalCost&#39; + @QuantitySelected + &#39;,&#39; + @QuantitySelected
        select @sqlTotals = @sqlTotals + &#39; from v_EW_Estimate_Configuration_Totals where ConfigRecId = -3&#39;
        
        
        select @sqlLine = &#39;Insert into tblKP_Estimate_Configuration_Lines&#39;
        select @sqlLine = @sqlLine + &#39;(MstrRfqRecId,RfqRecId,RfqLineRecId,CompanyId,VendorQuoteNum,LineGrp,LineNum,StatusRecId,&#39;
        select @sqlLine = @sqlLine + &#39; LineDesc,LineSize,LineMatl,LineDeco,LineFinish,CopyFromRecId,PerPieceCost,IsOptional,&#39;
        select @sqlLine = @sqlLine + &#39; CopyToNewRev,RecId,UnitPrice,LineQty,LinePrice,CustOrVend,SellQty1,RfqNum,ConfigLineIsOptional,ConfigLinePerPieceCost,ConfigLineRecid,SellPrice,SaleQty)&#39;
        select @sqlLine = @sqlLine + &#39; select distinct MstrRfqRecId,RfqRecId,RfqLineRecId,CompanyId,VendorQuoteNum,LineGrp,LineNum,&#39;
        select @sqlLine = @sqlLine + &#39; StatusRecId,LineDesc,LineSize,LineMatl,LineDeco,LineFinish,CopyFromRecId,PerPieceCost,IsOptional,&#39;
        select @sqlLine = @sqlLine + &#39; CopyToNewRev,RecId,UnitPrice&#39; + @QuantitySelected + &#39;,LineQty&#39; + @QuantitySelected + &#39;, isnull(LinePrice&#39; + @QuantitySelected + &#39;, 0.0000),CustOrVend,SellQty&#39; + @QuantitySelected + &#39;,RfqNum,ConfigLineIsOptional,ConfigLinePerPieceCost,ConfigLineRecid,SellPrice&#39; + @QuantitySelected + &#39;,SaleQty&#39; + @QuantitySelected
        select @sqlLine = @sqlLine + &#39; from v_EW_EstimateLine  where rfqlinerecid in (select RfqLineRecID from kp_tblVendorRfqConfigLine where ConfigRecID = -3) &#39;
        
        exec( @sqlHeader)
        exec(@sqlTotals)
        exec(@sqlLine)



How to rollback or commit a transaction in SQL Server

I have met some problem with the SQL server, this is the function I created:

    ALTER FUNCTION [dbo].[testing1](@price int)
    RETURNS @trackingItems1 TABLE (
       item       nvarchar  NULL,
       warehouse   nvarchar NULL,
       price int   NULL
    ) 
    AS
    BEGIN
       INSERT INTO @trackingItems1(item, warehouse, price)
       SELECT ta.item, ta.warehouse, ta.price 
       FROM   stock ta
       WHERE  ta.price &gt;= @price; 
       
       RETURN;
    END;

When I write a query to use that function like the following it getting the error

&gt; *String or binary data would be truncated. The statement has been terminated*

How can I fix this problem?

    select * from testing1(2)

This is the way I create the table

    CREATE TABLE stock(item       nvarchar(50) NULL,
                       warehouse   nvarchar(50) NULL,
                       price int NULL);

String or binary data would be truncated. The statement has been terminated

How to recover the unsaved scripts if the SSMS crashes / unsaved tab gets accidentally closed?

Recover unsaved SQL query scripts

Can a `TABLE` have a primary key without a clustered index?

And can a `TABLE` have a clustered index without having a primary key?

Can anybody briefly tell me the relationship between primary key and clustered index?

Relationship of Primary Key and Clustered Index

I have 2 tables, I want to filter the 1 table **before** the 2 tables are joined together.

Customer Table:


       ╔══════════╦═══════╗
       ║ Customer ║ State ║
       ╠══════════╬═══════╣
       ║ A        ║ S     ║
       ║ B        ║ V     ║
       ║ C        ║ L     ║
       ╚══════════╩═══════╝


Entry Table:

       ╔══════════╦═══════╦══════════╗
       ║ Customer ║ Entry ║ Category ║
       ╠══════════╬═══════╬══════════╣
       ║ A        ║  5575 ║ D        ║
       ║ A        ║  6532 ║ C        ║
       ║ A        ║  3215 ║ D        ║
       ║ A        ║  5645 ║ M        ║
       ║ B        ║  3331 ║ A        ║
       ║ B        ║  4445 ║ D        ║
       ╚══════════╩═══════╩══════════╝

I want to Left Join so I get all records from the Customer table regardless of whether there are related records in the Entry table. However I want to filter on category D in the entry table **before the join**.

Desired Results:

       ╔══════════╦═══════╦═══════╗
       ║ Customer ║ State ║ Entry ║
       ╠══════════╬═══════╬═══════╣
       ║ A        ║ S     ║  5575 ║
       ║ A        ║ S     ║  3215 ║
       ║ B        ║ V     ║  4445 ║
       ║ C        ║ L     ║  NULL ║
       ╚══════════╩═══════╩═══════╝

If I was to do the following query:

       SELECT Customer.Customer, Customer.State, Entry.Entry
       FROM Customer
       LEFT JOIN Entry
       ON Customer.Customer=Entry.Customer
       WHERE Entry.Category=&#39;D&#39;

This would filter out the last record.

So I want all rows from the left table and join it to the entry table filtered on category D.

Thanks to any help in advance!!

Filter Table Before Applying Left Join

    UPDATE dbo.TestStudents  
    SET LASTNAME = 
    ( CASE  
    WHEN (LASTNAME = &#39;AAA&#39;) THEN &#39;BBB&#39; 
    WHEN (LASTNAME = &#39;CCC&#39;) THEN &#39;DDD&#39; 
    WHEN (LASTNAME = &#39;EEE&#39;) THEN &#39;FFF&#39; 
    ELSE  (LASTNAME)
    END )
The statement work for the purpose but the else condition scan through every record in the table. Is there any way I can leave the unaffected rows as they are?


I want to use CASE statement to update some records in sql server 2005

I am trying to create a query for entity framework that will allow me to take a list of ids and update a field associated with them. 

Example in SQL:
 
    UPDATE Friends
    SET msgSentBy = &#39;1234&#39;
    WHERE id IN (1, 2, 3, 4)

How do I convert the above into entity framework?

Update Multiple Rows in Entity Framework from a list of ids

I am trying to understand how to UPDATE multiple rows with different values and I just don&#39;t get it. The solution is everywhere but to me it looks difficult to understand.

For instance, three updates into 1 query:

	UPDATE table_users
    SET cod_user = &#39;622057&#39;
        , date = &#39;12082014&#39;
    WHERE user_rol = &#39;student&#39;
        AND cod_office = &#39;17389551&#39;; 

	UPDATE table_users
    SET cod_user = &#39;2913659&#39;
        , date = &#39;12082014&#39;
    WHERE user_rol = &#39;assistant&#39;
        AND cod_office = &#39;17389551&#39;; 

	UPDATE table_users
    SET cod_user = &#39;6160230&#39;
        , date = &#39;12082014&#39;
    WHERE user_rol = &#39;admin&#39;
        AND cod_office = &#39;17389551&#39;; 

I [read][1] an example, but I really don&#39;t understand how to make the query. i.e:

    UPDATE table_to_update
    SET cod_user= IF(cod_office = &#39;17389551&#39;,&#39;622057&#39;,&#39;2913659&#39;,&#39;6160230&#39;)
        ,date = IF(cod_office = &#39;17389551&#39;,&#39;12082014&#39;)
    WHERE ?? IN (??) ;

I&#39;m not entirely clear how to do the query if there are multiple condition in the WHERE and in the IF condition..any ideas?

  [1]: http://www.karlrixon.co.uk/writing/update-multiple-rows-with-different-values-and-a-single-sql-query/

MySQL - UPDATE multiple rows with different values in one query

I am trying to update a text field in a table of my postgresql database.

    UPDATE public.table SET long_text = &#39;First Line&#39; + CHAR(10) + &#39;Second line.&#39; WHERE id = 19;

My intended result is that the cell will look like this:

&lt;pre&gt;
First Line
Second line
&lt;/pre&gt;

The above syntax returns an error.

Insert line break in postgresql when updating text field

Looking through the documentation for the Postgres 9.4 datatype JSONB, it is not immediately obvious to me how to do updates on JSONB columns. 

Documentation for JSONB types and functions: 

http://www.postgresql.org/docs/9.4/static/functions-json.html
http://www.postgresql.org/docs/9.4/static/datatype-json.html

As an examples, I have this basic table structure:

    CREATE TABLE test(id serial, data jsonb);

Inserting is easy, as in:

    INSERT INTO test(data) values (&#39;{&quot;name&quot;: &quot;my-name&quot;, &quot;tags&quot;: [&quot;tag1&quot;, &quot;tag2&quot;]}&#39;);

Now, how would I update the &#39;data&#39; column? This is invalid syntax:

    UPDATE test SET data-&gt;&#39;name&#39; = &#39;my-other-name&#39; WHERE id = 1;

Is this documented somewhere obvious that I missed? Thanks. 

Content Type	Original Author	Original Content on Stackoverflow
Question	Martina	View Question on Stackoverflow
Solution 1 - Mysql	John Woo	View Answer on Stackoverflow

Update MySQL with if condition

Mysql Problem Overview

Mysql Solutions

Solution 1 - Mysql

Show all stashes in git log

How to dynamically add elements to String array?

Attributions