Question update from sql table where maximum date

[Gideon]

I am building a VB.NET Windows Application form, and as a newbie cannot figure out an issue.
I have two sql tables, which are not joined. I wish to update one (skedulering) from another (ontledings) where I have certain "WHERE" clauses as in the following code :

      opdraginvoer.Connection = konneksie
            opdraginvoer.CommandText = "update dbo.Skedulering " & _
                "SET skedulering.skedph = ontledings.ph,      
                skedulering.skedsuiker    = ontledings.suiker," & _
                 "skedulering.skedsuur = 
              ontledings.suur,skedulering.skedanalisedatum = 
               ontledings.analisedatum " & _
                  "FROM Ontledings " & _
                    "WHERE Ontledings.plaasno=skedulering.skedplaasno " & _
                    "AND Ontledings.blokno=skedulering.skedblokno " & _
                    "AND YEAR(Ontledings.analisedatum) = year(getdate())"
            opdraginvoer.ExecuteNonQuery()

After the last "WHERE" condition ( i.e. YEAR(Ontledings.analisedatum) = year(getdate()), I wish to include another condition which specifies the maximum date in the column (ontledings.analisedatum).
I have tried : Ontledings.analisadatum = MAX(Ontledings.analisedatum). Alas it returns the following error :
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference
Any ideas ?

Regards

 

[JuggaloBrotha]

Before doing anything to add to the complexity of the data retrieved for the update query, I would make a select query to make sure the data retrieved is correct.
So I would make a select query from this:

UPDATE dbo.Skedulering
SET skedulering.skedph = ontledings.ph,
    skedulering.skedsuiker = ontledings.suiker,
	skedulering.skedsuur = ontledings.suur,
	skedulering.skedanalisedatum = ontledings.analisedatum
FROM Ontledings
WHERE Ontledings.plaasno=skedulering.skedplaasno
  AND Ontledings.blokno=skedulering.skedblokno
  AND YEAR(Ontledings.analisedatum) = YEAR(GetDate());

Then you'd need to add the Max(YourDateFieldHere) to the query and because you're using an aggregate function you'll need to add the other fields to a Group By clause too.

One last note, it looks like you're using Sql Server & if so I highly recommend making this a Stored Procedure then have your app just call the procedure. It'll make updating the query far easier in the future and will help keep the clutter of queries out of your application itself.

 

[cjard]

Using MAX(Ontledings.analisedatum) indicates you're trying to update a single row, the most recent one - correct? You want to find the most recent row in Ontledings (as according to whichever row has the highest analisedatum) and update another table with info from that latest row

In SQL server you must write a join query after the FROM. and you must tell UPDATE which table you're updating:

UPDATE ThisTable
SET somecolumn = OT.othercolumn
FROM
  Thistable TT 
  INNER JOIN
  OtherTable OT
  ON
    TT.ID = OT.ID

It helps to alias tables so you write the query correctly: give table names after the FROM bit an alias (TT and OT in my query) and do not give the table you are updating an alias. This is because you can only update one table, so while you may use that table several times in the join, each with different aliases, you are only ever updating one table obejct in the DB - so SQLserver makes you specify which table youre updating to make it clear and outline this point: you arent updating one of your aliased tables (which is a query result, a subset of table rows) - youre updating a database table object, picking rows out of it according to some join query you constructed and copying rows out of the joined up mash, into the genuine original table. k?

Hence:

UPDATE Skedulering
SET skedulering.skedph = o.ph,
    skedulering.skedsuiker = o.suiker,
	skedulering.skedsuur = o.suur,
	skedulering.skedanalisedatum = o.analisedatum
FROM 
(SELECT TOP 1 * Ontledings o ORDER BY analisedatum DESC) o
INNER JOIN
Skedulering s
ON
  o.plaasno=s.skedplaasno AND 
  o.blokno=s.skedblokno AND 
  YEAR(o.analisedatum) = YEAR(GetDate())

Note, I didn't use a MAX() function in the query because this one should be easier to understand - if you order by analisedatum descending, then the top1 row will be the most recent row. this as a subset then gets aliased as o and joined to skedulering s. This query will only act on any records if the most recent row in o is this year, the plasno and blokno from o are used as keys to s, and the relevant columns in skedulering are updated accordingly

If your intention was that the most recent record per variation of plaasno and blokno (let's assume there might be 30 records with the same plaasno and blokno, all inserted at different times, and you want to copy only some info from the most recent one, into skedulering, then you need:

UPDATE Skedulering
SET skedulering.skedph = o.ph,
    skedulering.skedsuiker = o.suiker,
	skedulering.skedsuur = o.suur,
	skedulering.skedanalisedatum = o.analisedatum
FROM 
(SELECT plaasno, blokno, ph, suiker, suur, analisedatum, COUNT(*) OVER(PARTITION BY plaasno, blokno, ORDER BY analisedatum DESC) as rowN FROM Ontledings) o
INNER JOIN
Skedulering s
ON
  o.plaasno=s.skedplaasno AND 
  o.blokno=s.skedblokno AND 
  YEAR(o.analisedatum) = YEAR(GetDate()) AND
  o.rowN = 1

The subquery

SELECT plaasno, blokno, ph, suiker, suur, analisedatum, ROW_NUMBER() OVER(PARTITION BY plaasno, blokno, ORDER BY analisedatum DESC as rowN FROM Ontledings

tells sqlserver to give ALL records from o, and then also partition them up (group them) into unique combinations of plaasno+blokno, order the partitioned rows by analisedatum descending and give them a row number. The results are like:

plaasno, blokno, ... analisedatum, rown
1, 1, 30-12-2014, 1
1, 1, 28-11-2014, 2
1, 1, 30-10-2014, 3
1, 2, 15-11-2014, 1 <-- new plaasno/blokno combination, row numbering restarts

etc

In the ON join criteria we have rown=1 so "only the latest of any particular plaasno/blokno", then the details are copied to sked...


Note, i recommend not to do this, really. Databases are designed so that you want a latest Ontledings row for any particular Sked row, then you do this:

SELECT 
  (columns) 
FROM 
  Skedulrings s
  INNER JOIN
  (SELECT (columns...), ROW_NUMBER() OVER(PARTITION BY plaasno,blokno ORDER BY analisedatum DESC) rown FROM Ontledings) o
  ON 
    o.ID = s.ID and 
    o.rown = 1
WHERE
(blah)

now you will get S row with latest row in S and use the values - no need to store latest values in O, into S as that will just create maintenance headaches...

 

[Gideon]

Thank you cjard for the explanation.

However I get the following Error : Invalid column name 'ID'

Here is my code :
SELECT skedph ,
skedsuiker ,
skedsuur ,
skedanalisedatum

FROM
Skedulering s
INNER JOIN
(SELECT plaasno, blokno, ph, suiker, suur, analisedatum, ROW_NUMBER() OVER(PARTITION BY plaasno,blokno ORDER BY analisedatum DESC) rown FROM Ontledings) o
ON
o.ID = s.Sked_ID and
o.rown = 1

WHERE o.plaasno=s.skedplaasno
AND o.blokno=s.skedblokno
AND YEAR(o.analisedatum) = year(getdate())


The error occurs at the line : o.ID = s.Sked_ID and (at o.ID)

Regards