phpmyadmin Question posted in
The official documentation can be found here.

Updating all table Records with with its corresponding data from another table – Phpmyadmin

the thing is, i have a table students table and another scores table and i already have all the students in the scores table but the class field is still empty so, i thought i could update all of them with the data from the the students table. each students with his/her class.

UPDATE scores_tbl
    INNER JOIN students_tbl
        ON scores_tbl.reg_id = students_tbl.reg_id
    SET scores_tbl.class = IF(students_tbl.class > 0, students_tbl.class, scores_tbl.class) 
WHERE scores_tbl.session ="2018/2019";

am displayed with: Truncated incorrect DOUBLE value

Tags:

2

Answers


  1. 0

    try this

    UPDATE scores_tbl
    INNER JOIN students_tbl
        ON scores_tbl.reg_id = students_tbl.reg_id
    SET scores_tbl.class = IF(IFNULL(students_tbl.class, 0) > 0, IFNULL(students_tbl.class, 0), IFNULL(scores_tbl.class, 0)) 
WHERE scores_tbl.session ="2018/2019";
    Login or Signup to reply.
  2. 0

    You may try this. Since you only want to update the record if student_tbl.class has any value. It is better to write this condition in where clause and simplify your query.

    UPDATE scores_tbl
    INNER JOIN students_tbl
        ON scores_tbl.reg_id = students_tbl.reg_id
    SET scores_tbl.class = students_tbl.class 
WHERE scores_tbl.session ="2018/2019" and cast(students_tbl.class as int) > 0;

    I am expecting that class column has only integer values.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search