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 5 of 5
  1. #1
    Regular Coder
    Join Date
    Aug 2010
    Posts
    408
    Thanks
    17
    Thanked 2 Times in 2 Posts

    condense 2 queries into 1

    Is it possible to streamline these two queries into one?

    table 1 has: id, firstname, lastname, suffix
    table 2 has: id, hair color, other descriptions
    table 3 has: a blank id field, firstname, lastname, suffix, hair color

    Basically, I am uploading table 3 temporarily so I can update table 2 information based on the id's that match table 1 - without overwriting existing content.

    Code:
    step1:
    UPDATE table3, table1 
    SET table3.id  = IFNULL(table3.id, table1.id) 
    WHERE table1.lastname = test_table3.lastname 
    AND table1.firstname = test_table3.firstname 
    AND table1.suffix = test_table3.suffix
    
    step2:
    UPDATE table2, table3
    SET table2.hair_color  = IFNULL(table2.hair_color, table3.hair_color)
    WHERE  table3.id  = table2.id
    as an extra question: I've tested this in dummy tables. but on my main, I need to reset the field I want to change table2's hair color to NULL for the above to work. I can change the table descriptors to NULL default and I check the box for Null. But it doesn't change the existing entries to show NULL in empty fields. I can do this by going into each entry, and checking the box that appears next to that field. Is there a query to change all of these to Null if they have no content? Maybe it's simple answer, but I want to make sure so I don't NULL out all my data. (using mysql and phpMyAdmin 3.4.5, if that matters)
    Last edited by turpentyne; 10-19-2011 at 05:52 PM. Reason: subquestion...

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,441
    Thanks
    76
    Thanked 4,372 Times in 4,337 Posts
    You could do it in one query, but it would get more complex and I don't see any real advantage in doing so.

    Why would you want to set *ALL* of table2.hair_color to NULL? If there's already a value there would you *REALLY* want to do that???

    Or do you simply mean you want to treat BLANKS (that is blank strings) the same as NULLs???
    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
    Regular Coder
    Join Date
    Aug 2010
    Posts
    408
    Thanks
    17
    Thanked 2 Times in 2 Posts
    sorry for not clarifying....

    Yeah, I just want blank strings in that column to be null. I don't want to affect data that is in that column

  • #4
    Regular Coder
    Join Date
    Aug 2010
    Posts
    408
    Thanks
    17
    Thanked 2 Times in 2 Posts
    I think I figured it out, and it was as simple as I assumed:

    Code:
    UPDATE `table2` SET `hair_color`= NULL WHERE `hair_color`= "";
    Unless somebody knows a way that this could be bad?

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,441
    Thanks
    76
    Thanked 4,372 Times in 4,337 Posts
    Sorry...I was out of it (ill) yesterday.

    Yes, that's fine.

    But you could have also simply changed your UPDATE to this:
    Code:
    UPDATE table2, table3
    SET table2.hair_color  =  table3.hair_color)
    WHERE  table3.id  = table2.id
    AND IFNULL(table2.hair_color,'') = ''
    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.


  •  

    Posting Permissions

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