Wrox Programmer Forums
|
Classic ASP Basics For beginner programmers starting with "classic" ASP 3, pre-".NET." NOT for ASP.NET 1.0, 1.1, or 2.0
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 19th, 2009, 02:36 PM
Authorized User
 
Join Date: Apr 2009
Posts: 52
Thanks: 0
Thanked 0 Times in 0 Posts
Default Need urgent help in Update query

TABLE1

NAME
RATE

TABLE2

NAME RATE1 DATE1
NAME RATE2 DATE2
NAME RATE3 DATE3

Can anyone help me to write a query which can update RATE in TABLE1 based on
following criteria ?

We have same column 'NAME' in both the tables.

First Find maximum Date from the TABLE2 and then take correspoinding rate from TABLE2 and update with the RATE column in TABLE1.

Last edited by Nishapd; June 19th, 2009 at 03:59 PM..
 
Old June 19th, 2009, 06:44 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

You don't say what DB you are using. Yes, it makes a lot of difference when making UPDATE queries. The syntax used depends a *LOT* on the DB you use.

For SQL Server, something like this:
Code:
UPDATE table1 
SET rate = T2.rate
FROM table1 AS T1, 
     table2 AS T2, 
     ( SELECT name, MAX(date) AS mdate FROM table2 GROUP BY name ) AS T3
WHERE T2.name = T3.name
  AND T2.date = T3.mdate
  AND T1.name = T2.name
*NOT* tested! Feels right, but no point in going further until you tell us what DB you are using.
 
Old June 19th, 2009, 08:33 PM
Authorized User
 
Join Date: Apr 2009
Posts: 52
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks you so much for your response.

I am using 'Oracle' Database as back end.
 
Old June 19th, 2009, 09:07 PM
Authorized User
 
Join Date: Apr 2009
Posts: 52
Thanks: 0
Thanked 0 Times in 0 Posts
Default

It is giving me an error "SQL command not properly ended" at line 2 of the query.

That means where we have SET clause.
 
Old June 20th, 2009, 02:01 AM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Sorry, can't help you with Oracle. You'll have to find the docs for multi-table update for Oracle and see what their syntax is.

If you'd been using MySQL or Access (or SQL Server) I could help. But ALL THREE of those DBs use *VERY DIFFERENT* SQL syntax for multi-table updates, so not surprising that Oracle isn't the same as SQL Server.

I would suggest you find an Oracle-specific forum and ask the question there. It really doesn't have anything, directly, to do with ASP, you know.
 
Old June 23rd, 2009, 03:55 PM
Authorized User
 
Join Date: Apr 2009
Posts: 52
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for your reply. I solved the issue.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Trigger update problem (urgent) msbsam SQL Server 2000 6 February 26th, 2007 02:39 AM
urgent query,,, please.....!!! SAAM C++ Programming 1 December 11th, 2006 08:22 AM
How does Update Link in FormView work? URGENT shaly ASP.NET 2.0 Professional 0 December 7th, 2006 04:10 PM
Insert, update, delete in gridview-!!! URGENT !!! dagad ASP.NET 2.0 Basics 0 September 27th, 2006 06:24 AM
I solved insert query.now see this Update Query. amit_mande@yahoo.com VB.NET 2002/2003 Basics 2 September 21st, 2006 12:48 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.