Update Query Assistance 1

[Bill4tektips]

I am trying to set up an Update Query to update records in tblBMS_Docs_From. I need it to update the field "Status" from the master data which is in a table named Raw_Data.
The Status field has all set to "Published" but if one is changed to "Withdrawn" then I need tblBMS_Docs_From to update.
My sql code at the moment is:
UPDATE Raw_Data INNER JOIN tblBMS_Docs_From ON Raw_Data.BMSReference = tblBMS_Docs_From.BMSReferenceFrom SET tblBMS_Docs_From.Status = "Withdrawn"
WHERE (((tblBMS_Docs_From.Status)="Withdrawn"));
Any help would be appreciated

 

[r937]

this part --

SET tblBMS_Docs_From.Status = "Withdrawn"
WHERE (((tblBMS_Docs_From.Status)= "Withdrawn"))

seems as though is not going to accomplish anything, because it says to set the column equal to what it already is

and from your description, i can't figure out what you actually want

maybe it is this --

UPDATE Raw_Data
INNER
JOIN tblBMS_Docs_From
ON tblBMS_Docs_From.BMSReferenceFrom = Raw_Data.BMSReference
SET tblBMS_Docs_From.Status = [red]Raw_Data.Status[/red]
WHERE tblBMS_Docs_From.Status = 'Withdrawn'

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[PHV]

Something like this ?

UPDATE tblBMS_Docs_From INNER JOIN Raw_Data ON Raw_Data.BMSReference = tblBMS_Docs_From.BMSReferenceFrom
SET tblBMS_Docs_From.Status = Raw_Data.Status
WHERE tblBMS_Docs_From.Status='Withdrawn'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Bill4tektips]

PHV, Thank you once again, it's working perfect

 

[r937]

nice one, PHV, your solution looks eerily similar to mine, although mine has much better formatting

;-)

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon