CODEWITHSUNDEEP
mysql
Shri
Shri

Reputation: 731

Update latest record

I have a following mysql query to update my tblcem_appraisal_log table

UPDATE tblcem_appraisal_log AS tblog
INNER JOIN (
            SELECT ta.id
            FROM tblcem_appraisal_log AS ta
            INNER JOIN tblcem_master_appraisal AS tm ON tm.id = ta.master_app_id        
            ORDER BY ta.id DESC
LIMIT 1) AS source ON source.id = tblog.id
SET tblog.quarter1 = tblcem_master_appraisal.quarter1 WHERE tblog.master_app_id=8;

I am getting an error unknown column tblcem_master_appraisal.quarter1 though column quarter1 is present in both tables. What exactly need to put in SET to update value from tblcem_master_appraisal table.

Upvotes: 0

Views: 41

Answers (2)

Shri
Shri

Reputation: 731

This works for me.

UPDATE tblcem_appraisal_log AS tblog
 INNER JOIN (
            SELECT ta.id, tm.quarter1 
            FROM tblcem_appraisal_log AS ta
            INNER JOIN tblcem_master_appraisal AS tm ON tm.id = ta.master_app_id 
            WHERE ta.master_app_id=8    
            ORDER BY ta.id DESC LIMIT 1) AS source ON source.id = tblog.id
SET tblog.quarter1 = source.quarter1;

Put where clause inside join

Upvotes: 0

Jens
Jens

Reputation: 69470

You have to select the column in your subselect and use the alias of that table:

  UPDATE tblcem_appraisal_log AS tblog
     INNER JOIN (
                SELECT ta.id, tm.quarter1 
                FROM tblcem_appraisal_log AS ta
                INNER JOIN tblcem_master_appraisal AS tm ON tm.id =    ta.master_app_id        
                ORDER BY ta.id DESC
    LIMIT 1) AS source ON source.id = tblog.id
    SET tblog.quarter1 = source.quarter1 WHERE tblog.master_app_id=8;

Upvotes: 1

Related Questions