angie_angie
asked on
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!
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!
ASKER
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?
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
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
If this is in code, you can do this:
UPDATE myTable (aNumberField, aStrField, anotherStrField)
VALUES (" & Split(allValues, ",") & ")
AW
UPDATE myTable (aNumberField, aStrField, anotherStrField)
VALUES (" & Split(allValues, ",") & ")
AW
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
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
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
AW
ASKER
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""
??
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""
??
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
AW
Open in new window