Databases
StackOverflow2013
Postsdbo
POUpdate a list in SQL with incremental dates using one statement?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POUpdate a list in SQL with incremental dates using one statement?
    primarykey
    data
    text
    <p>I am writing an update statement where I need to update a list of items all at once with incremental dates. The incremental value is user defined. I found an example that is pretty similar to my needs at <a href="http://haacked.com/archive/2004/02/28/sql-auto-increment.aspx" rel="nofollow">http://haacked.com/archive/2004/02/28/sql-auto-increment.aspx</a> , but do not know how I would implement it. That example is:</p> <pre><code>DECLARE @counter int SET @counter = 0 UPDATE #tmp_Users SET @counter = counter = @counter + 1 </code></pre> <p>My current statement is:</p> <pre><code>strSQL.CommandText = "Update tblItem set item_timed_close=convert(datetime, @item_timed_close),item_timed_start=convert(money, case when item_est_lo &lt; 500 then ((convert(int,item_est_lo+25)/50)*50) when item_est_lo &lt; 1000 then ((convert(int,item_est_lo+50)/100)*100) when item_est_lo &lt; 3000 then ((convert(int,item_est_lo+125)/250)*250) when item_est_lo &lt; 5000 then ((convert(int,item_est_lo+250)/500)*500) else ((convert(int,item_est_lo+12.5)/25)*25) end ) Where item_sale_id=@item_sale_id"; strSQL.Parameters.Add(new SqlParameter("@item_timed_close", SqlDbType.VarChar, 100, ParameterDirection.Input, true, 0, 0, "item_timed_close", DataRowVersion.Current, datetime_Var.AddMinutes(minutes_Var += Increments_var))); strSQL.Parameters.Add(new SqlParameter("@item_sale_id", SqlDbType.VarChar, 100, ParameterDirection.Input, true, 0, 0, "item_sale_id", DataRowVersion.Current, itemSaleId3_Var)); </code></pre> <p>datetime_Var is user defined, and is a <code>DateTime</code> format. minutes_Var equals zero to start with. Increments_var is user defined. I need <code>item_timed_close</code> to increment by whatever the <code>Increments_var</code> is set to.</p> <p><strong>EDIT</strong></p> <p>Results I am looking for would be something like this:</p> <p>User specifies a starting date of <code>2012-01-08 12:00:00 PM</code> and an increment of <code>10</code> seconds. For each row updated, the date would look like:</p> <pre><code>2012-01-08 12:00:00 PM 2012-01-08 12:00:10 PM 2012-01-08 12:00:20 PM 2012-01-08 12:00:30 PM 2012-01-08 12:00:40 PM 2012-01-08 12:00:50 PM 2012-01-08 12:01:00 PM 2012-01-08 12:01:10 PM 2012-01-08 12:01:20 PM </code></pre> <p>The first date could even have the incremented value already added to it for all we care, so long as it increments. I could change the initial starting date with that in mind through code.</p> <p><strong>Update</strong></p> <p>With Naval's suggestion, I tried a sub query. First, I set all date fields to NULL. Then, I run the following query:</p> <pre><code> strSQL3.CommandText = "Update tblItem set item_timed_close=DATEADD(minute,((Select count(*) as Count From tblItem Where item_sale_id=@item_sale_id And item_timed_close Is NULL) * @increment),convert(datetime, @item_timed_close)),item_timed_start=convert(money, case when item_est_lo &lt; 500 then ((convert(int,item_est_lo+25)/50)*50) when item_est_lo &lt; 1000 then ((convert(int,item_est_lo+50)/100)*100) when item_est_lo &lt; 3000 then ((convert(int,item_est_lo+125)/250)*250) when item_est_lo &lt; 5000 then ((convert(int,item_est_lo+250)/500)*500) else ((convert(int,item_est_lo+12.5)/25)*25) end ) Where item_sale_id=@item_sale_id"; strSQL3.Parameters.Add(new SqlParameter("@item_timed_close", SqlDbType.VarChar, 100, ParameterDirection.Input, true, 0, 0, "item_timed_close", DataRowVersion.Current, datetime_Var)); strSQL3.Parameters.Add(new SqlParameter("@item_sale_id", SqlDbType.Int, 5, ParameterDirection.Input, true, 0, 0, "item_sale_id", DataRowVersion.Current, itemSaleId3_Var)); strSQL3.Parameters.Add(new SqlParameter("@increment", SqlDbType.Int, 5, ParameterDirection.Input, true, 0, 0, "increment", DataRowVersion.Current, Increments_var)); </code></pre> <p>I was hoping that I could multiply the sub query by the variable passed in, but it didn't work as expected. It seems to multiply the <code>@increment</code> by 1 for every field. I've run the select statement alone and it returns 471. So I know that part works correctly. Do I need to cast <code>Count</code> as an integer or something?</p> <p><strong>DONE</strong></p> <p>I finally got it. I should have thought of this initially.... I had to change the counter variable in my update string's regular <code>Set</code> sequence. Here is my working query.</p> <pre><code> Declare @auto Int Set @auto = 0 Update tblItem set item_timed_close = DATEADD(minute, (@auto * @increment), convert(datetime, @item_timed_close)) , item_timed_start = convert(money, case when item_est_lo &lt; 500 then ((convert(int, item_est_lo+25)/50)*50) when item_est_lo &lt; 1000 then ((convert(int, item_est_lo+50)/100)*100) when item_est_lo &lt; 3000 then ((convert(int, item_est_lo+125)/250)*250) when item_est_lo &lt; 5000 then ((convert(int, item_est_lo+250)/500)*500) else ((convert(int,item_est_lo+12.5)/25)*25) end ) , @auto = (@auto + 1) Where item_sale_id=@item_sale_id </code></pre> <p>So I used the original example I had found, and just took out the last <code>Set @counter</code> part and put that into my update string's <code>set</code> parameters.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USFilip De Vos
    1. USJames
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POUpdate a list in SQL with incremental dates using one statement?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThe query you wrote looks flawed ... is it running correctly ?
      singulars
      1. POUpdate a list in SQL with incremental dates using one statement?
      1. USNaval
    2. CO@Naval Yes, as it is, it is running correctly. I just need it to also increment the date by the number of minutes the user specifies.
      singulars
      1. POUpdate a list in SQL with incremental dates using one statement?
      1. USJames
 

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