SQL: perform same operation on all rows

I'm using MySQL and I have a column of dates that were enterered into the database incorrectly. More specifically, they were entered in as dd/mm/yy but MySQL assumed they were in the format yyyy/mm/dd. I made the following code to correct this problem but there is an error stating that the subquery in the first line returns more than one row. How can I make it perform this operation on every row? Do I need a loop?

SET @raw_date = (SELECT j.a_date FROM tbl_job j);

SET @first = SPLIT_STR(@raw_date, '-', 1);
SET @second = SPLIT_STR(@raw_date, '-', 2);
SET @third = SPLIT_STR(@raw_date, '-', 3);

SET @first = (SELECT RIGHT(@first, 2));

SET @job_date = CONCAT(@third,'-',@second,'-',@first);

UPDATE tbl_job
SET tbl_job.a_date = @job_date;

Answers


SELECT j.a_date FROM tbl_job 
UPDATE tbl_job
SET tbl_job.a_date = @job_date

eliminate WHERE clause, it will update all rows...


Need Your Help

OnClick/Onhover Javascript/jquery

php javascript jquery css

Any ideas on how to create a Onclick/Hover event that would show a caption/bubble/popup that i would want to use as a quick help button. would appreciate any ideas or redirects on other sources/art...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.