[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 240
  • 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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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