How to do a sql update based on a join?

[Mahesh S]

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

[Rhino]

"Mahesh S" <ma********@gma il.com> wrote in message
news:11******** *************@u 72g2000cwu.goog legroups.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

[Brian Tkatch]

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.

[Mahesh S]

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.GL UCOSE_DATA
SET
HEALTHCAREDB.GL UCOSE_DATA.GLUC OSE = 5.5
where exists

(
SELECT
HEALTHCAREDB.GL UCOSE_DATA.GLUC OSE,
HEALTHCAREDB.SE NSOR_DATA.TIMES TAMP
FROM
HEALTHCAREDB.GL UCOSE_DATA, HEALTHCAREDB.SE NSOR_DATA
WHERE
HEALTHCAREDB.GL UCOSE_DATA.DATA _ID =
HEALTHCAREDB.SE NSOR_DATA.DATA_ ID
AND HOUR(HEALTHCARE DB.SENSOR_DATA. TIMESTAMP) > 7
AND HOUR(HEALTHCARE DB.SENSOR_DATA. TIMESTAMP) < 10
AND MINUTE(HEALTHCA REDB.SENSOR_DAT A.TIMESTAMP) > 45

)
I now seem to have another problem. I am getting "transactio n 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

[Brian Tkatch]

>AND HOUR(HEALTHCARE DB.SENSOR_DATA. TIMESTAMP) > 7

AND HOUR(HEALTHCARE DB.SENSOR_DATA. TIMESTAMP) < 10

It would be better and clearer to use BETWEEN:

AND HOUR(HEALTHCARE DB.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.GL UCOSE_DATA Glucose
SET
Glucose.GLUCOSE = 5.5
WHERE
EXISTS
(
SELECT
*
FROM
HEALTHCAREDB.SE NSOR_DATA Sensor
WHERE
-- Correlate without outer query.
Glucose.DATA_ID = Sensor.DATA_ID
-- Only grab the right time frame.
AND HOUR(Sensor.TIM ESTAMP) BETWEEN 7 AND 10
AND MINUTE(Sensor.T IMESTAMP) > 45
)

B.

[Rhino]

"Brian Tkatch" <Ma***********@ ThePentagon.com > wrote in message
news:11******** **************@ u72g2000cwu.goo glegroups.com.. .

AND HOUR(HEALTHCARE DB.SENSOR_DATA. TIMESTAMP) > 7
AND HOUR(HEALTHCARE DB.SENSOR_DATA. TIMESTAMP) < 10

It would be better and clearer to use BETWEEN:

AND HOUR(HEALTHCARE DB.SENSOR_DATA. TIMESTAMP) BETWEEN 7 AND 10

Actually, you should use

AND HOUR(HEALTHCARE DB.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

[Brian Tkatch]

Rhino wrote:

"Brian Tkatch" <Ma***********@ ThePentagon.com > wrote in message
news:11******** **************@ u72g2000cwu.goo glegroups.com.. .

AND HOUR(HEALTHCARE DB.SENSOR_DATA. TIMESTAMP) > 7
AND HOUR(HEALTHCARE DB.SENSOR_DATA. TIMESTAMP) < 10

It would be better and clearer to use BETWEEN:

AND HOUR(HEALTHCARE DB.SENSOR_DATA. TIMESTAMP) BETWEEN 7 AND 10

Actually, you should use

AND HOUR(HEALTHCARE DB.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.

[Sumanth]

you can use the following construct:

merge table2 using table1 on
table1.id = table2.id
when matched then
update set value = table1.timestam p
Thanks,
Sumanth
"Mahesh S" <ma********@gma il.com> wrote in message
news:11******** *************@u 72g2000cwu.goog legroups.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

[ChrisC]

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.GL UCOSE_DATA Glucose
WHERE
EXISTS
(
SELECT
*
FROM
HEALTHCAREDB.SE NSOR_DATA Sensor
WHERE
-- Correlate without outer query.
Glucose.DATA_ID = Sensor.DATA_ID
-- Only grab the right time frame.
AND HOUR(Sensor.TIM ESTAMP) BETWEEN 8 AND 9
AND MINUTE(Sensor.T IMESTAMP) > 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

[Mahesh S]

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