Need urgent help in Update query

Nishapd · June 19th, 2009, 02:36 PM

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.

Old Pedant · June 19th, 2009, 06:44 PM

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.

Nishapd · June 19th, 2009, 08:33 PM

Thanks you so much for your response.

I am using 'Oracle' Database as back end.

Nishapd · June 19th, 2009, 09:07 PM

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 Pedant · June 20th, 2009, 02:01 AM

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.

Nishapd · June 23rd, 2009, 03:55 PM

Thanks for your reply. I solved the issue.