SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Addict
    Join Date
    May 2006
    Location
    Ljubljana
    Posts
    241
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    update instead of insert (3 tables)

    Hello,

    I wrote a simple sql select syntax which will select all desired rows (items):

    Code:
    SELECT p.post_title, t.name
    FROM
    wp_posts AS p,
    wp_terms AS t,
    wp_term_taxonomy AS x
    WHERE
    t.term_id = x.term_id AND
    x.taxonomy = 'category' AND
    t.name = 'CatName'
    I want to change it to UPDATE syntax which will set wp_posts.status to 'trash' each 'CatName' (the code above).

    How should I change it so?

    I tried with this (it wont work):
    Code:
    UPDATE p
    SET p.post_status = 'trash'
    FROM
    wp_posts AS p,
    wp_terms AS t,
    wp_term_taxonomy AS x
    WHERE
    t.term_id = x.term_id AND
    x.taxonomy = 'category' AND
    t.name = 'CatName'
    What am I doing wrong?

    Thanks for help!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,265
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    please check your syntax in da manual -- nowhere will you find SET followed by FROM
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    May 2006
    Location
    Ljubljana
    Posts
    241
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So I should just move the SET/FROM around and it should work?

  4. #4
    SitePoint Addict
    Join Date
    May 2006
    Location
    Ljubljana
    Posts
    241
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Heres the updated code (still wont work):

    Code:
    UPDATE
    wp_posts p,
    wp_terms t,
    wp_term_taxonomy x
    SET p.post_status = 'trash'
    WHERE
    t.term_id = x.term_id AND
    x.taxonomy = 'category' AND
    t.name = 'CatName'

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,265
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    what does "still won't work" mean?

    did it run or give an error?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Guru
    Join Date
    Jun 2006
    Posts
    638
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    He has no link to the P(wp_posts) table, so it can't work.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,265
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    actually, i think it can (although i'm not going to bother to test it)

    it will likely update every row of p, if i'm not mistaken
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Addict
    Join Date
    May 2006
    Location
    Ljubljana
    Posts
    241
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Vali View Post
    He has no link to the P(wp_posts) table, so it can't work.
    How should I do this?

    Quote Originally Posted by r937 View Post
    actually, i think it can (although i'm not going to bother to test it)

    it will likely update every row of p, if i'm not mistaken
    Exactly.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,265
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    in post #1, you said your SELECT "will select all desired rows"

    are you sure? because the failure to join properly should have returned all rows there, too
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Guru
    Join Date
    Jun 2006
    Posts
    638
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    When I said that "it can't work", i meant to say that it will not result in the expected result.

    Code:
    SELECT p.post_title, t.name
    FROM
    wp_posts AS p,
    wp_terms AS t,
    wp_term_taxonomy AS x
    WHERE
    t.term_id = x.term_id AND
    x.taxonomy = 'category' AND
    t.name = 'CatName'
    Is the same as:
    Code:
    SELECT p.post_title, t.name
    FROM
    wp_posts AS p
    You probably want something like:
    Code:
    SELECT p.post_title, t.name
    FROM
    wp_posts AS p,
    wp_terms AS t,
    wp_term_taxonomy AS x
    WHERE
    t.term_id = x.term_id AND
    x.taxonomy = 'category' AND
    t.name = 'CatName' AND
    p.term_id = t.term_id; // Link up the P table to something.
    Add more records in your database when testing.

  11. #11
    SitePoint Addict
    Join Date
    May 2006
    Location
    Ljubljana
    Posts
    241
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your help!

    One more thing:

    Why will the following code produce this error: #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 'LEFT JOIN wp_term_relationships AS r ON (p.ID = r.object_id) WHERE p.post_type ' at line 3

    Code:
    UPDATE wp_posts AS p
    SET p.post_status = 'trash'
    LEFT JOIN wp_term_relationships AS r ON (p.ID = r.object_id)
    WHERE p.post_type = 'post'
    AND r.term_taxonomy_id != 22
    I get the same result if I try this instead:
    Code:
    UPDATE p
    SET p.post_status = 'trash'
    FROM wp_posts AS p
    LEFT JOIN wp_term_relationships AS r ON (p.ID = r.object_id)
    WHERE p.post_type = 'post'
    AND r.term_taxonomy_id != 22
    What am I doing wrong?

    Thanks alot!

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,265
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by l2u View Post
    What am I doing wrong?
    you are not checking in da manual for the correct syntax, that's what

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •