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?

Dan Grossman
  • 51,866
  • 10
  • 112
  • 101
Adeerlike
  • 457
  • 7
  • 22

1 Answers1

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'
Jeff Paquette
  • 7,089
  • 2
  • 31
  • 40
  • 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 Aug 07 '11 at 15:15
  • Sorry about that, the set statement comes after the join. See answer for updated query. – Jeff Paquette Aug 07 '11 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 Aug 07 '11 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 Aug 07 '11 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 Aug 07 '11 at 23:15