RODBC - Why is my update query not working?How does database indexing work?SQL update from one Table to another based on a ID matchInserting multiple rows in a single SQL query?SQL update query using joinsMySQL - UPDATE query based on SELECT QueryHow can I do an UPDATE statement with JOIN in SQL?Update a table using JOIN in SQL Server?How do I UPDATE from a SELECT in SQL Server?MySQL error code: 1175 during UPDATE in MySQL WorkbenchRODBC-Can anyone tell me why this doesn't work?
Car headlights in a world without electricity
Do creatures with a listed speed of "0 ft., fly 30 ft. (hover)" ever touch the ground?
Does Dispel Magic work on Tiny Hut?
What is the opposite of "eschatology"?
Could the museum Saturn V's be refitted for one more flight?
Why was Sir Cadogan fired?
Is this draw by repetition?
Did 'Cinema Songs' exist during Hiranyakshipu's time?
Avoiding the "not like other girls" trope?
Mathematica command that allows it to read my intentions
Can someone clarify Hamming's notion of important problems in relation to modern academia?
How to find if SQL server backup is encrypted with TDE without restoring the backup
GFCI outlets - can they be repaired? Are they really needed at the end of a circuit?
Using "tail" to follow a file without displaying the most recent lines
What are the G forces leaving Earth orbit?
Fair gambler's ruin problem intuition
How to stretch the corners of this image so that it looks like a perfect rectangle?
Is there a hemisphere-neutral way of specifying a season?
Does the Idaho Potato Commission associate potato skins with healthy eating?
Can a virus destroy the BIOS of a modern computer?
What reasons are there for a Capitalist to oppose a 100% inheritance tax?
What Exploit Are These User Agents Trying to Use?
Finding the error in an argument
Finitely generated matrix groups whose eigenvalues are all algebraic
RODBC - Why is my update query not working?
How does database indexing work?SQL update from one Table to another based on a ID matchInserting multiple rows in a single SQL query?SQL update query using joinsMySQL - UPDATE query based on SELECT QueryHow can I do an UPDATE statement with JOIN in SQL?Update a table using JOIN in SQL Server?How do I UPDATE from a SELECT in SQL Server?MySQL error code: 1175 during UPDATE in MySQL WorkbenchRODBC-Can anyone tell me why this doesn't work?
0
I'm reading in a .csv file and comparing two fields, one in a table called "Col" and one in the .csv file called "newdata". If they match I overwrite item_price in the "COL" table with the value of avg_price in "newdata". I get no errors, but nothing is changed in COL.
library(RODBC)
db <- "C:/Projects/Online.accdb"
conn <- odbcConnectAccess2007(db)
newData <- read.csv("C:/Projects/duplicates.csv", stringsAsFactors = F)
for(row in 1:nrow(newData))
query <- paste0(
"UPDATE COL
SET item_price = ",newData$avg_price[row],
"WHERE COL.generic ='",newData$generic[row],"'"
)
sqlQuery(conn, query)
close(conn)
sql r sql-update rodbc
asked Mar 8 at 19:47
AngusAngus
677
|
show 2 more comments
0
I'm reading in a .csv file and comparing two fields, one in a table called "Col" and one in the .csv file called "newdata". If they match I overwrite item_price in the "COL" table with the value of avg_price in "newdata". I get no errors, but nothing is changed in COL.
library(RODBC)
db <- "C:/Projects/Online.accdb"
conn <- odbcConnectAccess2007(db)
newData <- read.csv("C:/Projects/duplicates.csv", stringsAsFactors = F)
for(row in 1:nrow(newData))
query <- paste0(
"UPDATE COL
SET item_price = ",newData$avg_price[row],
"WHERE COL.generic ='",newData$generic[row],"'"
)
sqlQuery(conn, query)
close(conn)
sql r sql-update rodbc
asked Mar 8 at 19:47
AngusAngus
677
Can you print(query) within the loop and does it look like a correct sql that matches your Access db with a legit where clause? If you execute one of those print(query) statements in the underlying Access manually does it change your COL as expected?
– Soren
Mar 8 at 20:18
The item_price is not shown:[1] "UPDATE COL n SET item_price = WHERE COL.generic ='8440-20-008-7933'"
– Angus
Mar 8 at 20:25
I assume I can just run a query within access to see where I went wrong, but I thought I could learn by doing in R.
– Angus
Mar 8 at 20:36
what does head(newData) give you?
– Soren
Mar 8 at 20:37
generic avg..cost 1 8440-20-008-7933 6.070 2 8440-20-001-0714 9.735 3 8405-20-003-0729 20.540 4 8315-21-868-7262 9.315 5 8315-21-899-3717 19.190 6 8315-21-899-3721 7.765
– Angus
Mar 8 at 20:52
|
show 2 more comments
0
0
0
I'm reading in a .csv file and comparing two fields, one in a table called "Col" and one in the .csv file called "newdata". If they match I overwrite item_price in the "COL" table with the value of avg_price in "newdata". I get no errors, but nothing is changed in COL.
library(RODBC)
db <- "C:/Projects/Online.accdb"
conn <- odbcConnectAccess2007(db)
newData <- read.csv("C:/Projects/duplicates.csv", stringsAsFactors = F)
for(row in 1:nrow(newData))
query <- paste0(
"UPDATE COL
SET item_price = ",newData$avg_price[row],
"WHERE COL.generic ='",newData$generic[row],"'"
)
sqlQuery(conn, query)
close(conn)
sql r sql-update rodbc
asked Mar 8 at 19:47
AngusAngus
677
I'm reading in a .csv file and comparing two fields, one in a table called "Col" and one in the .csv file called "newdata". If they match I overwrite item_price in the "COL" table with the value of avg_price in "newdata". I get no errors, but nothing is changed in COL.
library(RODBC)
db <- "C:/Projects/Online.accdb"
conn <- odbcConnectAccess2007(db)
newData <- read.csv("C:/Projects/duplicates.csv", stringsAsFactors = F)
for(row in 1:nrow(newData))
query <- paste0(
"UPDATE COL
SET item_price = ",newData$avg_price[row],
"WHERE COL.generic ='",newData$generic[row],"'"
)
sqlQuery(conn, query)
close(conn)
sql r sql-update rodbc
sql r sql-update rodbc
asked Mar 8 at 19:47
AngusAngus
677
asked Mar 8 at 19:47
AngusAngus
677
edited Mar 8 at 20:12
Angus
asked Mar 8 at 19:47
AngusAngus
677
asked Mar 8 at 19:47
AngusAngus
677
asked Mar 8 at 19:47
AngusAngus
677
677
Can you print(query) within the loop and does it look like a correct sql that matches your Access db with a legit where clause? If you execute one of those print(query) statements in the underlying Access manually does it change your COL as expected?
– Soren
Mar 8 at 20:18
The item_price is not shown:[1] "UPDATE COL n SET item_price = WHERE COL.generic ='8440-20-008-7933'"
– Angus
Mar 8 at 20:25
I assume I can just run a query within access to see where I went wrong, but I thought I could learn by doing in R.
– Angus
Mar 8 at 20:36
what does head(newData) give you?
– Soren
Mar 8 at 20:37
generic avg..cost 1 8440-20-008-7933 6.070 2 8440-20-001-0714 9.735 3 8405-20-003-0729 20.540 4 8315-21-868-7262 9.315 5 8315-21-899-3717 19.190 6 8315-21-899-3721 7.765
– Angus
Mar 8 at 20:52
|
show 2 more comments
Can you print(query) within the loop and does it look like a correct sql that matches your Access db with a legit where clause? If you execute one of those print(query) statements in the underlying Access manually does it change your COL as expected?
– Soren
Mar 8 at 20:18
The item_price is not shown:[1] "UPDATE COL n SET item_price = WHERE COL.generic ='8440-20-008-7933'"
– Angus
Mar 8 at 20:25
I assume I can just run a query within access to see where I went wrong, but I thought I could learn by doing in R.
– Angus
Mar 8 at 20:36
what does head(newData) give you?
– Soren
Mar 8 at 20:37
generic avg..cost 1 8440-20-008-7933 6.070 2 8440-20-001-0714 9.735 3 8405-20-003-0729 20.540 4 8315-21-868-7262 9.315 5 8315-21-899-3717 19.190 6 8315-21-899-3721 7.765
– Angus
Mar 8 at 20:52
Can you print(query) within the loop and does it look like a correct sql that matches your Access db with a legit where clause? If you execute one of those print(query) statements in the underlying Access manually does it change your COL as expected?
– Soren
Mar 8 at 20:18
Can you print(query) within the loop and does it look like a correct sql that matches your Access db with a legit where clause? If you execute one of those print(query) statements in the underlying Access manually does it change your COL as expected?
– Soren
Mar 8 at 20:18
The item_price is not shown:[1] "UPDATE COL n SET item_price = WHERE COL.generic ='8440-20-008-7933'"
– Angus
Mar 8 at 20:25
The item_price is not shown:[1] "UPDATE COL n SET item_price = WHERE COL.generic ='8440-20-008-7933'"
– Angus
Mar 8 at 20:25
I assume I can just run a query within access to see where I went wrong, but I thought I could learn by doing in R.
– Angus
Mar 8 at 20:36
I assume I can just run a query within access to see where I went wrong, but I thought I could learn by doing in R.
– Angus
Mar 8 at 20:36
what does head(newData) give you?
– Soren
Mar 8 at 20:37
what does head(newData) give you?
– Soren
Mar 8 at 20:37
generic avg..cost 1 8440-20-008-7933 6.070 2 8440-20-001-0714 9.735 3 8405-20-003-0729 20.540 4 8315-21-868-7262 9.315 5 8315-21-899-3717 19.190 6 8315-21-899-3721 7.765
– Angus
Mar 8 at 20:52
generic avg..cost 1 8440-20-008-7933 6.070 2 8440-20-001-0714 9.735 3 8405-20-003-0729 20.540 4 8315-21-868-7262 9.315 5 8315-21-899-3717 19.190 6 8315-21-899-3721 7.765
– Angus
Mar 8 at 20:52
|
show 2 more comments
1 Answer
1
active
oldest
votes
1
From the comments, suggest a fix below, which will change the column names of the input file to match the names of the query:
library(RODBC)
db <- "C:/Projects/Online.accdb"
conn <- odbcConnectAccess2007(db)
newData <- read.csv("C:/Projects/duplicates.csv", stringsAsFactors = F)
names(newData) <- c("generic","avg_price")
for(row in 1:nrow(newData))
query <- paste0("UPDATE COL SET item_price = ",newData$avg_price[row]," WHERE COL.generic ='",newData$generic[row],"'")
sqlQuery(conn, query)
close(conn)
answered Mar 8 at 20:58
SorenSoren
1,2531711
Soren, thanks very much for this. One question, it seems that it only affects part of the table. The table is over 3.5 million records. Do you see something in the code that would limit the update query?
– Angus
Mar 8 at 21:26
Glad it helped! See what you get with "length(unique(newData$generic))" this will give you the total maximum number of updates you're passing to the database. If the number you get here is, say, 100,000 then you can only expect that many updates. If the number is close to 3.5 millions, then you may want to look more closely at the value(s) of newData$generic to see if that generic 'id' value is a legitimate value that exists in your database
– Soren
Mar 8 at 21:31
The length(..) in newData is just 173. That's just the length of the newData file. It should be taking each record in newData, finding all records in COL that match it (out of 3.5 million records) and replace item_price in COL with the value in avg_price. But it seems to stop doing the update after a few hundred matches. Are you saying it will only update 173 records in COL for each match?
– Angus
Mar 8 at 21:36
Its tough to say without seeing the data on both sides of the .csv file and the Access file, but it looks like your loop is working properly even if it's half-way working. Where it's stopping working suggests it's not matching somehow. Possibly the generic ID value is malformatted on one side or the other so it's not finding the match (maybe there are spaces or extra punctuation in there?). If there's 173 unique csvID values, on your db you can do "select count(distinct generic) from COL" and see if there are 173 there to match against?
– Soren
Mar 8 at 21:43
OK, I'll work it through. I assume I also don't have to run a for loop through each record in the db.
– Angus
Mar 8 at 21:47
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55070047%2frodbc-why-is-my-update-query-not-working%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
From the comments, suggest a fix below, which will change the column names of the input file to match the names of the query:
library(RODBC)
db <- "C:/Projects/Online.accdb"
conn <- odbcConnectAccess2007(db)
newData <- read.csv("C:/Projects/duplicates.csv", stringsAsFactors = F)
names(newData) <- c("generic","avg_price")
for(row in 1:nrow(newData))
query <- paste0("UPDATE COL SET item_price = ",newData$avg_price[row]," WHERE COL.generic ='",newData$generic[row],"'")
sqlQuery(conn, query)
close(conn)
answered Mar 8 at 20:58
SorenSoren
1,2531711
Soren, thanks very much for this. One question, it seems that it only affects part of the table. The table is over 3.5 million records. Do you see something in the code that would limit the update query?
– Angus
Mar 8 at 21:26
Glad it helped! See what you get with "length(unique(newData$generic))" this will give you the total maximum number of updates you're passing to the database. If the number you get here is, say, 100,000 then you can only expect that many updates. If the number is close to 3.5 millions, then you may want to look more closely at the value(s) of newData$generic to see if that generic 'id' value is a legitimate value that exists in your database
– Soren
Mar 8 at 21:31
The length(..) in newData is just 173. That's just the length of the newData file. It should be taking each record in newData, finding all records in COL that match it (out of 3.5 million records) and replace item_price in COL with the value in avg_price. But it seems to stop doing the update after a few hundred matches. Are you saying it will only update 173 records in COL for each match?
– Angus
Mar 8 at 21:36
Its tough to say without seeing the data on both sides of the .csv file and the Access file, but it looks like your loop is working properly even if it's half-way working. Where it's stopping working suggests it's not matching somehow. Possibly the generic ID value is malformatted on one side or the other so it's not finding the match (maybe there are spaces or extra punctuation in there?). If there's 173 unique csvID values, on your db you can do "select count(distinct generic) from COL" and see if there are 173 there to match against?
– Soren
Mar 8 at 21:43
OK, I'll work it through. I assume I also don't have to run a for loop through each record in the db.
– Angus
Mar 8 at 21:47
add a comment |
1
From the comments, suggest a fix below, which will change the column names of the input file to match the names of the query:
library(RODBC)
db <- "C:/Projects/Online.accdb"
conn <- odbcConnectAccess2007(db)
newData <- read.csv("C:/Projects/duplicates.csv", stringsAsFactors = F)
names(newData) <- c("generic","avg_price")
for(row in 1:nrow(newData))
query <- paste0("UPDATE COL SET item_price = ",newData$avg_price[row]," WHERE COL.generic ='",newData$generic[row],"'")
sqlQuery(conn, query)
close(conn)
answered Mar 8 at 20:58
SorenSoren
1,2531711
Soren, thanks very much for this. One question, it seems that it only affects part of the table. The table is over 3.5 million records. Do you see something in the code that would limit the update query?
– Angus
Mar 8 at 21:26
Glad it helped! See what you get with "length(unique(newData$generic))" this will give you the total maximum number of updates you're passing to the database. If the number you get here is, say, 100,000 then you can only expect that many updates. If the number is close to 3.5 millions, then you may want to look more closely at the value(s) of newData$generic to see if that generic 'id' value is a legitimate value that exists in your database
– Soren
Mar 8 at 21:31
The length(..) in newData is just 173. That's just the length of the newData file. It should be taking each record in newData, finding all records in COL that match it (out of 3.5 million records) and replace item_price in COL with the value in avg_price. But it seems to stop doing the update after a few hundred matches. Are you saying it will only update 173 records in COL for each match?
– Angus
Mar 8 at 21:36
Its tough to say without seeing the data on both sides of the .csv file and the Access file, but it looks like your loop is working properly even if it's half-way working. Where it's stopping working suggests it's not matching somehow. Possibly the generic ID value is malformatted on one side or the other so it's not finding the match (maybe there are spaces or extra punctuation in there?). If there's 173 unique csvID values, on your db you can do "select count(distinct generic) from COL" and see if there are 173 there to match against?
– Soren
Mar 8 at 21:43
OK, I'll work it through. I assume I also don't have to run a for loop through each record in the db.
– Angus
Mar 8 at 21:47
add a comment |
1
1
1
From the comments, suggest a fix below, which will change the column names of the input file to match the names of the query:
library(RODBC)
db <- "C:/Projects/Online.accdb"
conn <- odbcConnectAccess2007(db)
newData <- read.csv("C:/Projects/duplicates.csv", stringsAsFactors = F)
names(newData) <- c("generic","avg_price")
for(row in 1:nrow(newData))
query <- paste0("UPDATE COL SET item_price = ",newData$avg_price[row]," WHERE COL.generic ='",newData$generic[row],"'")
sqlQuery(conn, query)
close(conn)
answered Mar 8 at 20:58
SorenSoren
1,2531711
From the comments, suggest a fix below, which will change the column names of the input file to match the names of the query:
library(RODBC)
db <- "C:/Projects/Online.accdb"
conn <- odbcConnectAccess2007(db)
newData <- read.csv("C:/Projects/duplicates.csv", stringsAsFactors = F)
names(newData) <- c("generic","avg_price")
for(row in 1:nrow(newData))
query <- paste0("UPDATE COL SET item_price = ",newData$avg_price[row]," WHERE COL.generic ='",newData$generic[row],"'")
sqlQuery(conn, query)
close(conn)
answered Mar 8 at 20:58
SorenSoren
1,2531711
answered Mar 8 at 20:58
SorenSoren
1,2531711
answered Mar 8 at 20:58
SorenSoren
1,2531711
answered Mar 8 at 20:58
SorenSoren
1,2531711
1,2531711
Soren, thanks very much for this. One question, it seems that it only affects part of the table. The table is over 3.5 million records. Do you see something in the code that would limit the update query?
– Angus
Mar 8 at 21:26
Glad it helped! See what you get with "length(unique(newData$generic))" this will give you the total maximum number of updates you're passing to the database. If the number you get here is, say, 100,000 then you can only expect that many updates. If the number is close to 3.5 millions, then you may want to look more closely at the value(s) of newData$generic to see if that generic 'id' value is a legitimate value that exists in your database
– Soren
Mar 8 at 21:31
The length(..) in newData is just 173. That's just the length of the newData file. It should be taking each record in newData, finding all records in COL that match it (out of 3.5 million records) and replace item_price in COL with the value in avg_price. But it seems to stop doing the update after a few hundred matches. Are you saying it will only update 173 records in COL for each match?
– Angus
Mar 8 at 21:36
Its tough to say without seeing the data on both sides of the .csv file and the Access file, but it looks like your loop is working properly even if it's half-way working. Where it's stopping working suggests it's not matching somehow. Possibly the generic ID value is malformatted on one side or the other so it's not finding the match (maybe there are spaces or extra punctuation in there?). If there's 173 unique csvID values, on your db you can do "select count(distinct generic) from COL" and see if there are 173 there to match against?
– Soren
Mar 8 at 21:43
OK, I'll work it through. I assume I also don't have to run a for loop through each record in the db.
– Angus
Mar 8 at 21:47
add a comment |
Soren, thanks very much for this. One question, it seems that it only affects part of the table. The table is over 3.5 million records. Do you see something in the code that would limit the update query?
– Angus
Mar 8 at 21:26
Glad it helped! See what you get with "length(unique(newData$generic))" this will give you the total maximum number of updates you're passing to the database. If the number you get here is, say, 100,000 then you can only expect that many updates. If the number is close to 3.5 millions, then you may want to look more closely at the value(s) of newData$generic to see if that generic 'id' value is a legitimate value that exists in your database
– Soren
Mar 8 at 21:31
The length(..) in newData is just 173. That's just the length of the newData file. It should be taking each record in newData, finding all records in COL that match it (out of 3.5 million records) and replace item_price in COL with the value in avg_price. But it seems to stop doing the update after a few hundred matches. Are you saying it will only update 173 records in COL for each match?
– Angus
Mar 8 at 21:36
Its tough to say without seeing the data on both sides of the .csv file and the Access file, but it looks like your loop is working properly even if it's half-way working. Where it's stopping working suggests it's not matching somehow. Possibly the generic ID value is malformatted on one side or the other so it's not finding the match (maybe there are spaces or extra punctuation in there?). If there's 173 unique csvID values, on your db you can do "select count(distinct generic) from COL" and see if there are 173 there to match against?
– Soren
Mar 8 at 21:43
OK, I'll work it through. I assume I also don't have to run a for loop through each record in the db.
– Angus
Mar 8 at 21:47
Soren, thanks very much for this. One question, it seems that it only affects part of the table. The table is over 3.5 million records. Do you see something in the code that would limit the update query?
– Angus
Mar 8 at 21:26
Soren, thanks very much for this. One question, it seems that it only affects part of the table. The table is over 3.5 million records. Do you see something in the code that would limit the update query?
– Angus
Mar 8 at 21:26
Glad it helped! See what you get with "length(unique(newData$generic))" this will give you the total maximum number of updates you're passing to the database. If the number you get here is, say, 100,000 then you can only expect that many updates. If the number is close to 3.5 millions, then you may want to look more closely at the value(s) of newData$generic to see if that generic 'id' value is a legitimate value that exists in your database
– Soren
Mar 8 at 21:31
Glad it helped! See what you get with "length(unique(newData$generic))" this will give you the total maximum number of updates you're passing to the database. If the number you get here is, say, 100,000 then you can only expect that many updates. If the number is close to 3.5 millions, then you may want to look more closely at the value(s) of newData$generic to see if that generic 'id' value is a legitimate value that exists in your database
– Soren
Mar 8 at 21:31
The length(..) in newData is just 173. That's just the length of the newData file. It should be taking each record in newData, finding all records in COL that match it (out of 3.5 million records) and replace item_price in COL with the value in avg_price. But it seems to stop doing the update after a few hundred matches. Are you saying it will only update 173 records in COL for each match?
– Angus
Mar 8 at 21:36
The length(..) in newData is just 173. That's just the length of the newData file. It should be taking each record in newData, finding all records in COL that match it (out of 3.5 million records) and replace item_price in COL with the value in avg_price. But it seems to stop doing the update after a few hundred matches. Are you saying it will only update 173 records in COL for each match?
– Angus
Mar 8 at 21:36
Its tough to say without seeing the data on both sides of the .csv file and the Access file, but it looks like your loop is working properly even if it's half-way working. Where it's stopping working suggests it's not matching somehow. Possibly the generic ID value is malformatted on one side or the other so it's not finding the match (maybe there are spaces or extra punctuation in there?). If there's 173 unique csvID values, on your db you can do "select count(distinct generic) from COL" and see if there are 173 there to match against?
– Soren
Mar 8 at 21:43
Its tough to say without seeing the data on both sides of the .csv file and the Access file, but it looks like your loop is working properly even if it's half-way working. Where it's stopping working suggests it's not matching somehow. Possibly the generic ID value is malformatted on one side or the other so it's not finding the match (maybe there are spaces or extra punctuation in there?). If there's 173 unique csvID values, on your db you can do "select count(distinct generic) from COL" and see if there are 173 there to match against?
– Soren
Mar 8 at 21:43
OK, I'll work it through. I assume I also don't have to run a for loop through each record in the db.
– Angus
Mar 8 at 21:47
OK, I'll work it through. I assume I also don't have to run a for loop through each record in the db.
– Angus
Mar 8 at 21:47
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55070047%2frodbc-why-is-my-update-query-not-working%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Can you print(query) within the loop and does it look like a correct sql that matches your Access db with a legit where clause? If you execute one of those print(query) statements in the underlying Access manually does it change your COL as expected?
– Soren
Mar 8 at 20:18
The item_price is not shown:[1] "UPDATE COL n SET item_price = WHERE COL.generic ='8440-20-008-7933'"
– Angus
Mar 8 at 20:25
I assume I can just run a query within access to see where I went wrong, but I thought I could learn by doing in R.
– Angus
Mar 8 at 20:36
what does head(newData) give you?
– Soren
Mar 8 at 20:37
generic avg..cost 1 8440-20-008-7933 6.070 2 8440-20-001-0714 9.735 3 8405-20-003-0729 20.540 4 8315-21-868-7262 9.315 5 8315-21-899-3717 19.190 6 8315-21-899-3721 7.765
– Angus
Mar 8 at 20:52