Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

sql update

Posted on 2006-07-08
16
Medium Priority
?
238 Views
Last Modified: 2010-04-30
How would I write a sql statement that will look for one field from one table and match it to the same filed in another field.If found it will update an integer number in the second table.The value of the integer will be represented by a text box in my form.
0
Comment
Question by:thenone
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 2
  • +1
16 Comments
 
LVL 46

Assisted Solution

by:aikimark
aikimark earned 800 total points
ID: 17066685
execute the following SQL
"update table1 inner join table2 on table1.fieldname = table2.fieldname
set numfield = " & me.text1
0
 
LVL 8

Author Comment

by:thenone
ID: 17066919
would this update all of the fields at once?
0
 
LVL 19

Expert Comment

by:BrianGEFF719
ID: 17067037
Aikimark: I think he wants to update the SECOND table.

Brian
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 8

Author Comment

by:thenone
ID: 17067113
layout is like this

Table 1                       Table2
field1                          field1   id


update table2 "id" where table1 field1 matches table2 filed1 and use number from text field in vb.
0
 
LVL 19

Assisted Solution

by:BrianGEFF719
BrianGEFF719 earned 400 total points
ID: 17067185
I'm no SQL expert, but I think in that case you would want to change aikimark's suggestion to:

dim strSQL as string
strSQL = "update table2 inner join table2 on table1.fieldname = table2.fieldname set numfield = " & me.text1.text

Aikimark, correct me if i'm wrong please.


Brian
0
 
LVL 46

Expert Comment

by:aikimark
ID: 17067581
Brian,

That is not correct.  You have a typo.  It should be
dim strSQL as string
strSQL = "update [table2] inner join [table 1] on [table 1].[field1] = [table2].[field1] set [ID] = " & me.text1.text

0
 
LVL 6

Accepted Solution

by:
junglerover77 earned 800 total points
ID: 17067624
If you are using SQL Server as the database, aikimark's answer is absolutely correct. But other databases (e.g. DB2) will not support that SQL statement. Generally, you should make it like this:

dim strSQL as string
strSQL = "update table2 set ID = " & me.text1.text & " where field1 in (select field1 from table1)"

Jungle
0
 
LVL 8

Author Comment

by:thenone
ID: 17067869
I am using mysql 5.0
0
 
LVL 46

Expert Comment

by:aikimark
ID: 17068353
Please be aware that a field named "ID" is likely to be a primary key, which might prohibit you from changing its value.
0
 
LVL 8

Author Comment

by:thenone
ID: 17068800
my id is just a regular integer so it would be

comm.execute "update table1 inner join table2 on table1.fieldname = table2.fieldname
set numfield = " & me.text1"


Thanks everybody for their help I will post points this afternoon.
0
 
LVL 8

Author Comment

by:thenone
ID: 17073432
one more question if I was just adding the field number then what?
0
 
LVL 46

Expert Comment

by:aikimark
ID: 17075699
What do you mean?
0
 
LVL 8

Author Comment

by:thenone
ID: 17075726
ok so if I have a recordset where it is rs.open"select * from table1 where column=no"

then when I am looping through my records how can I have the recordset be able to change that column one at a time from no to yes.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 17076556
What does that have to do with this SQL Update question?
0
 
LVL 8

Author Comment

by:thenone
ID: 17076742
oops wrong question I posted my mistake what I mean to ask is how can I add the number to the existing number.

0
 
LVL 8

Author Comment

by:thenone
ID: 17076760
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses

661 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question