423,818 Members | 2,250 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,818 IT Pros & Developers. It's quick & easy.

SQL update- where problem

P: n/a
I have a parent record with a child record.

The parent record contains structures and the child records contains
visits to those structures.

Each table has a field named "Complete", what I want is the structure
'complete' to be changed to true when ANY of the visits have been
completed.

I have tried to implement SQL to do this, but it gives me the error
"Syntax Error (Missing Operator)..." when I try and save the query. I
have searched through the forums and couldn't find anything relevent to
this.

My SQL code (the error is on the last line i think):

UPDATE 4ptTable, 5vTable
SET 4ptTable.ptComplete = -1
WHERE 4ptTable.ptKey = 5vTable.vptKey And 5vTable.vComplete = -1

Where 4ptTable is the parent table (structures), 5vTable is the child
table (visits), ptComplete is the field I wish to update (with the
value -1 [true]), ptkey is the primary key and vptKey is the foriegn
keym vComplete is whether the visit is complete.

? Am I right in thinking I need the primary key to be equal to the
foreign key - otherwise it will update all records in the recordset.
? I may also need to change this so that the parent record will change
only when ALL the child records are completed - any ideas.

I'm hoping that it is just my basic SQL code that is wrong...
grr, why can't SQL be as intinctive as VBA!

Cheers,
James Hallam

Aug 1 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
You could probably do it by using a subquery and including the square
brackets around the names that start with a number:

UPDATE [4ptTable] SET [4ptTable].ptComplete = True
WHERE ([4ptTable].ptComplete = False)
AND EXISTS (SELECT vptKey FROM [5vTable]
WHERE ([5vTable].vptKey = [4ptTable.ptKey])
AND ([5vTable].vComplete = True));

However, I question whether the ptComplete field is justified.
If it should *always* be True if any related record is True,
then it is dependent data, and should not be stored.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"James Hallam" <ja******@googlemail.comwrote in message
news:11**********************@m79g2000cwm.googlegr oups.com...
>I have a parent record with a child record.

The parent record contains structures and the child records contains
visits to those structures.

Each table has a field named "Complete", what I want is the structure
'complete' to be changed to true when ANY of the visits have been
completed.

I have tried to implement SQL to do this, but it gives me the error
"Syntax Error (Missing Operator)..." when I try and save the query. I
have searched through the forums and couldn't find anything relevent to
this.

My SQL code (the error is on the last line i think):

UPDATE 4ptTable, 5vTable
SET 4ptTable.ptComplete = -1
WHERE 4ptTable.ptKey = 5vTable.vptKey And 5vTable.vComplete = -1

Where 4ptTable is the parent table (structures), 5vTable is the child
table (visits), ptComplete is the field I wish to update (with the
value -1 [true]), ptkey is the primary key and vptKey is the foriegn
keym vComplete is whether the visit is complete.

? Am I right in thinking I need the primary key to be equal to the
foreign key - otherwise it will update all records in the recordset.
? I may also need to change this so that the parent record will change
only when ALL the child records are completed - any ideas.

I'm hoping that it is just my basic SQL code that is wrong...
grr, why can't SQL be as intinctive as VBA!

Cheers,
James Hallam

Aug 1 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.