Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>You can use normal sql constructs in the <a href="http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html" rel="noreferrer">ON DUPLICATE KEY</a> syntax. So in order to do conditional updates during an insert you can do the following:</p> <pre><code>INSERT INTO tbl (hat, mittens, name) VALUES ('yellow','purple','jimmy') ON DUPLICATE KEY UPDATE name = CASE WHEN name &lt;&gt; VALUES(name) THEN VALUES(name) ELSE name END; </code></pre> <p>This will change the value to what you provided to the insert statement when it's different from what's in the row and will set the value to be what it already is if it hasn't changed and will result in MySQL not doing anything to the row preserving the last_update timestamp as Quassnoi pointed out.</p> <p>If you wanted to make 100% sure that you weren't relying on the behavior of MySQL where it doesn't update a row if you set a value to itself you can do the following to force the timestamp:</p> <pre><code>INSERT INTO tbl (hat, mittens, name) VALUES ('yellow','purple','jimmy') ON DUPLICATE KEY UPDATE name = CASE WHEN name &lt;&gt; VALUES(name) THEN VALUES(name) ELSE name END , last_update = CASE WHEN name &lt;&gt; VALUES(name) THEN now() ELSE last_update END; </code></pre> <p>This will only update the <code>last_update</code> to <code>now()</code> when the name has changed else it will tell MySQL to retain the value of <code>last_update</code>.</p> <p>Also, in the ON DUPLICATE KEY section of the statement you can refer to the columns in the table by their name and you can get the values that you provided to the insert statement values section using the <a href="http://dev.mysql.com/doc/refman/5.1/en/miscellaneous-functions.html#function_values" rel="noreferrer">VALUES(column_name)</a> function.</p> <hr> <p>The following is a log that shows that the last statement provided works even on 4.1 where the others don't work due to a bug that was fixed in version 5.0.</p> <pre><code>C:\mysql\bin&gt;mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.1.22-community Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql&gt; show databases; +----------+ | Database | +----------+ | mysql | | test | +----------+ 2 rows in set (0.00 sec) mysql&gt; use test; Database changed mysql&gt; show tables; Empty set (0.00 sec) mysql&gt; CREATE TABLE `tbl` ( -&gt; `hat` varchar(11) default NULL, -&gt; `mittens` varchar(11) default NULL, -&gt; `name` varchar(11) default NULL, -&gt; `stamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -&gt; UNIQUE KEY `clothes` (`hat`,`mittens`) -&gt; ) ENGINE=MyISAM DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.01 sec) mysql&gt; INSERT INTO tbl (hat,mittens,name) VALUES ('blue','green','george'); Query OK, 1 row affected (0.00 sec) mysql&gt; select * from tbl; +------+---------+--------+---------------------+ | hat | mittens | name | stamp | +------+---------+--------+---------------------+ | blue | green | george | 2009-06-27 12:15:16 | +------+---------+--------+---------------------+ 1 row in set (0.00 sec) mysql&gt; INSERT INTO tbl (hat,mittens,name) VALUES ('blue','green','george') ON DUPLICATE KEY UPDATE name='george'; Query OK, 2 rows affected (0.00 sec) mysql&gt; select * from tbl; +------+---------+--------+---------------------+ | hat | mittens | name | stamp | +------+---------+--------+---------------------+ | blue | green | george | 2009-06-27 12:15:30 | +------+---------+--------+---------------------+ 1 row in set (0.00 sec) mysql&gt; INSERT INTO tbl (hat, mittens, name) VALUES ('blue','green','george') ON DUPLICATE KEY UPDATE name=CASE WHEN name &lt;&gt; VALUES(name) THEN VALUES(name) ELSE name END; Query OK, 2 rows affected (0.00 sec) mysql&gt; select * from tbl; +------+---------+--------+---------------------+ | hat | mittens | name | stamp | +------+---------+--------+---------------------+ | blue | green | george | 2009-06-27 12:15:42 | +------+---------+--------+---------------------+ 1 row in set (0.00 sec) mysql&gt; INSERT INTO tbl (hat,mittens,name) VALUES ('blue','green','george') ON DUPLICATE KEY UPDATE name = CASE WHEN name &lt;&gt; VALUES(name) THEN VALUES(name) ELSE name END, stamp = CASE WHEN name &lt;&gt; VALUES(name) THEN now() ELSE stamp END; Query OK, 2 rows affected (0.00 sec) mysql&gt; select * from tbl; +------+---------+--------+---------------------+ | hat | mittens | name | stamp | +------+---------+--------+---------------------+ | blue | green | george | 2009-06-27 12:15:42 | +------+---------+--------+---------------------+ 1 row in set (0.00 sec) mysql&gt; </code></pre> <hr> <p>Let me know if you have any questions.</p> <p>HTH,</p> <p>-Dipin</p>
    singulars
    1. This table or related slice is empty.
    1. POconditional on duplicate key update
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USDipin
    1. USDipin
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POconditional on duplicate key update
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COFor me it was a bit difficult to find out what <> stands for (not equal to!) so I just thought I'd add this here in a comment in case someone else wonders in the future. :)
      singulars
      1. PO
      1. USAlisso
    2. COHi small help how can I query when I have multiple columns when updating
      singulars
      1. PO
      1. USChaitanya
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload