Solved

sql update

Posted on 2006-07-08
16
231 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
  • 8
  • 5
  • 2
  • +1
16 Comments
 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 200 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
 
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 100 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 45

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 200 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 45

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 45

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 45

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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 utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

895 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now