• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 241
  • Last Modified:

sql update

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
thenone
Asked:
thenone
  • 8
  • 5
  • 2
  • +1
3 Solutions
 
aikimarkCommented:
execute the following SQL
"update table1 inner join table2 on table1.fieldname = table2.fieldname
set numfield = " & me.text1
0
 
thenoneAuthor Commented:
would this update all of the fields at once?
0
 
BrianGEFF719Commented:
Aikimark: I think he wants to update the SECOND table.

Brian
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
thenoneAuthor Commented:
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
 
BrianGEFF719Commented:
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
 
aikimarkCommented:
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
 
junglerover77Commented:
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
 
thenoneAuthor Commented:
I am using mysql 5.0
0
 
aikimarkCommented:
Please be aware that a field named "ID" is likely to be a primary key, which might prohibit you from changing its value.
0
 
thenoneAuthor Commented:
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
 
thenoneAuthor Commented:
one more question if I was just adding the field number then what?
0
 
aikimarkCommented:
What do you mean?
0
 
thenoneAuthor Commented:
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
 
aikimarkCommented:
What does that have to do with this SQL Update question?
0
 
thenoneAuthor Commented:
oops wrong question I posted my mistake what I mean to ask is how can I add the number to the existing number.

0
 
thenoneAuthor Commented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 8
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now