473,588 Members | 2,471 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

UPDATE and increment by 10

Hi,

i want to update each field in a database table and increment by 10.

so it will look like

10, 20, 30, 40, 50

if anyone can help it would be great

Sep 4 '06 #1
16 3800
version2 napisal(a):
NTG, ...

update my_table set field = (field+10);

that should do.

Sep 4 '06 #2
i am looking at resetting all the values in the column and giving the
first value 10 htne 2 value 20 and so on

can you help?
Ac1d^ wrote:
version2 napisal(a):
NTG, ...

update my_table set field = (field+10);

that should do.
Sep 4 '06 #3
Ac1d^ wrote:
>NTG, ...

update my_table set field = (field+10);

that should do.
version2 wrote:
i am looking at resetting all the values in the column and giving the
first value 10 htne 2 value 20 and so on
Why don't you start by explaining what problem you're trying to solve
with this? It smells like a nasty hack to me.

Assuming you already have the incremental numbers, you can do

UPDATE my_table
SET field=(field*10 )

In addition, this has nothing to do with PHP. In future, you might want
to try comp.databases. mysql (or the appriate group for whatever DB you use)

Tim
Sep 4 '06 #4
"version2" <co************ @gmail.comwrote :
>
i am looking at resetting all the values in the column and giving the
first value 10 htne 2 value 20 and so on
"First" has no meaning in SQL. You can just do a SELECT with ORDER BY to
get the records in the order you want, then build the UPDATE commands one
by one.
--
- Tim Roberts, ti**@probo.com
Providenza & Boekelheide, Inc.
Sep 4 '06 #5
Does any know where a good change order for navigation script or
tutorial is?

Cause this is what i want to accomplish.

thanks

Sep 5 '06 #6
version2 wrote:
Does any know where a good change order for navigation script or
tutorial is?

Cause this is what i want to accomplish.
You sent me a private email on the subject, but if I'm going to help you
I'll do it in public so that other people can (potentially) benefit from it.

If you don't mind, I'll repeat what I understand of your problem to see
whether I've got it right. You have some a table with some elements that
you want to order in some way:

A 10
B 20
C 30
D 40
E 50

The second column being used to sort in the order you want. You're
leaving gaps between the numbers so that you can easily shuffle lines
around and get a different ordering, e.g.

A 10
C 30
B 35
D 40
E 50

Having done that you want to renumber the sort field so there are again
even gaps in it

A 10
C 20
B 30
D 40
E 50

The thing is, I think this is the wrong way to go about the task. If
what you want is an ordering, SQL can do that without you having to keep
track of the gaps between numbers yourself. Off the top of my head, you
could do something like:

A 1
B 2
C 3
D 4
E 5

And if you want to swap B and D, simply

-- Shunt all the contents after the target space up
UPDATE table
SET order_key=order _key + 1
WHERE order_key >= 4

-- Move target element to target space
UPDATE table
SET order_key=4
WHERE other_col='B'

-- Shunt back to fill the hole
UPDATE table
SET order_key=order _key - 1
WHERE order_key >= 3

There are probably better ways to do this, but maybe this puts you on
the right track.

Of course, with very large tables this solution may perform worse. But
the kind of data on which you would want to do this sort of manual
ordering is (I would imagine) not going to have large enough numbers to
make this significant. YMMV. Always test before you optimise.

Tim
Sep 5 '06 #7
version2 wrote:
Does any know where a good change order for navigation script or
tutorial is?

Cause this is what i want to accomplish.

thanks
OK, now what you're trying to do makes a lot more sense.

I think the easiest way to do this would be a combination of PHP and
SQL. You can do it all in SQL depending on the release, but this should
work with all releases.

Let's say your table contains, among other things:

id rank
1 10
2 20
3 30
4 40
5 50

And you want to swap 3 and 4.

You can add 15 t0 #3, as you indicated:

id rank
1 10
2 20
3 45
4 40
5 50

Now, to get them in the order:

id rank
1 10
2 20
4 30
3 40
5 50
$rank = 10;
$result1 = mysql_query('SE LECT id, rank FROM mytable ORDER BY rank');
while ($data = mysql_fetch_res ult($result1)) {
$result2 = mysql_query("UP DATE mytable SET rank=$rank WHERE id =
{$data['id']});
$rank += 10;
}
--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attgl obal.net
=============== ===
Sep 5 '06 #8
Jerry you are the only one so far to understand,

And i do think this is a good idea.

I have implemented the code but there was an error with
"mysql_fetch_re sult"

here is the current setup. Could you have a look and tell me what i am
doing wrong

index.php

$HTMLOutput .= "<tr>
<td align=\"center\ ">".$record[exerciseName]."</td>
<td align=\"center\ "><a href =
'item_position. php?action=move up&ID=".$recor d[ID]."&muscleName=" .$record[ArtistType]."'>move
up</a>".$record[position]."</td>
</tr>";
item_position.p hp
<?

