By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,491 Members | 879 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,491 IT Pros & Developers. It's quick & easy.

problem with Update query that has to fetch the values from another table

P: n/a
san
Hi,

I have the following update statement that updates one table by the
values from the other table which seems have some problem with the
syntax.

UPDATE tblResolve SET tblResolve.txtWhatIssue = tblIssues.ShortDesc,
tblResolve.txtCustomerDetails = tblIssues.CustomerDetails,
tblResolve.blnCustomerLost = tblIssues.CustomerLost,
tblResolve.txtActualCost = tblIssues.ActualCost, tblResolve.txtFUMLoss
= tblIssues.FUMLoss, tblResolve.txtActionTakenToResolve =
tblIssues.ActionTakenToResolve, tblResolve.txtSuggestedResolution =
tblIssues.SuggestedResolution
FROM tblResolve
INNER JOIN tblIssues ON tblResolve.intissuecode = tblIssues.IssueCode
WHERE tblResolve.intissuecode = forms!frmEnterIssues!IssueCode
;

When I run this query i get the following error:
Syntax error (missing operator) in the query expression
'tblIssues.SuggestedResolution
FROM tblResolve
INNER JOIN tblIssues ON tblResolve.intissuecode = tblIssues.IssueCode'

Can any one help me in getting this resolved.

Thanks in advance,
San.

Jan 17 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Your Inner Join needs to be moved up to follow the Update statement.
Something like:

UPDATE Table1 INNER JOIN Table2 ON Table1.lngPrimaryKey =
Table2.lngPrimaryKey SET Table1.strField = Table2.strField,
Table1.lngField = Table2.lngField, Table1.blnField = Table2.blnField,
Table1.dtmField = Table2.dtmField;

Sounds like your storing a lot of the same or similar data in two
seperate tables, which is a big relational database no-no. You may just
want to maintain the Issues table till the issues are resolved then
post them to the Resolve table using an Append Query to avoid data
duplication in the interim. That SQL statement would be something like
this:

INSERT INTO Table1 ( lngPrimayKey, strField, lngField, blnField,
dtmField ) SELECT Table2.lngPrimaryKey, Table2.strField,
Table2.lngField, Table2.blnField, Table2.dtmField FROM Table2;

Jan 18 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.