0 
UPDATE bad_table,good_table
SET bad_table.post_content = good_table.post_content    
WHERE bad_table.post_type = 'post' AND 
bad_table.post_date = good_table.post_date

This is my code for getting one whole column from one table to another, based on a date value, which I found is unique for this case. However, I get nothing.

If I select this, like so:

SELECT * FROM bad_table,good_table
WHERE bad_table.post_type = 'post' AND 
bad_table.post_date = good_table.post_date

...I get all the rows I would expect. What am I doing wrong?

Improve this question

1 Answer 1

Reset to default
2

Assuming there could be multiple entries in good_table that are not posts, you need to refine your join condition to only select 'posts' 

    update bad_table
inner join good_table on bad_table.post_date = good_table.post_date 
       and bad_table.post_type = good_table.post_type
       set bad_table.post_content = good_table.post_content
     where bad_table.post_type = 'post'
Improve this answer
5
  • thanks Jeff. Your code gives me an error though. #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 'inner join good_table on bad_table.post_date = good_table' at line 3
    – Adeerlike
    Commented Aug 7, 2011 at 15:15
  • Sorry about that, the set statement comes after the join. See answer for updated query.
    – Jeff Paquette
    Commented Aug 7, 2011 at 15:21
  • Thank you. just curious; do you have to also join table.post_type in order to later include it in the WHERE statement?
    – Adeerlike
    Commented Aug 7, 2011 at 17:43
  • No, I did the join on post_type to make sure that no other record types that had the same post_date were updated. I made that assumption about your data model simply because you had that post_type column.
    – Jeff Paquette
    Commented Aug 7, 2011 at 20:36
  • i actually made sure that the number of distinct post_date values which are post_type='post' is the same as the amount of posts i know of. thanks again.
    – Adeerlike
    Commented Aug 7, 2011 at 23:15

Not the answer you're looking for? Browse other questions tagged or ask your own question.