Page 1 of 1

nesting 'where' statements

Posted: Thu Sep 30, 2004 4:00 pm
by dsdsdsdsd
hello;

I have a table, the_table:
____________________________
| column_1 | column_2 | column_3 |
|-------------|--------------|--------------|
|m_______|_________|________|
|howdy___| pen_____|_________|
|r________|_________|_________|
|howdy___| pencil____|_________|
|________|_________|_________|


I want to do something like this:
update the_table set
(column_2 ="paint") ,
(column_3 = "orange" where column_2="pencil")
where column_1 = "howdy"

I put parenthesis in there to help see the nesting;

any thoughts?
Shannon Burnett

Posted: Thu Sep 30, 2004 4:06 pm
by feyd
the docs say nothing about being able to do it.. so not sure it's possible.. it probably isn't.

Posted: Thu Sep 30, 2004 4:20 pm
by John Cartwright

Code: Select all

update the_table set
(column_2 ="paint") ,
(column_3 = "orange")
where column_1 = "howdy" && column_2 = "pencil"

Couldn't you just do that?

Posted: Thu Sep 30, 2004 4:23 pm
by feyd
I believe Shannon wants to alter both sets of rows in his table, with that query.

Posted: Thu Sep 30, 2004 4:38 pm
by dsdsdsdsd
phenom, thanks for your response;

yes, feyd is correct;

I am beginning to decide that I will just use multiple queries; however this gets very ugly because by the time you get to your third or fourth query many of the important values have been changed and thus your queries begin to not meet the conditions that are set out for them;

thanks
Shannon Burnett
Asheville NC USA

Posted: Fri Oct 01, 2004 4:40 am
by AGISB
dsdsdsdsd wrote:phenom, thanks for your response;

yes, feyd is correct;

I am beginning to decide that I will just use multiple queries; however this gets very ugly because by the time you get to your third or fourth query many of the important values have been changed and thus your queries begin to not meet the conditions that are set out for them;

thanks
Shannon Burnett
Asheville NC USA
This doesn't seem to make sense. It doesn't matter if you (theoretically) can put it in one query (which is not possible) or use different queries. You cannot be sure what data is changed first in your single query.

So the approach has to be to use different queries. This might involve a lot of programming code to check all conditions possible but it seems to be the only way. At least you know exactly what the data looks like after every query.

Posted: Fri Oct 01, 2004 4:57 pm
by John Cartwright
Words from the wise,

always expect the worse when letting users input anything :0

especially queries...