nesting 'where' statements

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
dsdsdsdsd
Forum Commoner
Posts: 60
Joined: Fri Dec 05, 2003 3:10 pm

nesting 'where' statements

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

the docs say nothing about being able to do it.. so not sure it's possible.. it probably isn't.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I believe Shannon wants to alter both sets of rows in his table, with that query.
dsdsdsdsd
Forum Commoner
Posts: 60
Joined: Fri Dec 05, 2003 3:10 pm

Post 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
AGISB
Forum Contributor
Posts: 422
Joined: Fri Jul 09, 2004 1:23 am

Post 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.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Words from the wise,

always expect the worse when letting users input anything :0

especially queries...
Post Reply