Reputation: 1005
I am trying to update some rows in a table from another row in a different table.
this is the sql I have so far:
UPDATE nymb_posts
JOIN nymb_postmeta
ON nymb_postmeta.post_id = nymb_posts.ID
WHERE nymb_postmeta.meta_key = "_wp_attached_file"
AND nymb_posts.post_type = "attachment"
AND nymb_posts.post_parent = "0"
SET nymb_posts.Guid = nymb_postmeta.meta_value
I just get an "error in your SQL syntax". If I remove the WHERE clause there is no error. If I make it a SELECT insead of an UPDATE the WHERE clause works. What is wrong with the WHERE clause?
Upvotes: 0
Views: 58
Reputation: 1269513
The set
goes before the where
:
UPDATE nymb_posts JOIN
nymb_postmeta
ON nymb_postmeta.post_id = nymb_posts.ID
SET nymb_posts.Guid = nymb_postmeta.meta_value
WHERE nymb_postmeta.meta_key = "_wp_attached_file" AND
nymb_posts.post_type = "attachment" AND
nymb_posts.post_parent = "0";
Upvotes: 3