By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,665 Members | 1,382 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,665 IT Pros & Developers. It's quick & easy.

With INSERT can I increment an existing value in a column?

P: n/a

Folks,

I have a table of addresses and a seperate table with contact names -
All addresses tie to one or more names - I would like to keep track of
the number of names 'belonging' to an address and have thus included a
column in my address table called num_of_contacts. Everytime I add a
new contact, I would like to increment the num_of_contacts column in the
address table.

Is this possible?

Me thinks not (or at least, my attempts so far have failed me) so I'd
appreciate it if someone could tell me if I am wasteing my time trying
and instead SELECT the record, increment it, then UPDATE it.

cheers
randell d.
Jul 23 '05 #1
Share this Question
Share on Google+
11 Replies


P: n/a
On Wed, 16 Feb 2005 09:17:59 GMT, in mailing.database.mysql "Randell
D." <re******************************@fiprojects.moc > wrote:
|
| Folks,
|
| I have a table of addresses and a seperate table with contact names -
| All addresses tie to one or more names - I would like to keep track of
| the number of names 'belonging' to an address and have thus included a
| column in my address table called num_of_contacts. Everytime I add a
| new contact, I would like to increment the num_of_contacts column in the
| address table.
|
| Is this possible?
|
| Me thinks not (or at least, my attempts so far have failed me) so I'd
| appreciate it if someone could tell me if I am wasteing my time trying
| and instead SELECT the record, increment it, then UPDATE it.


Its not a good idea storing calculated values into a table. Use
queries to return the most up-to-date information.

---------------------------------------------------------------
jn******@yourpantsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------
Jul 23 '05 #2

P: n/a

"Randell D." <re******************************@fiprojects.moc > wrote in
message news:bdEQd.405430$8l.184243@pd7tw1no...

Folks,

I have a table of addresses and a seperate table with contact names -
All addresses tie to one or more names - I would like to keep track of
the number of names 'belonging' to an address and have thus included a
column in my address table called num_of_contacts. Everytime I add a
new contact, I would like to increment the num_of_contacts column in the
address table.

Is this possible?
Possible - yes. Good idea - no!

Your database is designed to count those names (contacts) for you. You want
to form a SELECT query that will yield up your [num_of_contacts]. Done in
this way your number will always be correct at the moment you ask the
question. As a rule, you never want to store a number that you can calculate
on the fly from existing information. If you need help, give a few more
details and someone will help you design a proper SELECT query that will
yield the COUNT you need.
Me thinks not (or at least, my attempts so far have failed me) so I'd
appreciate it if someone could tell me if I am wasteing my time trying
and instead SELECT the record, increment it, then UPDATE it.


You would be wasting your time trying to UPDATE a field from inside a SELECT
statement. You will have to increment it *using* a separate UPDATE
statement.
UPDATE addresses
WHERE {RecId} = id
SET num_of_contacts = num_of_contacts + 1

Assuming you really needed to do that!
Thomas Bartkus
Jul 23 '05 #3

P: n/a
Thomas Bartkus wrote:
"Randell D." <re******************************@fiprojects.moc > wrote in
message news:bdEQd.405430$8l.184243@pd7tw1no...
Folks,

I have a table of addresses and a seperate table with contact names -
All addresses tie to one or more names - I would like to keep track of
the number of names 'belonging' to an address and have thus included a
column in my address table called num_of_contacts. Everytime I add a
new contact, I would like to increment the num_of_contacts column in the
address table.

Is this possible?

Possible - yes. Good idea - no!

Your database is designed to count those names (contacts) for you. You want
to form a SELECT query that will yield up your [num_of_contacts]. Done in
this way your number will always be correct at the moment you ask the
question. As a rule, you never want to store a number that you can calculate
on the fly from existing information. If you need help, give a few more
details and someone will help you design a proper SELECT query that will
yield the COUNT you need.

Me thinks not (or at least, my attempts so far have failed me) so I'd
appreciate it if someone could tell me if I am wasteing my time trying
and instead SELECT the record, increment it, then UPDATE it.

