SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Update table A from TOP in table B
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pkipe
Starting Member

USA
6 Posts

Posted - 04/05/2012 :  11:52:26  Show Profile  Reply with Quote
Given table A as follows:

pk_a expiration_date
1 1/15/2012
2 1/15/2012
3 2/12/2012

and table B as follows:

pk_b fk_a date_expires
1 1 1/15/2012
2 1 1/15/2013
3 2 1/15/2012
4 2 1/15/2013
5 3 2/12/2011
6 3 2/12/2012
7 3 2/12/2013

How can I write an update query in SQL that will update A.expiration_date with B.date_expires from the highest B.pk_b for the join between A.pk_a and B.fk_a?

Thanks in advance,

Pete

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 04/05/2012 :  12:12:24  Show Profile  Reply with Quote

UPDATE a
SET a.expiration_date= t.date_expires
FROM tableA a
INNER JOIN (SELECT b.fk_a,b.date_expires
            FROM tableB b
            INNER JOIN (SELECT fk_a,MAX(pk_b) AS MaxB
                        FROM tableB
                        GROUP BY fk_a)b1
            ON b1.fk_a = b.fk_a
            AND b1.MaxB = b.pk_b
           )t
ON t.fk_a = a.pk_a


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

Go to Top of Page

pkipe
Starting Member

USA
6 Posts

Posted - 04/05/2012 :  12:48:46  Show Profile  Reply with Quote
Whew! Now I know why I wasn't able to figure this out on my own.

The actual table names are Station (table A) and License (table B). When I modified your code for my actual names, this is what the query looked like:

UPDATE Station
SET a.expiration_date = t.date_expires
FROM Station AS a
INNER JOIN (SELECT b.fk_station, b.date_expires
FROM License AS b
INNER JOIN (SELECT fk_station, MAX(pk_license) AS MaxB
FROM License
GROUP BY fk_station) AS b1
ON b1.fk_station = b.fk_station
AND b1.MaxB = b.pk_license) AS t
ON t.fk_station = a.pk_station

(Sorry for the formatting - this forum doesn't support tabbing or indenting, it seems.)

When I ran the query, I got the following error:

Syntax error (missing operator) in query expression 't.date_expires
FROM Station AS a
INNER JOIN (SELECT b.fk_station, b.date_expires
FROM License AS b
INNER JOIN (SELECT fk_station, MAX(pk_license) AS MaxB
'.

I'm running in Access 2003.

Any ideas?

Thanks! Pete
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 04/05/2012 :  13:21:33  Show Profile  Reply with Quote
sorry if you're looking for Access query post it in Access forum. What I've given you is a t-sql query which may not work in access

also you can format code by putting inside code tags

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

Go to Top of Page

pkipe
Starting Member

USA
6 Posts

Posted - 04/05/2012 :  17:35:56  Show Profile  Reply with Quote
Sorry - just posted in the Access forum. It doesn't look very active, though...wish me luck!

Thanks
Go to Top of Page

pkipe
Starting Member

USA
6 Posts

Posted - 04/06/2012 :  03:43:49  Show Profile  Reply with Quote
I posted my question in the Access forum, and the topic was locked by robvolk as a duplicate. I'm going to try another forum. Thanks anyway.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15655 Posts

Posted - 04/06/2012 :  06:50:36  Show Profile  Visit robvolk's Homepage  Reply with Quote
Sorry about locking it, we try to limit duplicate posts even if they're in the "wrong" forum.

If you create an Access query (i.e. MaxExpires) with this statement:

SELECT fk_station, MAX(pk_license) AS MaxB
FROM License
GROUP BY fk_station

You can then create an Access Update query that joins the Station table to MaxExpires and update the column.
Go to Top of Page

pkipe
Starting Member

USA
6 Posts

Posted - 04/06/2012 :  12:04:10  Show Profile  Reply with Quote
robvolk, I created the "MaxExpires" query you suggested exactly as you coded it. I then created the following query:

UPDATE MaxExpires
INNER JOIN Station
ON MaxExpires.fk_station = Station.pk_station
SET Station.expiration_date = [MaxExpires].[MaxB];

Running it results in the error message, "Operation must use an updateable query."

It seems I need to use the Dmax function to get around the problem of not being able to use Max in an update query in Access. The following works, but it selects the highest date value in the one-to-many join, not the date value for the highest License pk in the join:

UPDATE Station
SET Station.expiration_date = Dmax("date_expires", "License", "fk_station=" & pk_station)

Does anyone know how to resolve this?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000