multiple records update in single query with where class

10 pts.
Tags:
MS Access
VB6.0
Hi

I am doing one project using (Vb6.0 + ms-access)

I have two tables [Br style="outline-style: none; outline-width: initial; outline-color: initial;" /> [Br style="outline-style: none; outline-width: initial; outline-color: initial;" /> 1.segment-Table:[Br style="outline-style: none; outline-width: initial; outline-color: initial;" /> [Br style="outline-style: none; outline-width: initial; outline-color: initial;" /> Seg_id       Quotation_no      Sor_no     Op_no[Br style="outline-style: none; outline-width: initial; outline-color: initial;" /> -------      ------------      -------   --------[Br style="outline-style: none; outline-width: initial; outline-color: initial;" /> ACC-100        2229090        [Br style="outline-style: none; outline-width: initial; outline-color: initial;" /> MDC-101        2229091        [Br style="outline-style: none; outline-width: initial; outline-color: initial;" /> MDA-028        2229092        [Br style="outline-style: none; outline-width: initial; outline-color: initial;" /> ACC-100        2229094        [Br style="outline-style: none; outline-width: initial; outline-color: initial;" /> [Br style="outline-style: none; outline-width: initial; outline-color: initial;" /> 2.subseg-Table[Br style="outline-style: none; outline-width: initial; outline-color: initial;" /> [Br style="outline-style: none; outline-width: initial; outline-color: initial;" /> Quotation_no    Sor_no      Op_no[Br style="outline-style: none; outline-width: initial; outline-color: initial;" /> ------------    -------    -------[Br style="outline-style: none; outline-width: initial; outline-color: initial;" /> 2229090         0001898     A-0001[Br style="outline-style: none; outline-width: initial; outline-color: initial;" /> 2229091         0001899     A-0002[Br style="outline-style: none; outline-width: initial; outline-color: initial;" /> 2229092         0001900     A-0003[Br style="outline-style: none; outline-width: initial; outline-color: initial;" /> 2229094         0001901     A-0004[Br style="outline-style: none; outline-width: initial; outline-color: initial;" /> [Br style="outline-style: none; outline-width: initial; outline-color: initial;" /> i want to insert or update all the sor_no, op_no records from "subseg" table to "segment" table based on quotation_no.[Br style="outline-style: none; outline-width: initial; outline-color: initial;" /> [Br style="outline-style: none; outline-width: initial; outline-color: initial;" /> quotation_no is unique  for both tables.

please help me.

Thanks[Br style="outline-style: none; outline-width: initial; outline-color: initial;" /> sathik[Br style="mso-special-character: line-break; outline-style: none; outline-width: initial; outline-color: initial;" /> srbatcha@yahoo.co.in



Software/Hardware used:
software(vb6.0 + msaccess)

Answer Wiki

Thanks. We'll let you know when a new response is added.

The question is almost unreadable.

To update records in table SEGMENT with values from table SUBSEG, you could use a statement like this (I’m not sure if it will work on previous versions of access):

<pre>UPDATE segment st
INNER JOIN subseg ss
ON st.quotation_no = ss.quotation_no
SET st.sor_no = ss.sor_no, st.op_no = ss.op_no
WHERE …</pre>

To insert data from one table, into another table, you could use something like this:

<pre>INSERT INTO table_x (col1, col2, col3)
SELECT col1, col2, col3
FROM table_y
WHERE …</pre>

-CarlosDL
—————–

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following