UPDATE, updating only a specific value

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

Moderator: General Moderators

thundza
Forum Newbie
Posts: 7
Joined: Fri Aug 06, 2004 9:44 pm

UPDATE, updating only a specific value

Post by thundza »

Hey, I am somewhat new to MySQL, but I understand (I think) the UPDATE Syntax... UPDATE tblname SET columnname = somevalue WHERE somecondition. But, lets say i have a database like this:

COLUMNA COLUMNB

X......................Y
X......................Y
X......................Y
X......................Y

... and it continues on like this, where every value in COLUMNA is the same, and every value in COLUMNB is the same... but, I want to UPDATE the database so it is like this.

COLUMNA COLUMNB

X......................Y
X......................K <----I only want to change this value to K
X......................Y
X......................Y

... but with my knowledge, i'm not sure how to update a specific value like that where conditional statements won't help. I'm sure it's possible though, and would appreciate any help.
User avatar
Joe
Forum Regular
Posts: 939
Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow

Post by Joe »

You should either add an integer column with auto_increment attributes or their may be a possibilty of jumping to the appropiate row, not too sure how though!
thundza
Forum Newbie
Posts: 7
Joined: Fri Aug 06, 2004 9:44 pm

Post by thundza »

I'd like to be able to do it without an ID column if that is possible. Anybody else know how to?
User avatar
Joe
Forum Regular
Posts: 939
Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow

Post by Joe »

You may be able to use FIRST with some kind of incrementation taking place, again not to sure!
thundza
Forum Newbie
Posts: 7
Joined: Fri Aug 06, 2004 9:44 pm

Post by thundza »

Hmmm... not sure about the FIRST command, but I'll search through the rest of the site and see if there is any way.
User avatar
Joe
Forum Regular
Posts: 939
Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow

Post by Joe »

I was not too sure there myself actually, hehe
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

update requires some unique way of identifying the record you wish to update, or you will update many rows.. You may be able to use conditionals like IF, or the loop WHILE, however I think those are a little more remote..
thundza
Forum Newbie
Posts: 7
Joined: Fri Aug 06, 2004 9:44 pm

Post by thundza »

Are there any ways you can specify (x,y) coordinates, like (3, 11), or whatever the cell's coordinates may be?
User avatar
Joe
Forum Regular
Posts: 939
Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow

Post by Joe »

Not that I know of :(
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I don't believe so, at least not one exposed through php. Could you explain why you are set against using an id?
thundza
Forum Newbie
Posts: 7
Joined: Fri Aug 06, 2004 9:44 pm

Post by thundza »

I don't have access to create new columns, so an ID wouldn't work.
User avatar
Joe
Forum Regular
Posts: 939
Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow

Post by Joe »

If you have access to creating and executing querys why cant you just create a new table via php/mysql?.
Last edited by Joe on Fri Aug 06, 2004 10:45 pm, edited 2 times in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

hmmm, then you may be able to create a stored procedure to alter it.. http://dev.mysql.com/doc/mysql/en/Store ... dures.html

In the example you gave, all the records were the same, is this true of the table you wish to modify?

Additionally, there is a potential to create a temporary table with consequtive numbers stored in it so you may "count" up to the row you need to alter...
thundza
Forum Newbie
Posts: 7
Joined: Fri Aug 06, 2004 9:44 pm

Post by thundza »

I'll look into stored procedures...

The table I wish to modify is more complex than the simple example I gave, but the same basic concept of multiple identical values.

A temporary table might be the answer, I just wish there was a faster way. Thanks for all your help everybody.
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

Ugly hack but:

Code: Select all

update tblname set COLUMNB = 'K' limit 3
update tblname set COLUMNB = 'Y' limit 2
If running these two directly after each other the result would be as expected according to the data provided in the original post. If this approach would work is depending on how the actual table youre about to use looks like...
Post Reply