SQL and Variables? Help Needed

Discussion in 'MySQL' started by 15DigitalMarketing, May 30, 2007.

0
  1. #1
    My SQL problem is that I wish to carry a value from one table and find that value in another. Here is the code I have at the moment:

    $id = "SELECT `categories_id` FROM `categories_description` WHERE `categories_name` LIKE 'wxyz'";
    $sql = "UPDATE `categories` Set `sort_order`='10' WHERE `categories_id` = $id";

    So here I am finding the value of 'categories_id' from the 'categories_name' that I enter, which is good. But then I want to find that 'categories_id' in another table and update the record but unsure how to do it.

    If you need me to explain more then please ask.
     
    15DigitalMarketing, May 30, 2007 IP
  2. ansi

    ansi Well-Known Member

    Messages:
    1,483
    Likes Received:
    65
    Best Answers:
    0
    Trophy Points:
    100
    #2
    sounds to me like you need a join but i'm not quite sure what you are asking to be honest. please refer to en.wikipedia.org/wiki/Join_%28SQL%29 and dev.mysql.com/doc/refman/4.1/en/left-join-optimization.html for more information on joins.
     
    ansi, May 30, 2007 IP
  3. ansi

    ansi Well-Known Member

    Messages:
    1,483
    Likes Received:
    65
    Best Answers:
    0
    Trophy Points:
    100
    #3
    also, refer to this: dev.mysql.com/doc/refman/4.1/en/update.html

    your update query could be optimized to be only 1 query to the database. let mysql do the work, not you. plus that it would likely perform faster. not like you're going to notice it unless you're updating thousands of rows, but still a good idea and a way reduce overhead slightly.
     
    ansi, May 30, 2007 IP
  4. Clark Kent

    Clark Kent Guest

    Messages:
    122
    Likes Received:
    11
    Best Answers:
    0
    Trophy Points:
    0
    #4
    If you want to find categories starting with wxyz then you have to do like wxyz%, If you want to find categories end with wxyz then you have to do like %wxyz Or If you want to find categories contains wxyz then you have to do like %wxyz%

    Examples:
    $sql = "UPDATE categories Set sort_order=10 WHERE categories_id in (SELECT categories_id FROM categories_description WHERE categories_name LIKE 'wxyz%')";

    $sql = "UPDATE categories Set sort_order=10 WHERE categories_id in (SELECT categories_id FROM categories_description WHERE categories_name LIKE '%wxyz')";

    $sql = "UPDATE categories Set sort_order=10 WHERE categories_id in (SELECT categories_id FROM categories_description WHERE categories_name LIKE '%wxyz%')";


    Also you can use join in update but I don't like it :) Because you can't use LIMIT with update/inner join queries. You can check reference manual provided by @ansi if you want to do with inner join.

    Hope this helps..
    Clark
     
    Clark Kent, May 30, 2007 IP