Query for update datetime field in database

I have one table with IDateTime field and 1000 rows. I want to update for every 3 rows one date . Please help me in writing a query

for example

1 data1 2017/03/01
2 data2 2017/03/01
3 data3 2017/03/01
4 data4 2017/03/02
5 data5 2017/03/02
6 data6 2017/03/02
7 data7 2017/03/03
8 data8 2017/03/03
.
.

Could you please explain the overall purpose for this.

1 Like

Without knowing your database engine, it’s going to be a stab in the dark, and as benanamen is intimating, it’s a rather odd query request to make.

However, to at least give an answer to the original question, odd though it may be, it’s going to end up being something to the tune of

UPDATE tablename 
SET date = DATE_ADD(date, INTERVAL 1 DAY) 
WHERE MOD(id,3) = 0
1 Like

This query is not correct for topic question.

For solve the problem with simple single query author needs ROW_NUMBER(). But Problem is - ROW_NUMBER() available since MYSQL 8.0 Hm… Author has MYSQL as DB?

So… If author has old MYSQL there are two ways…

  1. Many queries with application as PHP.

  2. Complex multiple-table UPDATE query.

I would to chose variant 1.

1 Like

That… is absolutely NOT the 2 options. But thanks for playing.

If you need to bulk-apply a row modification, you do it mathematically, not by mass queries.

1 Like

If you’re saying you want to add a date column, where the dates are incremental based on the id field, then your two queries are something to the tune of

ALTER TABLE tablename ADD mydate date;
UPDATE tablename SET mydate = DATE_ADD("2017/03/01", INTERVAL DIV(id,3) DAY);

rather than 1000 queries.

1 Like

id is not ROW_NUMBER.

@igor_g, as @m_hutley said, No. As of the moment we do not now what the OP is really trying to do. Until we know that there isn’t much point at posting “solutions”. As pointed out, it is an odd request that doesn’t make much sense.

I want to write a query for sql server 2016

You need to provide more information than that if you want us to help you. I would suggest you read this before posting again.

UPDATE [dbo].[tables]

 SET [IDateTime]  = CONVERT(datetime, '2017/03/01')

This query updates all rows of IDateTime to '2017/03/01. But I need diffrenet date for every row. for example first row 2017/03/01 and second row 2017/03/02 …

How do I change this section to get the result?
CONVERT(datetime, ‘2017/03/01’)

To be absolutely sure what you’re asking for;
“I need different date for every row”… is the date in question… a random date? an incremental date? is it based off of something in the row (ID number?) or on the row number within the dataset?

a random date?
yes

is it based off of something in the row (ID number?)
no

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.