&lt;http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_now&gt;

&gt;&quot;NOW() returns a constant time that indicates the time at which the statement began to execute. (Within a stored routine or trigger, NOW() returns the time at which the routine or triggering statement began to execute.) This differs from the behavior for SYSDATE(), which returns the exact time at which it executes as of MySQL 5.0.13. &quot;

Assign `NOW()` to a variable then update the datetime with variable:

    update_date_time=now()

now update like this

    UPDATE table SET datetime =update_date_time;

correct the syntax, as per your requirement 

 

  

They should have the same time, the update is supposed to be atomic, meaning that whatever how long it takes to perform, the action is supposed to occurs as if all was done at the same time.

If you&#39;re experiencing a different behaviour, it&#39;s time to change for another DBMS.

The sqlite answer is 

    update TABLE set mydatetime = datetime(&#39;now&#39;);

in case someone else was looking for it.

Can you recommend a Java library for reading, parsing, validating and mapping rows in a comma separated value (CSV) file to Java value objects (JavaBeans)?

Can you recommend a Java library for reading (and possibly writing) CSV files?

What is the best database schema to track role-based access controls for a web application?

I am using Rails, but the RBAC plugin linked by Google looks unmaintained (only 300 commits to SVN; latest was almost a year ago).

The concept is simple enough to implement from scratch, yet complex and important enough that it&#39;s worth getting right.

So how do others architect and implement their RBAC model?

Best Role-Based Access Control (RBAC) database model

When you run something similar to:

    UPDATE table SET datetime = NOW();

on a table with 1 000 000 000 records and the query takes 10 seconds to run, will all the rows have the exact same time (minutes and seconds) or will they have different times? In other words, will the time be when the query started or when each row is updated?

I&#39;m running MySQL, but I&#39;m thinking this applies to all dbs.

When running UPDATE ... datetime = NOW(); will all rows updated have the same date/time?

When you run something similar to:
<pre><code class="hljs language-ini">UPDATE table SET datetime = NOW();
</code></pre>
on a table with 1 000 000 000 records and the query takes 10 seconds to run, will all the rows have the exact same time (minutes and seconds) or will they have different times? In other words, will the time be when the query started or when each row is updated?
I'm running MySQL, but I'm thinking this applies to all dbs.

I have 2 tables. One (domains) has domain ids, and domain names (dom_id, dom_url).

the other contains actual data, 2 of which columns require a TO and FROM domain names. So I have 2 columns rev_dom_from and rev_dom_for, both of which store the domain name id, from the domains table.

Simple.

Now I need to actually display both domain names on the webpage. I know how to display one or the other, via the LEFT JOIN domains ON reviews.rev_dom_for = domains.dom_url query, and then you echo out the dom_url, which would echo out the domain name in the rev_dom_for column.

But how would I make it echo out the 2nd domain name, in the dom_rev_from column?



How do you join on the same table, twice, in mysql?

I need to use an alias in the WHERE clause, but It keeps telling me that its an unknown column. Is there any way to get around this issue? I need to select records that have a rating higher than x. Rating is calculated as the following alias:

    sum(reviews.rev_rating)/count(reviews.rev_id) as avg_rating

Can you use an alias in the WHERE clause in mysql?

In MySQL, how do I get a list of all foreign key constraints pointing to a particular table? a particular column? This is the same thing as [this Oracle question][1], but for MySQL.

  [1]: https://stackoverflow.com/questions/85978/query-a-tables-foreign-key-relationships

How do I see all foreign keys to a table or column?

One of the responses to [a question I asked yesterday][1] suggested that I should make sure my database can handle UTF-8 characters correctly. How I can do this with MySQL?

  [1]: https://stackoverflow.com/questions/198721/converting-a-word-document-into-usable-html-in-php


How to make MySQL handle UTF-8 properly

I&#39;m running a MySQL database locally for development, but deploying to Heroku which uses Postgres. Heroku handles almost everything, but my case-insensitive Like statements become case sensitive. I could use iLike statements, but my local MySQL database can&#39;t handle that.

What is the best way to write a case insensitive query that is compatible with both MySQL and Postgres? Or do I need to write separate Like and iLike statements depending on the DB my app is talking to?

How do you write a case insensitive query for both MySQL and Postgres?

Does anyone know where I can find a library of common but difficult (out of the ordinary) SQL script examples. I am talking about those examples you cannot find in the documentation but do need very often to accomplish tasks such as finding duplicates etc.

It would be a big time saver to have something like that handy.

EDIT: Thanks everyone, I think this is turning into a great quick reference. The more descriptive the more effective it would be, so please if you see your way open - please edit and add some descriptions of what one could find. Many thanks to those that have already done so!

Library of Useful (Difficult) SQL scripts

I have a postgres table. I need to delete some data from it. I was going to create a temporary table, copy the data in, recreate the indexes and the delete the rows I need. I can&#39;t delete data from the original table, because this original table is the source of data. In one case I need to get some results that depends on deleting X, in another case, I&#39;ll need to delete Y. So I need all the original data to always be around and available.

However it seems a bit silly to recreate the table and copy it again and recreate the indexes. Is there anyway in postgres to tell it &quot;I want a complete separate copy of this table, including structure, data and indexes&quot;?

Unfortunately PostgreSQL does not have a &quot;CREATE TABLE .. LIKE X INCLUDING INDEXES&#39;

Copy a table (including indexes) in postgres

I have a Stored procedure which schedules a job. This Job takes a lot of time to get completed (approx 30 to 40 min). I need to get to know the status of this Job.
Below details would help me

1) How to see the list of all jobs that have got scheduled for a future time and are yet to start