You would be wasting your time trying to UPDATE a field from inside a SELECT
statement. You will have to increment it *using* a separate UPDATE
statement.
UPDATE addresses
WHERE {RecId} = id
SET num_of_contacts = num_of_contacts + 1

Assuming you really needed to do that!
Thomas Bartkus


Thanks for that... the last bit is likely to prove more useful for me
though I understand the arguements for using such a facility.

The reason why I thought the idea might be a good idea is I could have a
table of several thousand contacts and say only five might tie to one
particular address (there is no maximum number of contact names that
might tie to an address).

If I were to perform a select on the contacts table, I would have to
search the entire table every time regardless if I was looking for
zero, one, five or fifty contact names and I didn't think that would be
a good idea. I know MySQL can handle alot more records than my database
will contain, however I was hoping to come up with a best practice
method... I mean... just because I have the resources on my system does
not mean I have to use them if I don't have to use the, true?

anyway... thanks... you've given me some food for my thoughts,
Randell D.
Jul 23 '05 #4

P: n/a

"Randell D." <re******************************@fiprojects.moc > wrote in
message news:2gOQd.409212$8l.50657@pd7tw1no...
Thomas Bartkus wrote:
"Randell D." <re******************************@fiprojects.moc > wrote in
message news:bdEQd.405430$8l.184243@pd7tw1no...
Folks,

I have a table of addresses and a seperate table with contact names -
All addresses tie to one or more names - I would like to keep track of
the number of names 'belonging' to an address and have thus included a
column in my address table called num_of_contacts. Everytime I add a
new contact, I would like to increment the num_of_contacts column in the
address table.

Is this possible?

Possible - yes. Good idea - no!

Your database is designed to count those names (contacts) for you. You want to form a SELECT query that will yield up your [num_of_contacts]. Done in this way your number will always be correct at the moment you ask the
question. As a rule, you never want to store a number that you can calculate on the fly from existing information. If you need help, give a few more
details and someone will help you design a proper SELECT query that will
yield the COUNT you need.

Me thinks not (or at least, my attempts so far have failed me) so I'd
appreciate it if someone could tell me if I am wasteing my time trying
and instead SELECT the record, increment it, then UPDATE it.

You would be wasting your time trying to UPDATE a field from inside a SELECT statement. You will have to increment it *using* a separate UPDATE
statement.
UPDATE addresses
WHERE {RecId} = id
SET num_of_contacts = num_of_contacts + 1

Assuming you really needed to do that!
Thomas Bartkus


Thanks for that... the last bit is likely to prove more useful for me
though I understand the arguements for using such a facility.


The rest of your message suggests otherwise. I don't think you do understand
the arguments.
The reason why I thought the idea might be a good idea is I could have a
table of several thousand contacts and say only five might tie to one
particular address (there is no maximum number of contact names that
might tie to an address).
You seem to be wrestling (unnecessarily) with a problem that anyone here
could help you with. Why not show us how you "tie to one particular
address" your contacts? What does your table structure look like?
If I were to perform a select on the contacts table, I would have to
search the entire table every time regardless if I was looking for
zero, one, five or fifty contact names and I didn't think that would be
a good idea.
Wrong! It would be a *good* idea to do exactly that which you are avoiding.
Your database is optimized precisely for this kind of problem. You *do* want
to "to
search the entire table every time regardless ...".

Where I think you are messing up is in thinking that you have to loop
through thousands of records to get your count. Not so! What you need is to
learn wise use of the COUNT() function and let MySQL do it.
I know MySQL can handle alot more records than my database
will contain, however I was hoping to come up with a best practice
method... I mean... just because I have the resources on my system does
not mean I have to use them if I don't have to use the, true?
"Best practice" means you are going to let the database return the counts
for you on the fly.
anyway... thanks... you've given me some food for my thoughts,


Your not eating right!
Thomas Bartkus
Jul 23 '05 #5

