Hi

I have to perform an update on a table. I am having problems figuring
out how to join two tables as I need to check a value in a different
table before performing the update.

I have two tables here

Table1
ID TIMESTAMP

Table2
ID Value

I would like update value in table2 based on the timestamp?

Any help appreciated?

Thanks
Mahesh

"Mahesh S" <mahesh.leo@gmail.com> wrote in message
news:1146224308.703531.47450@u72g2000cwu.googlegro ups.com...
> Hi
>
> I have to perform an update on a table. I am having problems figuring
> out how to join two tables as I need to check a value in a different
> table before performing the update.
>
> I have two tables here
>
> Table1
> ID TIMESTAMP
>
> Table2
> ID Value
>
> I would like update value in table2 based on the timestamp?
>
> Any help appreciated?
>
You can only update a single table at a time, never a join of tables. But
you could do a subquery in the update statement. You haven't specified the
remaining columns in the two tables - I assume there are other columns in
the tables! - so here is an example, created from thin air, that contains
two of the tables in the Sample database:

Update Department
set deptname = 'Sales'
where mgrno = (select empno from Employee where lastname = 'HAAS');

In this example, I want to change the name of a department in the Department
table. For some reason, I can't recall the number of the department but I
know its manager has the last name Haas, which is written entirely in
uppercase in the database. I also know that the MGRNO column in the
Department table contains the employee number of the employee who manages
the department. That enables me to write a subquery which looks through the
Employee table for the employee number of Haas. Then, the outer part of my
Update statement changes the department name for the department that is
managed by Haas.

Assuming your two tables have something in common along the lines of the
Empno/Mgrno relationship, you should be able to write a similar Update
statement so that you can change the ID in Table2 based on the timestamp
value in Table1.

On the other hand, if your two tables really only have the two columns
depicted in your question, you will not be able to do the update you want
because the tables have nothing in common. In that case, you need to
redesign your tables.

--
Rhino

Thw question sounds vague. But i'll take a guess.

UPDATE Table2 SET Value = ?? WHERE EXISTS
(SELECT * FROM Table1 WHERE Id = Table2.Id AND TIMESTAMP = ???)

B.

Hi Brian and Rhino

Thanks for the response. Yes, Brian, you suggestion is closer to what
I am looking for.

I have also inlcuded the the update statement I am using.

UPDATE
HEALTHCAREDB.GLUCOSE_DATA
SET
HEALTHCAREDB.GLUCOSE_DATA.GLUCOSE = 5.5
where exists

(
SELECT
HEALTHCAREDB.GLUCOSE_DATA.GLUCOSE,
HEALTHCAREDB.SENSOR_DATA.TIMESTAMP
FROM
HEALTHCAREDB.GLUCOSE_DATA, HEALTHCAREDB.SENSOR_DATA
WHERE
HEALTHCAREDB.GLUCOSE_DATA.DATA_ID =
HEALTHCAREDB.SENSOR_DATA.DATA_ID
AND HOUR(HEALTHCAREDB.SENSOR_DATA.TIMESTAMP) > 7
AND HOUR(HEALTHCAREDB.SENSOR_DATA.TIMESTAMP) < 10
AND MINUTE(HEALTHCAREDB.SENSOR_DATA.TIMESTAMP) > 45

)


I now seem to have another problem. I am getting "transaction log"
full problem.

I performed this command to increase the log space - db2 update db cfg
for CAR_DCCR using LOGFILSIZ 5000

This had an effect as in it takes longer now for the transcation full
problem to occur.

The table Glucose_Data that I am trying to update has around 1.5
million records.

Any suggestions as to how I can get around the log problem?

Thanks
Mahesh

>AND HOUR(HEALTHCAREDB.SENSOR_DATA.TIMESTAMP) > 7
>AND HOUR(HEALTHCAREDB.SENSOR_DATA.TIMESTAMP) < 10

It would be better and clearer to use BETWEEN:

AND HOUR(HEALTHCAREDB.SENSOR_DATA.TIMESTAMP) BETWEEN 7 AND 10


The EXISTS statement should not require a repeat of the GLUCOSE_DATA
TABLE. Not should anything be SELECTed. EXISTS just checks that a
record is returned. The actual data is irrelevant.

