multiple records update in single query with where class
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)
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)
ASKED:
January 27, 2010 7:53 PM
UPDATED:
January 27, 2010 10:01 PM
Answer Wiki:
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
-----------------
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
