I want to write an update statement, but can't figure out the syntax.

if the value of column 'form' is = 'K' I want to set it to 'ck'

Yes, I read the manual, but when I try:
update payment if form = 'K' then set form = 'ck'
I get the standard error message:
#1064 - You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right
syntax to use near 'if form = 'K' then set form = 'ck'' at line 1

bill

bill wrote:

update payment
set form ='ck' where (form='K') and (whatever else you need here. Or
nothing to globally replace all insances of K in the field form of table
payment);

On Oct 1, 4:52*pm, The Natural Philosopher <t... (AT) invalid (DOT) invalid>
wrote:
Oh golly - right - I was so focused on using IF I missed the easy way.
Let me generalize the question then - when does one use the IF
statement and how ?

bill

bill wrote:
Oh.. well I guess its really there to set conditions on the output of a
select, not choose which actual rows to select/update in the first place.

I'm no DB guru, but its seems to make that what the DB software wants to
do first, is analyse what rows its going to operate on, and then get
those loaded, and that's the WHERE clause.

Then once its got its rows it uses the field statements to determine
which fields to grab, and after that you can use conditional and other
functions to make the data more presentable.

So you could say

UPDATE payment
set form=(IF(form='K','ck',form));
intsead of

UPDATE payment
set form ='ck' where (form='K');

without a WHERE clause but that means it's scanning every row in the
whole table. It may have to do that anyway to prepare the WHERE form='K'
in the other query, but if it has any index on 'form' it wont, and I am
not sure it doesn't have clever ways of doing that stuff anyway. Maybe a
guru knows.

So logically the two queries (if I got the syntax right) do the same
thing, but I bet one is faster than the other.








> bill

On Oct 1, 6:06*pm, The Natural Philosopher <t... (AT) invalid (DOT) invalid>
wrote:
Thanks
This came from a situation where the table was imported from a legacy
application and the encoding had changed.
K becomes ck
A becomes ca
M becomes mo

so I needed 3 if statements.
However, I decided to rewrite the importation program and re-import
the data.

And, yes, every row had to be read.
Now I am better educated and will be better ready for the next time,
many thanks for that.
bill

bill wrote:
I would have on a one off basis like that simply used three where
statements in three separate updates..I've done a load of this on my
database..

update product, categories set price = ("new_price") where
product.category = categories.id and categories.name="GENERIC
WIDGET"...and so on..

Three simple queries quicker to write and debug than one complex one.


files..;-)


Important thing with being a newbie is not to get distracted by people
who think there is one best solution.

Newbies have learning curves. Sometimes sticking to what you know, and
doing it three times, is quicker than learning what would be faster, if
you knew like the oldster does.

Plenty of time to learn the language beter when the pressure is off, the
data is imported, and the boss is shagging the secretrary again.

On 2010-10-02 15:45, bill wrote:
[...]
One alternative is to use a case stmt as in:

update payment
set form = case form when 'K' then 'ck'
when 'A' then 'ca'
when 'M' then 'mo'
end
where form in ('K','A','M');

It will at worst do one table scan

/Lennart

[...]

On Oct 2, 4:27*pm, The Natural Philosopher <t... (AT) invalid (DOT) invalid>
wrote:
Good points, especially the last one ! :-)

On Oct 2, 10:52*pm, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com>
wrote:
Thank you for another learning option.
bill

On Fri, 1 Oct 2010 14:02:30 -0700 (PDT), bill wrote:
One uses IF to conditionally set values that ARE NOT part of the
determination of whether a row needs updating. For example, if you want
to standardize ALL the entires less than a year old to STATUS =
'Current' if they're PRIORITY_CD = 'High' or less than a month old and
otherwise set them to STATUS = 'Slightly Stale'. Your WHERE clause will
be looking for the entries being less than a year old, and the IF will
handle determining what to set the STATUS to.


--
81. If I am fighting with the hero atop a moving platform, have
disarmed him, and am about to finish him off and he glances behind
me and drops flat, I too will drop flat instead of quizzically
turning around to find out what he saw. --Evil Overlord list