Solved

sql update

Posted on 2006-07-08
16
232 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
ScreenConnect 6.0 Free Trial

Discover new time-saving features in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

 
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
 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

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…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

770 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