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;
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...