update query using a select statement

[matpj]

hi folks,

I have a select query that uses two instances of the same table, that returns about 42 records.
One of the fields returned by the query is TEXT_VALUE.

I need to update all instances of TEXT_VALUE that are returned by the query.

I have tried various methods, but I can't seem to get it to work.
does anyone have any ideas.


for what its worth, my select statement is:

SELECT MSP2003_MSP_TEXT_FIELDS.PROJ_ID, MSP2003_MSP_TEXT_FIELDS.TEXT_FIELD_ID, MSP2003_MSP_TEXT_FIELDS.TEXT_VALUE, MSP2003_MSP_TEXT_FIELDS_1.TEXT_FIELD_ID, MSP2003_MSP_TEXT_FIELDS_1.TEXT_VALUE
FROM MSP2003_MSP_TEXT_FIELDS INNER JOIN MSP2003_MSP_TEXT_FIELDS AS MSP2003_MSP_TEXT_FIELDS_1 ON (MSP2003_MSP_TEXT_FIELDS.TEXT_REF_UID = MSP2003_MSP_TEXT_FIELDS_1.TEXT_REF_UID) AND (MSP2003_MSP_TEXT_FIELDS.PROJ_ID = MSP2003_MSP_TEXT_FIELDS_1.PROJ_ID)
WHERE (((MSP2003_MSP_TEXT_FIELDS.TEXT_REF_UID)=0) AND ((MSP2003_MSP_TEXT_FIELDS.TEXT_FIELD_ID)=188743999) AND ((MSP2003_MSP_TEXT_FIELDS.TEXT_VALUE)='R&D') AND ((MSP2003_MSP_TEXT_FIELDS_1.TEXT_FIELD_ID)=188743731) AND (Not (MSP2003_MSP_TEXT_FIELDS_1.TEXT_VALUE)="DST01UK"));

 

[JonFer]

Are you going to update the field in the first instance of the table? Is the update value independent of the data in the second instance?

If the answer is Yes to both questions, then you can modify your select to be an update of the table:

  Update table1
  Set field1="new value"
  Where Exists (Select * from table1 as s 
                Where s.text_ref_uid=table1.text_ref_uid And
                     ...
                )

 

[matpj]

The value that needs updating is in the first instance of the table, but is dependant on the query I pasted above - so I guess it is dependant on the second instance having text_value NOT = "DST01UK"

any other ideas?

cheers,
Matt

 

[JonFer]

The text_value NOT="DST01UK" is a criteria to know which records to update but if you are not using any data from the second instance to determine the update value, you are still okay.