Solved

UPDATE multiple rows with loop

Posted on 2006-11-14
10
778 Views
Last Modified: 2012-08-14
I have the table 'addr_im' that was altered by Adding a datetime  field called 'date_create'.
There are 1753 rows
I need to populate the 'date_create 'with a random Date in the last month

USING THIS FRO THE RANDOM DATE
$ranDate = date('Y/m/d h:m:s', mktime(rand(0,24),rand(0,60),rand(0,60),10,rand(1,30),2006));

I need to loop through each row and update 'date_create' with $ranDate

0
Comment
Question by:Scottshane
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
10 Comments
 
LVL 35

Assisted Solution

by:Raynard7
Raynard7 earned 250 total points
ID: 17943347
You should be able to do this with one statement

ie

Update addr_im set date_create = FROM_UNIXTIME(UNIX_TIMESTAMP(date_add('2006-10-01', interval round(rand()*30) day)) + SEC_TO_TIME(rand() * 60*60*24))
0
 
LVL 19

Accepted Solution

by:
VoteyDisciple earned 250 total points
ID: 17943351
Why not do it directly in the database with a similar structure.  Off the top of my head, I'm thinking...

UPDATE addr_im
SET date_create = DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 60 * 60 * 24 * 30) SECOND);

Presto, done in a single query.

That's a little rough in that it assumes a 30 day month, but you can fix that easily.
0
 
LVL 19

Expert Comment

by:VoteyDisciple
ID: 17943375
That's it; I'm just gonna have to stop taking the time to verify my syntax or something if I'm gonna be a couple seconds too late all the time.  (-;



I did want to add: databases are really good at doing loop-like stuff natively.  Any time you're thinking of writing a loop it's worth considering an SQL approach that will do the same thing.  It's certainly not a universal truth, but it's a pretty good bet that one exists.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 35

Expert Comment

by:Raynard7
ID: 17943385
How is my answer wrong? - it is more complete than voteys and gives you for october as you wanted.
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17943422
?
0
 
LVL 1

Author Comment

by:Scottshane
ID: 17943512
There are no comments in this thread stating that you answer's wrong.
His was the answer I chose.
and his mistake actually worked out to my advantage.

the end.
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17943527
Well,

The policy is if an answer is correct then the first correct answer should be given the points - or they should be shared.

The fact that my answer was correct and first and you did not accept it or give points for an assist means that it is incorrect - according to EE policy.

This is why I wanted to know why it was wrong - otherwise it should have been graded accordingly.
0
 
LVL 1

Author Comment

by:Scottshane
ID: 17943561
Ok, my bad I didn't read the Policy.
Is there a way to grade yo post facto?
If I can do that I will.
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17943594
Hi, you could post a requrest in the community support forum for the question to be re opened for grading.

You do not have to if you do not want to - I just do not like putting the effort in for a correct decision and then having it disregarded for one that is near identical - this was the third time it has happened today so that is why i was a little snappy - I apologise
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question