(Solved) Simple addition question

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

(Solved) Simple addition question

Author

Topic

sqlconfused
Yak Posting Veteran

50 Posts

Posted - 2013-10-09 : 11:42:17

Hi.

Another question with what I can presume is an easy answer...

I have a table named galleryindex which contains rows of photo gallery names along with an associated id (gsid).

example:
gsid (assigned ID), gnumpics (# of photos), gname (gallery name)
=================
43, 133, our summer visit
493, 53, camping photos

When someone uploads more photos I do the following to recalculate the NEW number of photos for the gallery.

SELECT gnumpics FROM galleryindex where gsid= 43
(select current # of photos from location 43 - our summer visit)

in ASP classic I then do this:
sb = objrs("gnumpics")
sb = sb + totupl
SQL = "UPDATE galleryindex SET gnumpics =" & sb & " where gsid= '" & fnum & "'"

to update the gallery with the new amount of photos which is obtained by pulling the old value into SB, and adding "totupl" (total pics uploaded) to it and then updating the values.

In SQL speak it would be like this if 20 photos were just uploaded:

SELECT gnumpics FROM galleryindex where gsid= 43
(results in 133 photos)

UPDATE galleryindex SET gnumpics =153 where gsid= 43
(write back 133 + 20)

Basically I'm trying to update the current column gnumpics by a numeric value and I know there's an easier way then pulling the current, adding to it and then writing it back to SQL.

Thank you in advance

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-10-09 : 11:57:06

Probably the easiest way is to NOT store the total, but just calculate it on the fly. But, if you want to store it, can you simply query for the total number of images and update in one statement? Assuming you have a table with a row for each image:

UPDATE 
	galleryindex 
SET 
	gnumpics = 
		(
			SELECT COUNT(pics)
			FROM <pic_table> 
			WHERE gsid= 43
		)	
WHERE gsid= 43

sqlconfused
Yak Posting Veteran

50 Posts

Posted - 2013-10-09 : 14:10:30

Like a charm, it worked... thank you so much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources