Solved

How to write UPDATE with VALUES

Posted on 2008-06-11
10
212 Views
Last Modified: 2010-03-20
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
Comment
Question by:angie_angie
  • 5
  • 2
  • 2
  • +1
10 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21758426
the correct UPDATE syntax is this:
UPDATE myTable 
  SET aNumberField = 12345
    , astrField = 'aaa'
   , anotherStrField = 'bbb'
WHERE someid = 1

Open in new window

0
 

Author Comment

by:angie_angie
ID: 21758465
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
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 21758810
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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 21758821
If this is in code, you can do this:

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

AW
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 21758913
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
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 21759398
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
 

Author Comment

by:angie_angie
ID: 21765481
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
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 21766075
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
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 21766273
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
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 21768371
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server - Select Distinct and Count Rows for Each Distinct Row Returned 2 22
Footer for each row on Gridview 2 21
sql server concatenate fields 10 36
Query Syntax 17 36
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

803 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