UPDATE
HEALTHCAREDB.GLUCOSE_DATA Glucose
SET
Glucose.GLUCOSE = 5.5
WHERE
EXISTS
(
SELECT
*
FROM
HEALTHCAREDB.SENSOR_DATA Sensor
WHERE
-- Correlate without outer query.
Glucose.DATA_ID = Sensor.DATA_ID
-- Only grab the right time frame.
AND HOUR(Sensor.TIMESTAMP) BETWEEN 7 AND 10
AND MINUTE(Sensor.TIMESTAMP) > 45
)

B.

"Brian Tkatch" <Maxwell_Smart@ThePentagon.com> wrote in message
news:1146235693.010308.264720@u72g2000cwu.googlegr oups.com...
> >AND HOUR(HEALTHCAREDB.SENSOR_DATA.TIMESTAMP) > 7
>>AND HOUR(HEALTHCAREDB.SENSOR_DATA.TIMESTAMP) < 10
>
> It would be better and clearer to use BETWEEN:
>
> AND HOUR(HEALTHCAREDB.SENSOR_DATA.TIMESTAMP) BETWEEN 7 AND 10
>
Actually, you should use

AND HOUR(HEALTHCAREDB.SENSOR_DATA.TIMESTAMP) BETWEEN 8 AND 9

'BETWEEN' is inclusive so, if you said BETWEEN 7 AND 10, you'd get 7, 8, 9
and 10 in the result. The original version of the query wants the value to
be greater than 7 and less than 10.

--
Rhino

Rhino wrote:
> "Brian Tkatch" <Maxwell_Smart@ThePentagon.com> wrote in message
> news:1146235693.010308.264720@u72g2000cwu.googlegr oups.com...
> > >AND HOUR(HEALTHCAREDB.SENSOR_DATA.TIMESTAMP) > 7
> >>AND HOUR(HEALTHCAREDB.SENSOR_DATA.TIMESTAMP) < 10
> >
> > It would be better and clearer to use BETWEEN:
> >
> > AND HOUR(HEALTHCAREDB.SENSOR_DATA.TIMESTAMP) BETWEEN 7 AND 10
> >
> Actually, you should use
>
> AND HOUR(HEALTHCAREDB.SENSOR_DATA.TIMESTAMP) BETWEEN 8 AND 9
>
> 'BETWEEN' is inclusive so, if you said BETWEEN 7 AND 10, you'd get 7, 8, 9
> and 10 in the result. The original version of the query wants the value to
> be greater than 7 and less than 10.
>
> --
> Rhino

You are correct. Thanx for the catch.

B.

you can use the following construct:

merge table2 using table1 on
table1.id = table2.id
when matched then
update set value = table1.timestamp


Thanks,
Sumanth


"Mahesh S" <mahesh.leo@gmail.com> wrote in message
news:1146224308.703531.47450@u72g2000cwu.googlegro ups.com...
> Hi
>
> I have to perform an update on a table. I am having problems figuring
> out how to join two tables as I need to check a value in a different
> table before performing the update.
>
> I have two tables here
>
> Table1
> ID TIMESTAMP
>
> Table2
> ID Value
>
> I would like update value in table2 based on the timestamp?
>
> Any help appreciated?
>
> Thanks
> Mahesh
>

Mahesh,

Here is a way to get around the transaction full problem (adopted form
a Serge Reilieu method posted here earlier and using Brians modified
SQL):

Run the following SQL until it stops updating any rows:

UPDATE
(select Glucose.GLUCOSE from HEALTHCAREDB.GLUCOSE_DATA Glucose
WHERE
EXISTS
(
SELECT
*
FROM
HEALTHCAREDB.SENSOR_DATA Sensor
WHERE
-- Correlate without outer query.
Glucose.DATA_ID = Sensor.DATA_ID
-- Only grab the right time frame.
AND HOUR(Sensor.TIMESTAMP) BETWEEN 8 AND 9
AND MINUTE(Sensor.TIMESTAMP) > 45
)
where Glucose.GLUCOSE != 5.5
fetch first 10000 rows only)
SET Glucose.GLUCOSE = 5.5

This will update up to the next 10,000 rows each time it is run. If
you still have transaction log issues, then lower the limit.

-Chris

Hey Chris, Sumanth, Brian and Rhino

Thanks a lot for all the input.

Its been very helpful and yes, it does work.

Really appreciate it.

Cheers
Mahesh