I am new to MySQL queries. I have to update all rows in a database with a date constructed from two other fields in that row, the update code below works for one row, but I need to loop through all rows and update.

UPDATE jobs SET job_date = CONCAT(job_year,"-",job_month,"-01") WHERE id = 2;

I have seen PROCEDURE, but don't know enough to get it to work.

DROP PROCEDURE IF EXISTS ROWPERROW;
DELIMITER ;;

CREATE PROCEDURE ROWPERROW()
BEGIN
DECLARE n INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
SELECT COUNT(*) FROM jobs INTO n;
SET i=0;
WHILE i<n DO 
    UPDATE jobs SET job_date = CONCAT(job_year,"-",job_month,"-01");
  SET i = i   1;
END WHILE;
End;
;;

Demo

CodePudding user response：

This works for one row:

UPDATE jobs SET job_date = CONCAT(job_year,"-",job_month,"-01") WHERE id = 2;

because you told it to update

WHERE id = 2;

If you get rid of that WHERE clause, it will update all rows.

You do not need a PROCEDURE.

CodePudding user response：

like lester said, remove where clause where id = 2

In my experience, this situation (create a varchar date column based on year and month column) does not occur very often, cuz most of the time, you get the exact time first and then you get year and month from that time.

If you can only get the year and month data from the data source, you should store the job_date column when insert into the table

If you can get the job_date column first, I think there are a few things you should consider

1. store job_date of datetime or timestamp type(they are slightly different) when insert the row
2. put index on job_date.
3. generally, you don`t need to create year/month column if there is a job_date column, if you want to extract the year / month value from mysql, you can use functions of mysql, like select job_date, year(job_date), month(job_state) from xxx
4. If you have to create column year/month, you should create two column year and month based on job_date, of integer type(or type year / tinyint), and create index on (year, month)