...

View Full Version : condense 2 queries into 1



turpentyne
10-19-2011, 05:30 PM
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.




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)

Old Pedant
10-19-2011, 10:13 PM
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???

turpentyne
10-19-2011, 10:21 PM
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

turpentyne
10-20-2011, 01:37 AM
I think I figured it out, and it was as simple as I assumed:


UPDATE `table2` SET `hair_color`= NULL WHERE `hair_color`= "";

Unless somebody knows a way that this could be bad?

Old Pedant
10-20-2011, 10:35 PM
Sorry...I was out of it (ill) yesterday.

Yes, that's fine.

But you could have also simply changed your UPDATE to this:


UPDATE table2, table3
SET table2.hair_color = table3.hair_color)
WHERE table3.id = table2.id
AND IFNULL(table2.hair_color,'') = ''



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum