Update using MIN from second table

[jeisch]

I have been trying to update the value of a column in table A, using the Min value of a column from table B, and have been unable to figure it out.

This is the query I need to use to update the value in Table A

Select COL1, COL2, MIN(UPD_DT)
From Table B
Group By COL1, COL2;

The B.MIN(UPD_DT) needs to used to update A.UPD_DT
where A.COL1 = B.COL1 and A.COL2 = B.COL2.

I've tried many different variations for this query, but just can't find any way that actually works.

Any suggestions would be greatly appreciated.

 

[BrianTyler]

Try something like:

update tablea a
set a.upd_dt =
(select min(b.upd_dt)
from tableb
where a.col1 = b.col1 and
a.col2 = b.col2)

I'm pushed for time now so I cannot try it for myself

 

[jeisch]

I tried to do that and it tells me I'm trying to update with a NULL value, I don't think it will work because it is not grouping the minumum date based on the values of col1 and col2.

I tried the following and got an error message:

UPDATE TableA A
SET upd_dt =
(SELECT B2.crt_dt
FROM
(SELECT b.col1, b.col2,
MIN(AC.Upd_dt) AS crt_dt
FROM TableB B
GROUP BY b.col1, B.col2) B2
WHERE A.col1 = B2.col1
AND A.col2 = B2.col2);

The error I get is:

-815, ERROR: A GROUP BY OR HAVING CLAUSE IS IMPLICITLY OR EXPLICITLY SPECIFIED IN A SUBSELECT OF A BASIC PREDICATE OR A SET CLAUSE OF AN UPDATE STATEMENT

Can you not use a group by clause in an update statement? Does anyone know a way around this?

Thanks

 

[stevexff]

Brian's query looks like it ought to work. Is it possible that the inner SELECT might not find a corresponding COL1 and COL2 on table B - this might cause an attempt to set the UPD_DT to null. Try modifying Brian's query to a SELECT instead of an UPDATE, and see what prints out.

SELECT a.key_column, a.upd_dt,
     (select min(b.upd_dt)
      from tableb b
      where a.col1 = b.col1  and
            a.col2 = b.col2) as created_dt
from tablea a;

(not tested)

 

[blom0344]

Does anyone know a way around this?

Did you consider storing the aggregate outcome within temp table / view first?
DB2 updates are notoriously finnicky, may be that the aggregate as inner part will not work as expected...

Ties Blom
Information analyst

 

[blom0344]

Just did a little test using a view that stores the min values. This leads to a simple update statement, but it looks like a costly solution:

UPDATE TABLE1 A
SET QTY_ORDER = (SELECT QTY FROM VIEW B WHERE A.FISC_YEAR = B.FYEAR AND A.FISC_MONTH = B.FMONTH)
WHERE .............

Ties Blom
Information analyst

 

[BrianTyler]

If there is a chance that there are rows in tablea with no corresponding rows in table b, you will need:

update tablea a
set a.upd_dt =
(select min(b.upd_dt)
from tableb
where a.col1 = b.col1 and
a.col2 = b.col2)
where exists
(select c.col1
from tableb c
where a.col1 = c.col1 and
a.col2 = c.col2)

 

[blom0344]

Brian,

You are definitely right about adding the 'exists' clause for the reasons you mention. I think the problem lies in the update having to use a value based on the min value of two dimensions. So ,the referenced value is the outcome of an aggregate. I have no idea if this is possible at all within ONE SQL statement...

Ties Blom
Information analyst

 

[BrianTyler]

Ties,

I can't see any reason why it should not work. The two dimensions identify a sub-set of the table, and the aggregate function is simply the minimum value for that subset. Where you could get problems would be if there was a need for a 'group by', introducing a heirarchy.

brian

 

[blom0344]

Seems to me that a group by is mandatory if you calculate the min value over 2 dimensions.....

Ties Blom
Information analyst

 

[jeisch]

Thanks Ties,

I ended up loading the data into a temporary table, and then doing my update from there. I don't think I was going to be able to get around the group by clause, which I think I do need.

Jim