MySQL: updating one table from another

I always find this a bit awkward, especially if there isn’t a straightforward (i.e. primary key) way of matching records across tables. I find the INNER JOIN syntax easier here — I always do a SELECT using the join criteria first, and inspect the results to make sure the right rows are getting matched, before doing an update.

UPDATE Property1 PI INNER JOIN Property2 TP ON
PI.column1=TP.column1 AND PI.column2=TP.propid
SET PI.seqno = TP.seqno
WHERE PI.recordtype=4 AND PI.seqno IS NULL

Note that I’m only updating one of the tables here, the one specified immediately after the UPDATE keyword.

This entry was posted in Databases. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>