P: n/a
Thomas Bartkus wrote:
"Randell D." <re******************************@fiprojects.moc > wrote in
message news:2gOQd.409212$8l.50657@pd7tw1no...
Thomas Bartkus wrote:
"Randell D." <re******************************@fiprojects.moc > wrote in
message news:bdEQd.405430$8l.184243@pd7tw1no...
Folks,

I have a table of addresses and a seperate table with contact names -
All addresses tie to one or more names - I would like to keep track of
the number of names 'belonging' to an address and have thus included a
column in my address table called num_of_contacts. Everytime I add a
new contact, I would like to increment the num_of_contacts column in the
address table.

Is this possible?
Possible - yes. Good idea - no!

Your database is designed to count those names (contacts) for you. You
want
to form a SELECT query that will yield up your [num_of_contacts]. Done
in
this way your number will always be correct at the moment you ask the
question. As a rule, you never want to store a number that you can
calculate
on the fly from existing information. If you need help, give a few more
details and someone will help you design a proper SELECT query that will
yield the COUNT you need.

Me thinks not (or at least, my attempts so far have failed me) so I'd
appreciate it if someone could tell me if I am wasteing my time trying
and instead SELECT the record, increment it, then UPDATE it.
You would be wasting your time trying to UPDATE a field from inside a
SELECT
statement. You will have to increment it *using* a separate UPDATE
statement.
UPDATE addresses
WHERE {RecId} = id
SET num_of_contacts = num_of_contacts + 1

Assuming you really needed to do that!
Thomas Bartkus


Thanks for that... the last bit is likely to prove more useful for me

> though I understand the arguements for using such a facility.


The rest of your message suggests otherwise. I don't think you do understand
the arguments.

The reason why I thought the idea might be a good idea is I could have a
table of several thousand contacts and say only five might tie to one
particular address (there is no maximum number of contact names that
might tie to an address).

You seem to be wrestling (unnecessarily) with a problem that anyone here
could help you with. Why not show us how you "tie to one particular
address" your contacts? What does your table structure look like?

If I were to perform a select on the contacts table, I would have to
search the entire table every time regardless if I was looking for
zero, one, five or fifty contact names and I didn't think that would be
a good idea.

Wrong! It would be a *good* idea to do exactly that which you are avoiding.
Your database is optimized precisely for this kind of problem. You *do* want
to "to
search the entire table every time regardless ...".

Where I think you are messing up is in thinking that you have to loop
through thousands of records to get your count. Not so! What you need is to
learn wise use of the COUNT() function and let MySQL do it.

I know MySQL can handle alot more records than my database
will contain, however I was hoping to come up with a best practice
method... I mean... just because I have the resources on my system does
not mean I have to use them if I don't have to use the, true?

"Best practice" means you are going to let the database return the counts
for you on the fly.

anyway... thanks... you've given me some food for my thoughts,

Your not eating right!
Thomas Bartkus


Sorry... didn't realise there was a follow on post...

I think we both have a misunderstanding... or maybe its just me...

I don't want to count the records... I want to perform a select and
have a numeric value that I can assign to LIMIT... roughly speaking,
this is why:

table:address
It contains numerous addresses, no names - each address has a hash
(There's a reason as to why I'm not using unique numeric IDs... long
story but ignore it for now).

table:contacts
It contains names, and a cell for address_hash. Thus for each name, I
will have an address to tie it to. I permit no limit on names that can
be tied to a single address.

My 'problem':
I retrieve an address and want to find out all the names that are tied
to it... I know I could just do:

SELECT contacts.firstname,contacts.lastname,address.line_ 1
FROM contacts,address
WHERE contacts.address_hash='$myhashkey'
AND address.hash='$myhashkey';

However since my select does not know how many contact names are tied to
the address, it will search through the entire contacts table, true?

If I was to suffix the above query with a LIMIT condition, this would
help, true? The problem is, I don't know how many names *might* be
reitrieved from the contacts table hence why it would be useful if I had
a value for addres.num_of_contacts, as I could use its value as part of
my LIMIT criteria.

True/False?

Have you an alternative solution?

Thanks for the (constructive) criticism - I'm doing MySQL for about a
year plus now though not in a working environment so I'm open to all
comments that help improve my skillset.

