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

How to write UPDATE with VALUES

Hi,

I want to update some fields of some records in a table. My difficulty is that I have all the values stored in one string for all the fields that I want to update. Like this:

allValues = "12345, 'aaa', 'bbb'"
Can I do it in one shot to update my table? Meaning:

Before update, my table is like:
                     someID           aNumberField             aStrField         anotherStrField
                        1                        54321                       a                           b
 
After update, my table should be like:
                    someID           aNumberField             aStrField         anotherStrField
                        1                        12345                      aaa                      bbb
                   
I'm trying to use something like this:
UPDATE myTable (aNumberField, aStrField, anotherStrField)
VALUES (" & allValues & ")

It doesn't work. Can anyone help me on this? Is there a way not to seperate allValues since in my real problem, it's a lot more than just 3 values?

Thank you very much!
0
angie_angie
Asked:
angie_angie
  • 5
  • 2
  • 2
  • +1
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the correct UPDATE syntax is this:
UPDATE myTable 
  SET aNumberField = 12345
    , astrField = 'aaa'
   , anotherStrField = 'bbb'
WHERE someid = 1

Open in new window

0
 
angie_angieAuthor Commented:
Sorry I didn't make myself clear. The thing is, the values that are stored in allValues are from different resources which cannot be "seen" programmatically. Is there anyway to do it? Or can I delete the record and re-insert with the updated values?
0
 
Arthur_WoodCommented:
where is the variable AllValues coiming from, and where are you trying to execute this SQL?


UPDATE myTable (aNumberField, aStrField, anotherStrField)
VALUES (" & allValues & ")


you need to be a bit more explicit about precisly what you are trying to do, and where this is to be executed.  Is it in a Query or in code.

AW
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Arthur_WoodCommented:
If this is in code, you can do this:

UPDATE myTable (aNumberField, aStrField, anotherStrField)
VALUES (" & Split(allValues, ",") & ")

AW
0
 
Éric MoreauSenior .Net ConsultantCommented:
This syntax is not valid in SQL:
UPDATE myTable (aNumberField, aStrField, anotherStrField)
VALUES (" & Split(allValues, ",") & ")

This is the syntax of the INSERT statement. The update needs to be in the format provided by angelIII
0
 
Arthur_WoodCommented:
emoueau>>>Notice that I prefaced my suggestion with "If this is in code" - and if it is done in code, the the Split function is a valid call.

AW
0
 
angie_angieAuthor Commented:
Arthur Wood,

It is in VBA code. Are the string values in allValues seperated by single or double quotes??

i.e. should it be:
allValues = "12345, 'aaa', 'bbb'"
or,              "12345, "aaa", "bbb""

??
0
 
Arthur_WoodCommented:
it should be

allValues = "12345, 'aaa', 'bbb'"

and then you can utilize the Split function to create an array object which will hold the separate values as  "12345", "aaa", "bbb"

Dim myArray() as Variant
Dim allValues as String

AllValues = "12345, 'aaa', 'bbb'"

myArray = Split(allValues, ",")

UPDATE myTable (aNumberField, aStrField, anotherStrField)
VALUES (" & myArray & ")


CurrentDB.Execute("UPDATE myTable (aNumberField, aStrField, anotherStrField)
VALUES (" & myArray & ")")


or that last line could be

CurrentDb.Execute ("UPDATE myTable (aNumberField, aStrField, anotherStrField)
VALUES (" & Split(allValues, ",") & ")")

AW




0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
AW: I don't believe what I see? the UPDATE syntax you suggest is WRONG !!!


here is some code that will split the values (and the fields list) and generate the correct UPDATE syntax:
Dim allValues as String
Dim allFields as string
Dim arrValues() as string
dim arrFields() as string
 
AllValues = "12345, 'aaa', 'bbb'"
AllFields = "aNumberField, aStrField, anotherStrField"
 
arrValues = Split(allValues, ",")
arrFields = Split(allFields, ",")
 
Dim strSQL as string
dim strSep as string
Dim lngLoop as long
 
strSQL = " UPDATE myTable "
strSep = " SET "
 
for lngLoop = lbound(arrFields) to ubound(arrFields)
 strSQL = strSQL & strSep & arrFields(lngLoop) & " = " & arrValues(lngLoop)
 strSep = ", "
next
 
CurrentDB.Execute strSQL

Open in new window

0
 
Arthur_WoodCommented:
yeah, minor brain freeze.  I realized the error of my ways on the way to work this AM.  Oh well, what can I say.  LOL ;-)

AW
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

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