"Kraer, Joseph" <jkraer@stripped> wrote on 11/30/2005 11:58:56 AM:
> I am trying to update a couple of rows in a table by doing the
> following:
>
> update table
> set column5 = number1
> where column 1 = number2
> and column1 = number3
>
> but it is not working. I tried listing the conditions separated by
> commas (where column 1 = number2, column1 = number3) and also didn't
> work. What am I doing wrong?
>
> Thanks in advance,
>
> Joseph "Tito" Kraer
> Business Systems Analyst
> Taylor, Bean & Whitaker Mortgage Corp
>
UPDATE statements are evaluated on a row-by-row basis. What you actually
told MySQL to do was to change any rows where column1 has BOTH values at
the same time. Since one column in a single row cannot possibly have two
values at once (not even SET columns because they do not have more than
one set of values at a single time) this condition cannot possibly be met
and the UPDATE will never happen. Make sense?
You said:
WHERE column1=number2 AND column1=number3
The AND is a logical comparator meaning that both comparisons must be true
for the WHERE clause to be true and your desired UPDATE to occur.
What I think you wanted to do is to UPDATE two rows. One row where
column1=number2 and the other row where column1=number3. That is an OR
situation not an AND situation:
WHERE column1=number2 OR column1=number3
Another way to write that condition is with an IN clause :
WHERE column1 IN (number2, number3)
HTH!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