Cheers
Randell D.
Jul 23 '05 #6

P: n/a
"Randell D." <re******************************@fiprojects.moc > wrote in
message news:PZaRd.416462$8l.385840@pd7tw1no...

I don't want to count the records... I want to perform a select and
have a numeric value that I can assign to LIMIT... roughly speaking,
this is why:
You go on but you never do explain why - speaking roughly or otherwise.
What in heck kind of number do you need to give to LIMIT? What do you
expect it to do for you?

If you simply needed to paginate, LIMIT does it all
LIMIT 25, StartRecNo
gets you 25 records at a time if you simply increment [StartRecNo] by 25
each iteration through your paginating loop.

BUT That's just a guess because you never do state what it is you are trying
to achieve!
table:address
It contains numerous addresses, no names - each address has a hash
(There's a reason as to why I'm not using unique numeric IDs... long
story but ignore it for now).
Good! I'm ignoring :-)
table:contacts
It contains names, and a cell for address_hash. Thus for each name, I
will have an address to tie it to. I permit no limit on names that can
be tied to a single address.
So what?
You seem to think this is some kind of big deal, but I see nothing other
than routine database stuff here.
My 'problem':
I retrieve an address and want to find out all the names that are tied
to it... I know I could just do:

SELECT contacts.firstname,contacts.lastname,address.line_ 1
FROM contacts,address
WHERE contacts.address_hash='$myhashkey'
AND address.hash='$myhashkey';
Okay!
However since my select does not know how many contact names are tied to
the address, it will search through the entire contacts table, true?
True. But why is this at all bothersome?
Are you not indexing?
MySQL was designed to eat problems like this for lunch.

Why/how would SELECT know or care how many records there are ahead of it.
It doesn't! The whole point of SELECT is to find out for heavens sakes!
If I was to suffix the above query with a LIMIT condition, this would
help, true?
Help what? What can this possibly do for you?
If you have a reason *you* can't accept an indeterminate flood of records,
then by all means LIMIT. I assure you, MySQL won't care one way or the
other.

But your question "would this help?" is indecipherable.
It won't help you get a date. It won't help you make money. It won't improve
your health

AND it won't help MySQL do it's job!
The problem is, I don't know how many names *might* be
reitrieved from the contacts table hence why it would be useful if I had
a value for addres.num_of_contacts, ...
If *you* need to know "how many names *might* be reitrieved from the
contacts table" then it is incumbent on *you* to ask.

SELECT COUNT(contacts.*) as NumberOfNames
FROM contacts,address
WHERE contacts.address_hash='$myhashkey'
AND address.hash='$myhashkey';

MySQL won't mind if you ask - honest!
as I could use its value as part of my LIMIT criteria.
True/False?
In order to achieve *what* ?
If you were expecting it to somehow make your query more efficient, the
answer is most certainly False.
Have you an alternative solution?
Heck - I still don't even have the problem.
Thanks for the (constructive) criticism - I'm doing MySQL for about a
year plus now though not in a working environment so I'm open to all
comments that help improve my skillset.


Hey - I'm trying!
Thomas Bartkus
Jul 23 '05 #7

P: n/a
Randell D. wrote:
I retrieve an address and want to find out all the names that are tied
to it... I know I could just do:

SELECT contacts.firstname,contacts.lastname,address.line_ 1
FROM contacts,address
WHERE contacts.address_hash='$myhashkey'
AND address.hash='$myhashkey';


I would write this query slightly differently:

SELECT c.firstname, c.lastname, a.line_1
FROM contacts AS c INNER JOIN address AS a
ON c.address_hash = a.hash
WHERE a.hash = '$myhashkey'

You don't need to worry about the number of contacts that match the
address. The query above returns only those entries that match, whether
that is zero, 1, or more.

You should put indexes on c.address_hash and a.hash, so MySQL can look
up these values quickly using the indexes. MySQL doesn't have to read
the whole table that way.

