Update table A from TOP in table B

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

SQL Server 2000 Forums

SQL Server Development (2000)

Update table A from TOP in table B

Author

Topic

pkipe
Starting Member

6 Posts

Posted - 2012-04-05 : 11:52:26

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

52326 Posts

Posted - 2012-04-05 : 12:12:24

[code]
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
[/code]

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

pkipe
Starting Member

6 Posts

Posted - 2012-04-05 : 12:48:46

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-05 : 13:21:33

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/

pkipe
Starting Member

6 Posts

Posted - 2012-04-05 : 17:35:56

Sorry - just posted in the Access forum. It doesn't look very active, though...wish me luck!

Thanks

pkipe
Starting Member

6 Posts

Posted - 2012-04-06 : 03:43:49

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.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-04-06 : 06:50:36

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.

pkipe
Starting Member

6 Posts

Posted - 2012-04-06 : 12:04:10

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?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources