Simple Max function(beginner)

[gphillips]

Hi,
Simply Need to update a table where revision field is the maxiumum in BFCD_Designs tabke. Is this correct for where statement.
What is correct syntax?

Where MAX BFCD_Designs.Revision

[Ecniv]

Not quite no.

Try something like:

UPDATE <table>
SET <field> = <value>
WHERE bfcd_designid in (
    SELECT bdcd_designid, Max(BFCD_Designs.Revision)
    FROM <table>
    GROUP BY bdcd_designid
)

Except I think this wont run as it returns two fields and you only need those that are the latest revision per designid (I guess).
If you only want the latest revision to be updated you would need to pull the max revision, and match that to the table ... but if there are two designs with the same revision, it would update both.


Can you post your table structure and I will recreate in access to check the best route to do this.

Or if you are updating as an sql statement you could open a recordset to retrieve all those that need to be updated first, put the ids into a string then append the string into the where clause above instead of the subquery select statment. It means an extra step but you can see which are going to be updated form the first recordset results.

[Ecniv]

Er. From your previous thread I made up a couple of tables. Not sure if this is how or right but the Sql I got is:

UPDATE BFCD_Designs
SET BFCD_Designs.EndDt = #5/30/2007#
WHERE 
BFCD_Designs.designid in
(
SELECT BFCD_Designs.DesignID
FROM BFCD_Designs INNER JOIN (SELECT BFCD_Designs.DesignID, Max(BFCD_Designs.Revision) AS MaxOfRevision
FROM BFCD_Designs
GROUP BY BFCD_Designs.DesignID
) AS sq ON BFCD_Designs.DesignID = sq.DesignID
GROUP BY BFCD_Designs.DesignID)

Now, this will update all the rows everytime, as the revision is applied to each and there is only one per design. If the revisions are in another table it may work, or if you use a revision date to return only one then the above Sql may be more useful.

Depends on what you want updated.

Edit:
BFCD_Designs
DesignID - PK - Autonumber
DesignName - text
EndDt - date
Revision - text

User_design_import
UserDesID - pk - autonumber
DesignID - number

[gphillips]

update BFCD_Designs set EndDt='20070530'
from BFCD_Designs inner join USER_D on BFCD_Designs.DesignId= USER_D.Design

Cheers m8, just for clarity I just need to add the max code to the above

Where BFCD_Designs table Revisions field has the highest number, update the EndDt
This is from the previous post.


What would be the ammendment to the above?? - sorry wasn't expecting it to be more than one line.

[Ecniv]

UPDATE BFCD_Designs
SET BFCD_Designs.EndDt = #5/30/2007#
WHERE 
BFCD_Designs.designid in
(
SELECT BFCD_Designs.DesignID
FROM BFCD_Designs INNER JOIN 
    (SELECT  Max(BFCD_Designs.Revision) AS MRevision
     FROM BFCD_Designs
) AS sq ON BFCD_Designs.revision = sq.mrevision 
)

Try the above. Please note it matches what it thinks is the highest revision. If more than one record has the same highest revision they both will be updated.

[gphillips]

I can't see the USER_D table mentioned soz.

[Ecniv]

update BFCD_Designs 
set EndDt='20070530'
WHERE designID in (
SELECT bfcd.designid
FROM BFCD_Designs
inner join USER_D on BFCD_Designs.DesignId= USER_D.Design
)

Uh, what do you want the max of though?

[gphillips]

I have attached the TABLES as requested just for clarity.

The max revision is in the BFCD_Designs TABLE which is linked to the USER_D simply to get the values to be updated.


There will be a lot more values in BFCD_DESIGNS of which will not get updated. These are not shown, this why we are doing a join to USER_D table.


Hope this is clear.

I amdoing this in SQLSERVER2000, so code will be slightly different.

[Ecniv]

UPDATE BFCD_Designs
SET BFCD_Designs.EndDt = #5/30/2007#

WHERE 
BFCD_Designs.EndDt is Null
AND BFCD_Designs.designid in
(
SELECT BFCD_Designs.DesignID
FROM BFCD_Designs INNER JOIN 
    (SELECT BFCD_Designs.DesignID,
     Max(BFCD_Designs.Revision) AS MRevision
     FROM BFCD_Designs
     GROUP BY BFCD_Designs.DesignID
    ) AS sq 
  ON BFCD_Designs.revision = sq.mrevision 
  AND BFCD_Designs.designid = sq.designid
)

The bit in bold forces only those not already end dated to have a date.
I am joining all designs to the max revision per design id from all designs and returning only the designID.
This then goes into the where clause filter to tell the db which records to update.

Since your primary key is not one field, the design id is repeated more than once, you need to use two fields to find the row. However as it is a subquery (joining on the update means you cannot update (at least in Access)) it returns only a list of DesignIds, so I added the filter in bold so it will only update those not already end dated.

Personal preference would be :
Designs table - designs information
Revisions table - Design id and revision <- might hold the start / end for revision - Or at least a start date?
UserImp table - those that need checking for updates

Either method, as long as it does the job and works is good. It also depnds on how you are getting the users to interact with it.

[gphillips]

I can't see the code for the inner join to the USER_D Table. Am I missing the point?


The USER_D table is used to link to the BFCD_Designs table to know what to update.
Even though they exactly match - in reality there will be more values in the BFCD_DESIGNS table.

It needs to work around this code. Date needs to be in this format - otherwise errors.

update BFCD_Designs set EndDt='20070530'
from BFCD_Designs inner join USER_D on BFCD_Designs.DesignId= USER_D.Design

There were two sheet on the original attachment. Did you notice?


whats SQ mean in code?
what as mrevison mean in code?

Never heared of that

Thanks.

[szlamany]

I'm confused about your data.

