Jim
Jim

Reputation: 1005

sql update with join

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

Answers (2)

Gajendra Singh Rajput
Gajendra Singh Rajput

Reputation: 100

Use set before where clause then it will work

Upvotes: 0

Gordon Linoff
Gordon Linoff

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

Related Questions