You're concerned about the efficiency and performance of the query, and
that's good, but be smart about where you focus your efforts; don't
solve performance problems until you can prove that they exist and are
in fact leading to a performance bottleneck in your application.
There's no sense making work for yourself to solve one performance
issue, if other parts of the application are still going to be the
limitation on performance. For what it's worth, I have yet to find any
simple query on a dataset of a few thousand rows become a bottleneck;
it's more likely that the routine that outputs the result takes more time.

Also, I agree with the other folks that storing the number of contacts
per address is not recommended. There's too much risk that this value
might become out of sync with the state of the data. Incorrect results
are far worse than slow performance.

Regards,
Bill K.
Jul 23 '05 #8

P: n/a
Bill Karwin wrote:
Randell D. wrote:
I retrieve an address and want to find out all the names that are tied
to it... I know I could just do:

SELECT contacts.firstname,contacts.lastname,address.line_ 1
FROM contacts,address
WHERE contacts.address_hash='$myhashkey'
AND address.hash='$myhashkey';

I would write this query slightly differently:

SELECT c.firstname, c.lastname, a.line_1
FROM contacts AS c INNER JOIN address AS a
ON c.address_hash = a.hash
WHERE a.hash = '$myhashkey'

You don't need to worry about the number of contacts that match the
address. The query above returns only those entries that match, whether
that is zero, 1, or more.

You should put indexes on c.address_hash and a.hash, so MySQL can look
up these values quickly using the indexes. MySQL doesn't have to read
the whole table that way.

You're concerned about the efficiency and performance of the query, and
that's good, but be smart about where you focus your efforts; don't
solve performance problems until you can prove that they exist and are
in fact leading to a performance bottleneck in your application. There's
no sense making work for yourself to solve one performance issue, if
other parts of the application are still going to be the limitation on
performance. For what it's worth, I have yet to find any simple query
on a dataset of a few thousand rows become a bottleneck; it's more
likely that the routine that outputs the result takes more time.

Also, I agree with the other folks that storing the number of contacts
per address is not recommended. There's too much risk that this value
might become out of sync with the state of the data. Incorrect results
are far worse than slow performance.

Regards,
Bill K.

Thanks for re-writing the query with the inner join - Joins are new to
me and I'm only just begining to understand it.

I'm glad you can see that I was concentrating on the efficiency and
performance of the query - but I also understand your arguements as to
why I might well be wasteing my time. I'll leave off it for the moment
and investigate it at a later stage.

Thanks,

randelld
Jul 23 '05 #9

P: n/a
Thomas Bartkus wrote:
"Randell D." <re******************************@fiprojects.moc > wrote in
message news:PZaRd.416462$8l.385840@pd7tw1no...
I don't want to count the records... I want to perform a select and
have a numeric value that I can assign to LIMIT... roughly speaking,
this is why:

You go on but you never do explain why - speaking roughly or otherwise.
What in heck kind of number do you need to give to LIMIT? What do you
expect it to do for you?

If you simply needed to paginate, LIMIT does it all
LIMIT 25, StartRecNo
gets you 25 records at a time if you simply increment [StartRecNo] by 25
each iteration through your paginating loop.

BUT That's just a guess because you never do state what it is you are trying
to achieve!

table:address
It contains numerous addresses, no names - each address has a hash
(There's a reason as to why I'm not using unique numeric IDs... long
story but ignore it for now).

Good! I'm ignoring :-)

table:contacts
It contains names, and a cell for address_hash. Thus for each name, I
will have an address to tie it to. I permit no limit on names that can
be tied to a single address.

So what?
You seem to think this is some kind of big deal, but I see nothing other
than routine database stuff here.

My 'problem':
I retrieve an address and want to find out all the names that are tied
to it... I know I could just do:

SELECT contacts.firstname,contacts.lastname,address.line_ 1
FROM contacts,address
WHERE contacts.address_hash='$myhashkey'
AND address.hash='$myhashkey';

Okay!

However since my select does not know how many contact names are tied to
the address, it will search through the entire contacts table, true?

True. But why is this at all bothersome?
Are you not indexing?
MySQL was designed to eat problems like this for lunch.

Why/how would SELECT know or care how many records there are ahead of it.
It doesn't! The whole point of SELECT is to find out for heavens sakes!

If I was to suffix the above query with a LIMIT condition, this would
help, true?

Help what? What can this possibly do for you?
If you have a reason *you* can't accept an indeterminate flood of records,
then by all means LIMIT. I assure you, MySQL won't care one way or the
other.

But your question "would this help?" is indecipherable.
It won't help you get a date. It won't help you make money. It won't improve
your health

AND it won't help MySQL do it's job!

The problem is, I don't know how many names *might* be
reitrieved from the contacts table hence why it would be useful if I had
a value for addres.num_of_contacts, ...

If *you* need to know "how many names *might* be reitrieved from the
contacts table" then it is incumbent on *you* to ask.

SELECT COUNT(contacts.*) as NumberOfNames
FROM contacts,address
WHERE contacts.address_hash='$myhashkey'
AND address.hash='$myhashkey';

MySQL won't mind if you ask - honest!

as I could use its value as part of my LIMIT criteria.
True/False?

In order to achieve *what* ?
If you were expecting it to somehow make your query more efficient, the
answer is most certainly False.

Have you an alternative solution?

Heck - I still don't even have the problem.

Thanks for the (constructive) criticism - I'm doing MySQL for about a
year plus now though not in a working environment so I'm open to all
comments that help improve my skillset.

Hey - I'm trying!
Thomas Bartkus

I'll leave it - I cannot explain it any more clearer than I have done
already - I know MySQL is geared towards handling the data, but as
another reply to my post found, I was merely trying to assist MySQL in
performing the query with greater efficiency. I do have indexes, and I
didn't want MySQL to have to investigate every hash in order to find
every name that tie's to my address. The way I see it, if I had one my
contacts table with 20,000 records, and if I had a record that had one
name, it would still examine everyone of the hashes for my contacts
table *in case* there were more names. I had hoped that by being able
to have a LIMIT in my select, then once the record is found in the
contacts hash table, it would return straight away as opposed to
continue searching. But because any number of names can be tied to an
address, the LIMIT value will vary... In some cases, the address table
may not have a name tied to it therefore without using LIMIT, I'd end up
searching the contacts table and waste resources.

I hope you understand the above... if not... then... well...

lets just leave it...

Thanks for taking the time/effort though,

Randell D.
Jul 23 '05 #10

P: n/a
Randell D. wrote:
if I had one my
contacts table with 20,000 records, and if I had a record that had one
name, it would still examine everyone of the hashes for my contacts
table *in case* there were more names.
That's why you would use indexes on the address fields. An index is
sorted, so MySQL can look up values very quickly. So don't feel too bad
about making MySQL work! :-) It's really not a problem here.
I had hoped that by being able
to have a LIMIT in my select, then once the record is found in the
contacts hash table, it would return straight away as opposed to
continue searching.


Yes, this is an optimization that LIMIT can perform (see
http://dev.mysql.com/doc/mysql/en/li...mization.html), but as you
have already realized, you must LIMIT by a fixed number, which makes it
hard to use in this case.

Regards,
Bill K.
Jul 23 '05 #11

P: n/a
Bill Karwin wrote:
Randell D. wrote:
if I had one my contacts table with 20,000 records, and if I had a
record that had one name, it would still examine everyone of the
hashes for my contacts table *in case* there were more names.

That's why you would use indexes on the address fields. An index is
sorted, so MySQL can look up values very quickly. So don't feel too bad
about making MySQL work! :-) It's really not a problem here.
I had hoped that by being able to have a LIMIT in my select, then once
the record is found in the contacts hash table, it would return
straight away as opposed to continue searching.

Yes, this is an optimization that LIMIT can perform (see
http://dev.mysql.com/doc/mysql/en/li...mization.html), but as you
have already realized, you must LIMIT by a fixed number, which makes it
hard to use in this case.

Regards,
Bill K.

Thanks again...

randelld
Jul 23 '05 #12

This discussion thread is closed

Replies have been disabled for this discussion.