It's always helpful for us to see a small sample of some rows and which ones you want to have updated.

But at any rate - I would use a subquery in the where clause - something like

(and this only works in MS SQL SERVER...)

Update SomeTable Set SomeField='xyz'
     From SomeTable S1
     Where S1.Revision=(Select Max(S2.Revision) From SomeTable S2
                                                 Where S2.PriKey=S1.PriKey)

Basic concept is that the sub-query returns a revision number for some primary key (if you need all the "max" rows updated) that is the max value. And we only update the S1 "alias" when the revision in the update matches the revision in the sub-query.

[gphillips]

Ok here the history:

All sql server 2000

History
This post is an extension to update query simple (begginner).

I simply want to update the BFCD_Designs table 'Enddt field with the date 30/05/2007. I only want to update where there is a match between User_D and BFCD_Designs on one field. Just to be clear the join is on DesignID field in BFCD_Designs table and Design field in User_D table. The User_D table only has one field where as the table to update has many fields.


This code is covered here.

update BFCD_Designs set EndDt='20070530'
from BFCD_Designs inner join USER_D on BFCD_Designs.DesignId= USER_D.Design

What I need to somehow do now?

But I realised I then needed to include a where clause to the above for revison citeria in BFCD_DESIGNS TABLE.


Where field Revison in BFCD_DESIGNS TABLE is the highest value.


Your code suggest the solution is less lines of code than ENCIV.


What are the ammendment considering what I want to do?


Thanks,

[Ecniv]

UPDATE BFCD_Designs
SET BFCD_Designs.EndDt = #5/30/2007#

WHERE 
BFCD_Designs.EndDt is Null
AND BFCD_Designs.designid in
(
SELECT BFCD_Designs.DesignID
FROM BFCD_Designs INNER JOIN 
    (SELECT BFCD_Designs.DesignID,
     Max(BFCD_Designs.Revision) AS MRevision
     FROM BFCD_Designs
     GROUP BY BFCD_Designs.DesignID
    ) AS sq 
  ON BFCD_Designs.revision = sq.mrevision 
  AND BFCD_Designs.designid = sq.designid
)

I'll break it down :

UPDATE BFCD_Designs
SET EndDt = #5/30/2007#

This bit you know - set the end date to what ever date (use applicable to your db so sql server as you put it would be ' enddt = '20070530'

WHERE 
BFCD_Designs.EndDt is Null
AND BFCD_Designs.designid in

Here I am filtering off those which already have an end date (otherwise you would overwrite it).
Then I am filtering to those that have a particular designid. The design ids are selected in the subquery.

(
SELECT BFCD_Designs.DesignID
FROM BFCD_Designs INNER JOIN 
    (SELECT BFCD_Designs.DesignID,
     Max(BFCD_Designs.Revision) AS MRevision
     FROM BFCD_Designs
     GROUP BY BFCD_Designs.DesignID
    ) AS sq 
  ON BFCD_Designs.revision = sq.mrevision 
  AND BFCD_Designs.designid = sq.designid
)

This is the sub query.
I want it to return the designid so I select that.
I am joining the main design table to an aliased copy of itself, but I am only bringing back the design id and the Maximum revision number. I have aliased the max revision number to MRevision (ie renamed it).
The 'As Sq' is the sub-sub query that gets the max number and the design id and is an alias. To save typing etc


If I brought back just the design id, you would update all those with that design id unless otherwise filtered. So I joined the main table to the max derived table by the design id and the revision number.


All the above is only one method (that hopefully does what you want).

Szlamany is way better Sql server and sql code than me and probably realised a better faster way. I suggest you used both methods and go with which ever a) works b) gives you the results you want and c) you understand.

[gphillips]

Thanks both I like your explanatons - its useful - thansk for explaining much apprecaited .I must emphasis I need to join to the USER_D
table to find the values I need to update in BFCD_Designs table. Its simply a inner join to the BFCD_Designs.
However linking the BFCD_DESIGNS table to the USER_D table only provide the first set of filtered values I need to update.
The filtered designs I need to update needs to be filtered further so I get the max revision in the BFCD_DESIGNS TABLE.
Only after this second filter does the update take place.

SO IF YOU LIKE THERE ARE TWO FILTERS ONE AFTER ANOTHER:

Firstly linking the BFCD_DESIGNS TABLE TO THE USER_D table - crucial
Then secondly using the filtered values above and somehow writing A WHERE Code ON BFCD_DESIGNS TABLE for the maxiumum revision.
The revision field is only contained within the BFCD_DESIGNS TABLE.

Does this make sense? My understanding of the above is that it only works if you are using one table and every nulll or max revsion needs to be updated.
There will be a lot more values in the BFCD_DESIGNS in reality and only certain max values need to get updated after the crucial link between the USER_D table and BFCD_DESIGNS TABLE.

As a result I am still confused by where clause subquery. I don't think it fits with what I want to do.
I just need to add a second filter(after the crucial first) where BFCD_Designs.Revision is the maximum.

Taking account of thabove.What needs to change to make it work in sql server 2000???

update BFCD_Designs set EndDt='20070530'
from BFCD_Designs inner join USER_D on BFCD_Designs.DesignId= USER_D.Design
Where S1.Revision=(Select Max(S2.Revision) From SomeTable S2
                                                 Where S2.PriKey=S1.PriKey)

Cheers,

[szlamany]

I'm confused about your data.

It's always helpful for us to see a small sample of some rows and which ones you want to have updated.

You gone to great lengths to describe in words what you want to do - but I would need to spend 20 minutes reading all that to grasp what it is...

Show me a dozen rows of sample data from both of these tables - with just the columns that are pertinent. Keep is simple.

Make sure to indicate which rows should be updated in this "example".