Solved

How to write UPDATE with VALUES

Posted on 2008-06-11
10
210 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]
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 44

Expert Comment

by:Arthur_Wood
Comment Utility
If this is in code, you can do this:

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

AW
0
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 44

Expert Comment

by:Arthur_Wood
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

762 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