Update SQL Statement Help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am attempting to run an UPDATE on the Assets_LineFeatures TABLE. This is
conditional based upon unmatched records in the INSPECTIONS Table. Here is
what I have so far, of course it doesn't work, but any ideas to help move me
in the right direction are much appreciated.

j


UPDATE Assets_LineFeatures
SET Assets_LineFeatures.COMMENTS = "Unable To Inspect Conduit"
LEFT JOIN INSPECTIONS ON Assets_LineFeatures.SYSASSET = INSPECTIONS.SYSASSET
WHERE (((INSPECTIONS.SYSASSET) Is Null)) AND
(((Assets_LinesFeatures.AssetName) LIKE "C*"));
 
So instead of trying to create the "FindUnmatched" right in the SQL, I built
and ran that seperately and inserted the results into another table
"UnmatchedConduits". So, now I just need to UPDATE
Asset_LineFeatures.COMMENTS with the string "Unable To Inspect Conduits"
WHERE Asset_LineFeatures.SYSASSET LIKE 'C*'...

Still running into problems with the syntax though.


UPDATE Assets_LineFeatures
SET Assets_LineFeatures.COMMENTS = "Unable To Inspect Conduit"
LEFT JOIN [UnmatchedConduits] ON Assets_LineFeatures.SYSASSET =
[UnmatchedConduits].SYSASSET
WHERE Assets_LinesFeatures.AssetName LIKE 'C*';
 
Success!

UPDATE Assets_LineFeatures
RIGHT JOIN UnmatchedConduits ON Assets_LineFeatures.SYSASSET =
UnmatchedConduits.SYSASSET
SET Assets_LineFeatures.COMMENT = 'Unable To Inspect Conduit';



jamesfreddyc said:
So instead of trying to create the "FindUnmatched" right in the SQL, I built
and ran that seperately and inserted the results into another table
"UnmatchedConduits". So, now I just need to UPDATE
Asset_LineFeatures.COMMENTS with the string "Unable To Inspect Conduits"
WHERE Asset_LineFeatures.SYSASSET LIKE 'C*'...

Still running into problems with the syntax though.


UPDATE Assets_LineFeatures
SET Assets_LineFeatures.COMMENTS = "Unable To Inspect Conduit"
LEFT JOIN [UnmatchedConduits] ON Assets_LineFeatures.SYSASSET =
[UnmatchedConduits].SYSASSET
WHERE Assets_LinesFeatures.AssetName LIKE 'C*';
jamesfreddyc said:
I am attempting to run an UPDATE on the Assets_LineFeatures TABLE. This is
conditional based upon unmatched records in the INSPECTIONS Table. Here is
what I have so far, of course it doesn't work, but any ideas to help move me
in the right direction are much appreciated.

j


UPDATE Assets_LineFeatures
SET Assets_LineFeatures.COMMENTS = "Unable To Inspect Conduit"
LEFT JOIN INSPECTIONS ON Assets_LineFeatures.SYSASSET = INSPECTIONS.SYSASSET
WHERE (((INSPECTIONS.SYSASSET) Is Null)) AND
(((Assets_LinesFeatures.AssetName) LIKE "C*"));
Click to expand...
Click to expand...
 
Your syntax is wrong for an ACCESS Update query. You might TRY the
following

UPDATE Assets_LineFeatures LEFT JOIN INSPECTIONS
ON Assets_LineFeatures.SYSASSET = INSPECTIONS.SYSASSET
SET Assets_LineFeatures.COMMENTS = "Unable To Inspect Conduit"
WHERE (((INSPECTIONS.SYSASSET) Is Null)) AND
(((Assets_LinesFeatures.AssetName) LIKE "C*"));

As always, back up your data before trying this. Just in case it does not
work as you expect it to.

Once it works as you want, then you can forget about backing up just before
you execute the query.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Thanks for the input, John...

Even though I figured out a workaround, I'd still like to determine a
solution to the original problem (UPDATE the table using the correct WHERE
clause). The solution you provided had some minor errors (not your fault
since my original SQL was in error). So, by taking your SQL and editing it,
I came up with the following:

UPDATE Assets_LineFeatures
INNER JOIN INSPECTIONS ON Assets_LineFeatures.SYSASSET = INSPECTIONS.SYSASSET
SET Assets_LineFeatures.COMMENT = "Unable To Inspect Conduit"
WHERE (((INSPECTIONS.SYSASSET) Is Null) AND
(([Assets_LinesFeatures].[Asset_Name]) Like "C*" And
(([Assets_LinesFeatures].[Asset_Name]) Not Like "CH*")));

The above SQL runs, but I do get an "Enter Parameter Value" Prompt on the
"Asset_Name" field, followed by "About to update 0 rows" message.

Any further comments are greatly appreciated.

j
 
Here is the correct SQL I have been trying to accomplish:

UPDATE Assets_LineFeatures
LEFT JOIN INSPECTIONS ON Assets_LineFeatures.SYSASSET = INSPECTIONS.SYSASSET
SET Assets_LineFeatures.COMMENT = "Unable To Inspect Conduit"
WHERE ((INSPECTIONS.SYSASSET) Is Null)
AND ((Assets_LineFeatures.ASSET_NAME) Not Like "CH*")
AND ((Assets_LineFeatures.ASSET_NAME) Like "C*");


Thanks again for your input!

j
 
Back
Top