Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Regular Coder
    Join Date
    Aug 2010
    Posts
    418
    Thanks
    18
    Thanked 2 Times in 2 Posts

    update with value from nested query maybe?

    Not sure how I would do this, or if it's possible... Kind of an experiment.

    What I want is to pull all rows where column z starts with a 'like' value, then replace it with the id of a row from subquery match of that value to a second column. I'm not sure how to really explain it.. But here's a visual representation...


    so if I have rows like this:

    Code:
    ID              tpl_id                synonym_for
    ---------+------------------+------------------------
    12              key-161                key-9w0
    13              key-1w1                null
    14              key-asdf               key-1w1
    15              key-9w0                key-1w1

    I want to be able to update it to be this:

    Code:
    ID              tpl_id                synonym_for
    ---------+------------------+------------------------
    12              key-161                 15
    13              key-1w1                 null
    14              key-asdf                13
    15              key-9w0                 13
    I've only gotten as far as calling the results where the third column starts with key...

    UPDATE tablename SET synonym_for = ???? WHERE
    (SELECT id,synonym_for AS to_be_changed FROM `plantae` WHERE synonym_for LIKE 'key%' )


    From there, I just stared at the screen thinking... uhhhhmmmmm. I'm stumped.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,014
    Thanks
    79
    Thanked 4,436 Times in 4,401 Posts
    Part of the problem is that your "new" table does not and CAN NOT have the same STRUCTURE as your "old" table.

    Consider: The values of 15 and 13 in your "new" table a INTs, not VARCHARs, *AND* they are foreign keys to primary keys of other records in the table.

    I think that, instead of trying to UPDATE your existing table, you should be creating a brand new table with new columns.

    No?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,014
    Thanks
    79
    Thanked 4,436 Times in 4,401 Posts
    Oh...and why are you using LIKE in there, at all??? Your table values are exact matches; no need for LIKE. Are you sure you are telling us the truth about the contents of your table? Seems unlikely to me.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    Regular Coder
    Join Date
    Aug 2010
    Posts
    418
    Thanks
    18
    Thanked 2 Times in 2 Posts
    ah... I was using LIKE because I wanted to go through a range of items that started with certain characters... but different numbers.

    Between this and my previous question, I'm just trying to figure out some ways to speed up the importation of some large data sets from a different database. Everything matches up between the two tables, except that one column. It's kind of a tedious way.

    Currently I had to convert the column from integer to varchar for importing the info. Then, once I get the matching ids in a separate query, I do an update to the table. Then when I'm done, I can convert back to integer. It's tedious, but I can't seem to find a more efficient way to get such a large amount of data in.

  • #5
    Regular Coder
    Join Date
    Aug 2010
    Posts
    418
    Thanks
    18
    Thanked 2 Times in 2 Posts
    wha??? some how I double posted...

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,014
    Thanks
    79
    Thanked 4,436 Times in 4,401 Posts
    So give me REAL data to play with.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,079
    Thanks
    2
    Thanked 320 Times in 312 Posts
    Your series of threads have been moderately vague and changing (the defining information within one thread varies.)

    However, programming is an exact science. You cannot sneak up on a problem by trying to do one small part at a time without taking into account what the whole goal is. You end up doing the equivalent of painting yourself into a corner and must keep backtracking and rewriting code.

    You must be upfront, detailed, and consistent when trying to program or ask for help in a programming forum. Let us know the details of what you are trying to accomplish in the first post, don't paraphrase or alter what you are actually trying to accomplish, post example data that accurately reflects the real data type or just post real data.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.


  •  

    Posting Permissions

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