Read one column, update another...

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

Read one column, update another...

Author

Topic

oap
Yak Posting Veteran

60 Posts

Posted - 2011-12-28 : 01:52:25

Yes yes, I know this has been covered before but I couldn't find a post that covered my particular needs.

I have a routine that I run manually to update a table of city names and tell you the number of photo galleries and total photos in all galleries for each city.

In essence I loop through all of the photos, perform a SQL command to tabulate via the SUM command the number of galleries and photos for each GDIR, and then perform a secondary UPDATE SQL command to update another table containing the city names (SITES).

The process takes about 20-30 seconds for approximately 230 cities but I feel it could certainly be more efficient.

The cities are held in one table (SITES) while the photos are held in GALLERYINDEX.

' objconn will access GALLERYINDEX which holds approx. 35,000 photo filenames and dirctory locations

Set objconn = Server.CreateObject("ADODB.Connection")
objconn.Open "Provider=SQLOLEDB; Data Source =xxx.xx.24.53; Initial Catalog = sqldb; User Id = USER; Password=PASS; trusted_connection=false"

'objconn2 will be used for the update command to update a secondary table named SITES

Set objconn2 = Server.CreateObject("ADODB.Connection")
objconn2.Open "Provider=SQLOLEDB; Data Source =xxx.xx.24.53,14333; Initial Catalog = sqldb; User Id = USER; Password=PASS"

strSQL = "SELECT gdir, SUM (gnumpics) as nopics, COUNT(*) AS tpics from galleryindex GROUP BY gdir ORDER BY gdir"
Set objrs = Server.CreateObject("ADODB.Recordset")
objrs.open strSQL, objconn

' gdir - a number representing each unique city
' tpics - total number of galleries as calculated by the SUM of the number of recordsets with the same gdir
' nopics - number of photos for all galleries with same GDIR

' loop through galleries
while NOT objrs.EOF
tgal = 0 ' total galleries
tpics = 0 ' total pics
curloc = objrs("gdir") ' get number value for city

' count pics and galleries for each location
tgal = objrs("tpics")
tpics = tpics + objrs("nopics")
objrs.movenext

strSQL2 = "UPDATE sites SET pics='" & tpics & "', galcount='" & tgal & "' WHERE ID = " & curloc
set objRS2 = objConn2.Execute(strSQL2)

wend

objconn2.close

So lets say that there are 4 records with a GDIR of "123" (gdir is a numerical value for each unique city)

GSID GDIR GNUMPICS
-------------------
1we2 123 20
1wke 123 10
1we2 123 5
1jd3 123 10

nopics will = 45 (total number photos by adding GNUMPICS together)
tgal = 4 (4 total galleries as 4 recordsets exist)

and thus result with :
UPDATE sites SET pics='45', galcount='4' WHERE ID = 123

This works fine, but I don't like doing two seperate database calls. I think it's inefficient and there must be an easier way.

Second question if I can do two-for-one. I also count total pages displayed on the website. This is an incremental value that goes up by 1 any time a page is shown to a user.

Example:
"Since December 2010 a total of 486413 pages have been generated"

The code for this is below:

SET show=SERVER.CREATEOBJECT("ADODB.RECORDSET")
show.OPEN "SELECT TPV from STATS", DBCONN, 3, 3
show("tpv") = show("tpv") + 1
show.update
tpv = show("tpv")
show.close

This is a table named STATS with just one field, "TPV" (total page views). It is a numeric value that as you can tell, is incremented.

I am receiving a "consistency" error when two people are logged in at the same time and it tries to update at the same time as another person does. I'm not too familiar with the .OPEN method for databases but I think it's something to do with the ",3,3" which specifies read only, write only, etc.

Thank you for any help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-28 : 03:27:12

you just need this i guess


Set objconn = Server.CreateObject("ADODB.Connection")
objconn.Open "Provider=SQLOLEDB; Data Source =xxx.xx.24.53; Initial Catalog = sqldb; User Id = USER; Password=PASS; trusted_connection=false"

strSQL = "UPDATE s SET s.pics=s1.nopics,s.galcount = s1.tpics  FROM sites s INNER JOIN (SELECT gdir, SUM (gnumpics) as nopics, COUNT(*) AS tpics from galleryindex GROUP BY gdir)s1 ON s1.gdir = s.ID"

set objRS2 = objConn2.Execute(strSQL)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

oap
Yak Posting Veteran

60 Posts

Posted - 2012-01-01 : 08:47:56

Thank you for the reply, it worked great!

Could you possibly explain what this does? I'm unfamiliar with the INNER JOIN and what the 's' is for.

(I'll try to Google it as well, so as not to be lazy).

whatamouth
Starting Member

16 Posts

Posted - 2012-01-02 : 05:02:08

he used s for aliasing the table 'sites'

Neil Matias
neilmatias@yahoo.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-02 : 12:05:42

quote:
Originally posted by oap

Thank you for the reply, it worked great!

Could you possibly explain what this does? I'm unfamiliar with the INNER JOIN and what the 's' is for.

(I'll try to Google it as well, so as not to be lazy).

INNER JOIN joins (merges) data from both the tables on the matching column values as specified in ON condition
s is an alias ie short name given for the table so that i dont have to repeat it everywhere

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources