Solved

sql update

Posted on 2006-07-08
16
233 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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
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…

808 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