Problems with using aliases on update mysql command

Question

I'm encountering a mysql syntax issue wit the following update query command and I'm not sure how to overcome this. It looks fine to me :/ could anyone help me out please?

This is the query:

UPDATE r 
SET r.status_code = 7, r.last_updated = now()
FROM record r
LEFT JOIN held_proposals h ON h.proposal_id = r.proposal_id
WHERE r.proposal_id = h.proposal_id AND h.end_date < now() AND r.student_record_id = 46;

this is the error:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM record r LEFT JOIN held_proposals h ON h.proposal_id = r.proposal_id WHE' at line 3

the table structure for each table:

held proposals:

held_proposals table

record:

record tabls

Really need to sort this out in the next 30 minutes. please help! thanks in advance

Jens · Accepted Answer · 2015-04-27 07:29:47Z

1

Your syntax is wrong.

Try this untested query:

UPDATE record r  LEFT JOIN held_proposals h ON h.proposal_id = r.proposal_id
SET r.status_code = 7, r.last_updated = now()
WHERE h.end_date < now() AND r.student_record_id = 46;

edited Apr 27, 2015 at 7:29

answered Apr 27, 2015 at 7:25

Jens

69.6k15 gold badges107 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user610 Over a year ago

Thanks for the quick response however its updated it for all the records and i need it to just update the ones which have the end date set to before now. any ideas?

user610 Over a year ago

basically i want to update the status for records in the record table if the end_date in the held_proposals table is set to before now(). so any records from a minute a go, a day ago. if you know what i mean.

Jens Over a year ago

@user610 so your query should work. or try DATE_SUB(now(),INTERVAL 1 MINUTE)

Collectives™ on Stack Overflow

Problems with using aliases on update mysql command

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related