Results 1 to 13 of 13
  1. #1
    Join Date
    Apr 2004
    Posts
    5

    Unanswered: Updating a table row, how?

    Okay i have a table in my database that has a few rows. There called:

    Userid
    Threadid
    date

    You can obvioulsly tell what there for.
    Anyway, I added a new row called 'threadtitle' but the values are all blank. Is there anyway that i can run a update query to add all the threadtitles to the proper threadid. All the thread ids have the correct threadid, but now i want to be able to use the thread title.

    Basicly, i just want to update the row threadtitle with the correct thread titles based on the threadid row. TIA!

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    1)
    update tableA ta
    INNER JOIN
    tableB tb ON
    ta.id = tb.id
    Set ta.title = tb.title;

    2)
    update tableA ta
    set ta.title = (Select tb.title from tableB tb where id = ta.id);
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "I added a new row called 'threadtitle' "

    you mean a new column?

    "All the thread ids have the correct threadid, but now i want to be able to use the thread title."

    okay, makes sense

    " just want to update the row threadtitle with the correct thread titles based on the threadid row"

    unfortunately, this doesn't -- make sense, that is

    could you please give a few sample rows of the table, before the update and what you want them to look like after?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by r937
    could you please give a few sample rows of the table, before the update and what you want them to look like after?
    Great analysis, and even better suggestion!

    -PatP

  5. #5
    Join Date
    Apr 2004
    Posts
    5
    Sorry guys. Here we go:


    --Hackinstall' (table)
    ----threadid (colum)
    ----dateinstalled (colum)
    ----userid (colum)
    ----threadtitle (colum)


    threadtitle is new, and empty. I want to update that colum with the thread titles. Someone said this could work

    PHP Code:
    <?php 
    require('global.php'); 

    $threads $DB_site->query("SELECT threadid, title FROM " TABLE_PREFIX "thread"); 
    while(
    $thread $DB_site->fetch_array($threads)) 

    $DB_site->query("UPDATE YourTable SET title='$thread[title]' WHERE threadid='$thread[threadid]'"); 

    echo 
    "Script Done"
    ?>
    Hope this helps.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by LiveWire
    threadtitle is new, and empty. I want to update that colum with the thread titles.
    where are the thread titles supposed to come from?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    I have already provided the solution.

    1)
    update tableA ta
    INNER JOIN
    tableB tb ON
    ta.id = tb.id
    Set ta.title = tb.title;

    2)
    update tableA ta
    set ta.title = (Select tb.title from tableB tb where id = ta.id);
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, robert, very nice syntax (which does not work in every database), but could you please make it relevant?

    which one is tableB?

    LiveWire has mentioned only one table so far
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Quote Originally Posted by r937
    which one is tableB?
    The table containing the source threadIDs.

    1) is for SQL Server
    2) is for Oracle.

    Both 1) and 2) assume that each source threadID has only one threadTitle.
    Last edited by r123456; 08-15-04 at 08:46.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well, i'm sure the original poster will be quite satisfied with that

    thanks for your efforts
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Apr 2004
    Posts
    5
    Your way didn't work, that script i posted above works perfect. Thanks for trying.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    whose way?

    and thanks for telling us that you have solved the problem

    your method doesn't scale, by the way

    any time you do an sql operation inside a loop, your performance goes way down

    no problem doing a one-shot update, but i wouldn't put something like that into production
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Quote Originally Posted by livewire
    SELECT threadid, title FROM " . TABLE_PREFIX . "thread")
    This would be tableB

    Therefore, given the code you supplied:
    Code:
    UPDATE YourTable SET title='$thread[title]' WHERE threadid='$thread[threadid]'");
    Substitute tableA with yourTable and tableB with tablePrefex.
    Last edited by r123456; 08-15-04 at 21:07.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

Posting Permissions

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