Solved

sql update

Posted on 2006-07-08
16
230 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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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…
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…

746 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

12 Experts available now in Live!

Get 1:1 Help Now