473,545 Members | 2,025 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How to do a sql update based on a join?

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

Apr 28 '06 #1
9 33645

"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
Apr 28 '06 #2
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.

Apr 28 '06 #3
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

Apr 28 '06 #4
>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.

Apr 28 '06 #5

"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

Apr 28 '06 #6

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.

Apr 28 '06 #7

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

Apr 28 '06 #8
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

May 1 '06 #9
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

May 2 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
12572
by: meyvn77 | last post by:
Im using an ADP to connect to a SQL Sqever DB. In access it was really easy to say Inner join on table1 and table2 and update columnA from table1 with columnC from table2 where table1.key = table2.key and table2 columnB = 1 and table2 columnD = 4 I have tried all manner of beasts to get this thing to work..
2
66146
by: beena | last post by:
Apologize for posting this question.... Yes there were postings on update with join.... My question involves 4 table join... (hopefully qualifies as a new question) Need to convert the following sql from SYbase to UDB 8.2 FP8 on AIX. UPDATE TABLE A SET A.FLD_SUPV = B.FLD_SUPV FROM TABLEA A, TABLEB B, TABLEC C,TABLED D
1
1670
by: Juzam55 | last post by:
I have a table of mappings of various codes, tbl_Map with fields mapID, code1 and code2 I need to write and Update command that for each row in tbl_Map updates another table's, tbl_Codes, "code" field with code2 where it = code1 So in the end, tbl_Codes contain codes based on the mapping rules in tbl_Map.
2
1414
by: none | last post by:
I do not understand what is wrong with my query: mysql> update prices,prices_tmp set prices.new_ecampus=prices_tmp.new where prices.isbn = prices_tmp.isbn; ERROR 1064: You have an error in your SQL syntax near 'prices_tmp set prices.new_ecampus=prices_tmp.new where prices.isbn = prices_tmp.' at line 1
5
15491
by: Sphenix | last post by:
------------------------ UPDATE A SET A.ID = '?' + A.ID FROM TABLEA A LEFT OUTER JOIN TABLEB B ON A.INDEX = B.INDEX WHERE B.DUP_ID IS NULL ------------------------ seems like update with join statement not working in DB2 version 5
2
2122
by: Kc-Mass | last post by:
I have a form that users use to input the fiscal year for an update query. I have that value (after input sitting in a public variable and a Form control. How do I get Access to use that value to update the FY in the table within the general update query? Thx for ideas/sources.
7
2474
by: jayman9782 | last post by:
Someone please help. I'm running the following SQL query in sql server 2000... UPDATE S SET S. = B. FROM B LEFT OUTER JOIN S ON B. = S.Account WHERE (S. = 2007) All it does is zero out the field I'm trying to update. I'm almost positive the syntax is 100% correct, but then again it is...
7
1635
by: Jashua Cumbie | last post by:
I have recently imported some data to SQL for a client, using files provided by another company. We made edits in Excel, using different vlookups and concatenates, and have now realized that one field called ShortFileName is not correct. The table (File) with the incorrect ShortFileName data has other relational tables that point to it...
0
7479
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7926
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
7773
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
4962
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3468
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3450
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1901
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1028
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
722
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.