if ($_GET["action"] == "moveup") {

$muscleName=$_G ET['muscleName'];
$ID = $_GET["ID"];
$rank = 10;

//this is where i am adding in the 15
$phase1 = "UPDATE ".DB_PREFIX."". TBL_PREFIX." SET position = position +
15 WHERE ID = $ID;";
//this is where i am calling the $phase1 to run
mysql_query($ph ase1);
//this is your code i am tring to implement
$result1 = mysql_query("SE LECT id, position FROM
".DB_PREFIX."". TBL_PREFIX." ORDER BY position");

while ($data = mysql_fetch_res ult($result1)) {
$result2 = mysql_query("UP DATE ".DB_PREFIX."". TBL_PREFIX." SET
position=$posit ion WHERE id = {$data['id']}");
$rank += 10;
}
header("Locatio n: index.php");
exit;

}

?>

Sep 5 '06 #9
version2 wrote:
Jerry you are the only one so far to understand,

And i do think this is a good idea.

I have implemented the code but there was an error with
"mysql_fetch_re sult"

here is the current setup. Could you have a look and tell me what i am
doing wrong

index.php

$HTMLOutput .= "<tr>
<td align=\"center\ ">".$record[exerciseName]."</td>
<td align=\"center\ "><a href =
'item_position. php?action=move up&ID=".$recor d[ID]."&muscleName=" .$record[ArtistType]."'>move
up</a>".$record[position]."</td>
</tr>";
item_position.p hp
<?

if ($_GET["action"] == "moveup") {

$muscleName=$_G ET['muscleName'];
$ID = $_GET["ID"];
$rank = 10;

//this is where i am adding in the 15
$phase1 = "UPDATE ".DB_PREFIX."". TBL_PREFIX." SET position = position +
15 WHERE ID = $ID;";
//this is where i am calling the $phase1 to run
mysql_query($ph ase1);
//this is your code i am tring to implement
$result1 = mysql_query("SE LECT id, position FROM
".DB_PREFIX."". TBL_PREFIX." ORDER BY position");

while ($data = mysql_fetch_res ult($result1)) {
$result2 = mysql_query("UP DATE ".DB_PREFIX."". TBL_PREFIX." SET
position=$posit ion WHERE id = {$data['id']}");
$rank += 10;
}
header("Locatio n: index.php");
exit;

}

?>
OK, what error do you get?

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attgl obal.net
=============== ===
Sep 6 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

19
8687
by: Westcoast Sheri | last post by:
To keep track of how many fruits my visitors buy, I use a mySQL database (2 columns: "fruit" and "quantity")....so can we make these following mySQL queries work somehow? (visitor buys 5 apples): replace into fruit_database set fruit = 'apple' , quantity = quantity + 5; (visitor buys 7 apples): replace into fruit_database set fruit = 'apple' , quantity = quantity +
6
3366
by: Paul Eden | last post by:
Hello all. I'm totally stumped. Please de-stump me! I've read my data in from the database, changed it according to what i want to do and now I want to write it back. But it seems I can only use the update statement will only accept explicit fieldnames and value. The fieldnames I can live with, I only have 5 columns in the database, but why can't I pass a $variable to this statement?
0
2621
by: braedsjaa | last post by:
Just starting out with mySql / php ... hope this is a simple question and my description is understandable: I want to present table data from one row in one webpage with links to the previous and next row/page. The way I propose to do this is to have a field called SortOrder with values assigned incrementally so that each webpage 'knows' that the next page should have SortOrder_next == SortOrder_this + 1 and the previous page...
33
4262
by: Lee C. | last post by:
I'm finding this to be extremely difficult to set up. I understand that Access won't manage the primary key and the cascade updates for a table. Fine. I tried changing the PK type to number and setting default value to a UDF that manages the auto-numbering. Access won't take a UDF as a default value. Okay, I'll use SQL WITHOUT any aggregate functions, for the default value. Access won't do that either. Okay, I create a second...
8
3704
by: Maxi | last post by:
There is a lotto system which picks 21 numbers every day out of 80 numbers. I have a table (name:Lotto) with 22 fields (name:Date,P1,P2....P21) Here is the structure and sample data: "Date","P1","P2","P3","P4","P5","P6","P7","P8","P9","P10","P11","P12","P13","P14","P15","P16","P17","P18","P19","P20","P21" 1/1/2005,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21 1/2/2005,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22
5
9825
by: Wing | last post by:
Hi all, I am writing a function that can change the value "Quantity" in the selected row of MS SQL table "shoppingCart", my code is showing below ------------------------------------------------------ static void addQuantity(int rowNum, int oldQuantity) { int newQuantity = oldQuantity++; SqlConnection thisConnection = new SqlConnection (@"Data Source=(local);Integrated Security=SSPI;" +
6
2603
by: Tom Allison | last post by:
I seemed to remember being able to do this but I can't find the docs. Can I run a sql query to insert new or update existing rows in one query? Otherwise I have to run a select query to see if it's there and then another one to update/insert. What I'm trying to do is create a counter for each key, insert a value of 1 or increment the value by 1 and then set another specific row (where key = $key) to always increment by 1.
1
1302
by: Hexman | last post by:
Hello Again, I have a table that contains data that needs to be ranked. There are actually 2 columns that need to be ranked. I have already completed the task but did it in a "Brute Force" way. I created a datatable did a select (got 72,000 recs) and order by. Looped thru the dt updating each record with the ranking value and accepting changes. I copied that program and used the new criteria for selection (got 33,000 recs) and order by...
5
2499
by: logina1 | last post by:
hi all, This command update table1 set col4 = rownum; updates all the records with auto increment number.But i want to increment based on date. i.e when the date changes it need to start from 1. for ex: col1 col2 col3 ----------------------------------- 01-jun-2007 a aa 01-jun-2007 b bb 02-jun-2007 a aa 01-jun-2007 b bb
1
3187
by: jimilives | last post by:
I forgot to turn on auto increment when I reinstalled this database and now I have a bunch of NULL values in my ID field for last few hundred inserts, how can I update this table to replace the NULLS with a new ID value where the ID is an auto increment with out losing the ID values of old records, basically i want to do this: UPDATE ID SET id = 'NEW AUTO #' where ID = 'NULL' I hope this makes sense, I am not bound to having the numbers...
0
7862
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
8228
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8357
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8223
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
6634
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
5729
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
3847
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
2372
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
1196
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.