2) How to see the the list of jobs running and the time span from when they are running

3) How to see if the job has completed successfully or has stoped in between because of any error.


How can I determine the status of a job?

Is it possible in a sqlite database to craete a table that has a timestamp column that default to `DATETIME(&#39;now&#39;)` ?

Like this:

    CREATE TABLE test (
        id INTEGER PRIMARY KEY AUTOINCREMENT, 
        t TIMESTAMP DEFAULT DATETIME(&#39;now&#39;)
    );

This gives an error... How to resolve?


sqlite database default time value &#39;now&#39;

Update: Now that it&#39;s 2016 I&#39;d use PowerShell for this unless there&#39;s a really compelling backwards-compatible reason for it, particularly because of the regional settings issue with using `date`. See @npocmaka&#39;s https://stackoverflow.com/a/19799236/8479

---

What&#39;s a Windows command line statement(s) I can use to get the current datetime in a format that I can put into a filename?

I want to have a .bat file that zips up a directory into an archive with the current date and time as part of the name, for example, `Code_2008-10-14_2257.zip`. Is there any easy way I can do this, independent of the regional settings of the machine?

I don&#39;t really mind about the date format, ideally it&#39;d be yyyy-mm-dd, but anything simple is fine.

So far I&#39;ve got this, which on my machine gives me `Tue_10_14_2008_230050_91`:

    rem Get the datetime in a format that can go in a filename.
    set _my_datetime=%date%_%time%
    set _my_datetime=%_my_datetime: =_%
    set _my_datetime=%_my_datetime::=%
    set _my_datetime=%_my_datetime:/=_%
    set _my_datetime=%_my_datetime:.=_%

    rem Now use the timestamp by in a new ZIP file name.
    &quot;d:\Program Files\7-Zip\7z.exe&quot; a -r Code_%_my_datetime%.zip Code

I can live with this, but it seems a bit clunky. Ideally it&#39;d be briefer and have the format mentioned earlier.

I&#39;m using Windows Server 2003 and Windows&amp;nbsp;XP Professional. I don&#39;t want to install additional utilities to achieve this (although I realise there are some that will do nice date formatting).


How do I get current date/time on the Windows command line in a suitable format for usage in a file/folder name?

Since I&#39;ve started to use jQuery, I have been doing a lot more JavaScript development.

I have the need to parse different date formats and then to display them into another format.

Do you know of any good tool to do this?

Which one would you recommend?

Best JavaScript Date Parser &amp; Formatter?

I want to convert a string like this:

    &#39;10/15/2008 10:06:32 PM&#39;

into the equivalent DATETIME value in Sql Server.

In Oracle, I would say this:

    TO_DATE(&#39;10/15/2008 10:06:32 PM&#39;,&#39;MM/DD/YYYY HH:MI:SS AM&#39;)

[This question][1] implies that I must parse the string into one of the [standard formats][2], and then convert using one of those codes.  That seems ludicrous for such a mundane operation.  Is there an easier way?


  [1]: https://stackoverflow.com/questions/202243/custom-datetime-formatting-in-sql-server
  [2]: http://www.sql-server-helper.com/tips/date-formats.aspx

Sql Server string to date conversion

Something similar to Unix&#39;s timestamp, that is a single number that represents the current time and date. Either as a number or a string.

How do you get a timestamp in JavaScript?

I&#39;ve been searching a lot but couldn&#39;t find a solution. How do you deal with a DateTime that should be able to contain an uninitialized value (equivalent to null)? I have a class which might have a DateTime property value set or not. I was thinking of initializing the property holder to DateTime.MinValue, which then could easily be checked. I guess this is a quite common question, how do you do that?

DateTime &quot;null&quot; value

I have a database with `account numbers` and `card numbers`. I match these to a file to `update` any card numbers to the account number, so that I am only working with account numbers. 

I created a view linking the table to the account/card database to return the `Table ID` and the related account number, and now I need to update those records where the ID matches with the Account Number.

This is the `Sales_Import` table, where the `account number` field needs to be updated:

    LeadID	AccountNumber
    147	        5807811235
    150	        5807811326
    185	        7006100100007267039


And this is the `RetrieveAccountNumber` table, where I need to update from:

    LeadID	AccountNumber
    147	        7006100100007266957
    150	        7006100100007267039


I tried the below, but no luck so far:

    UPDATE [Sales_Lead].[dbo].[Sales_Import] 
    SET    [AccountNumber] = (SELECT RetrieveAccountNumber.AccountNumber 
                              FROM   RetrieveAccountNumber 
                              WHERE  [Sales_Lead].[dbo].[Sales_Import]. LeadID = 
                                                    RetrieveAccountNumber.LeadID) 

It updates the card numbers to account numbers, but the account numbers gets replaced by `NULL`


SQL update from one Table to another based on a ID match

Ideally I want to do this:

    UPDATE TOP (10) messages SET status=10 WHERE status=0 ORDER BY priority DESC;

In English: I want to get the top 10 available (status=0) messages from the DB and lock them (status=10). A message with a higher priority should be gotten first.

unfortunately MS SQL doesn&#39;t allow an order by clause in the update.

Anyway how to circumvent this?

How to update and order by using ms sql

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?

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

Content Type	Original Author	Original Content on Stackoverflow
Question	Darryl Hein	View Question on Stackoverflow
Solution 1 - Mysql	micahwittman	View Answer on Stackoverflow
Solution 2 - Mysql	MRRaja	View Answer on Stackoverflow
Solution 3 - Mysql	gizmo	View Answer on Stackoverflow
Solution 4 - Mysql	My Name Goes Here	View Answer on Stackoverflow

When running UPDATE ... datetime = NOW(); will all rows updated have the same date/time?

Mysql Problem Overview

Mysql Solutions

Solution 1 - Mysql

Solution 2 - Mysql

Solution 3 - Mysql

Solution 4 - Mysql

Best Role-Based Access Control (RBAC) database model

Can you recommend a Java library for reading (and possibly writing) CSV files?

Attributions