Update query not working

[PatrickM]

I can't figure out how to update my table. I've tried using the following
script but looks like I'm trying to do something wrong. I get the error
"Operation must use an updateable query":

UPDATE [0106 treat srcedat gwsw 5] LEFT JOIN Main ON [0106 treat srcedat
gwsw 5].ID = Main.ID SET Main.Source_dataset_0106_gw_sw = "yes";

- '0106 treat srcedat gwsw 5' is a select query with sub-queries
- 'Main' is the table I'm trying to update

Where the ID number in '0106...' matches the ID number in 'Main' I want to
put 'yes' in the field in 'Main'. Any suggestions on how to actually do it?
Some relevant points:

- ID numbers occur only once in '0106...'
- ID numbers occur only once in 'Main'
- all the ID numbers in '0106...' are in 'Main', but not vice versa
- the records in '0106...) are actually a subset of the records in 'Main'

Thanks,
Patrick

 

[John W. Vinson]

I can't figure out how to update my table. I've tried using the following
script but looks like I'm trying to do something wrong. I get the error
"Operation must use an updateable query":

UPDATE [0106 treat srcedat gwsw 5] LEFT JOIN Main ON [0106 treat srcedat
gwsw 5].ID = Main.ID SET Main.Source_dataset_0106_gw_sw = "yes";

- '0106 treat srcedat gwsw 5' is a select query with sub-queries
- 'Main' is the table I'm trying to update

Where the ID number in '0106...' matches the ID number in 'Main' I want to
put 'yes' in the field in 'Main'. Any suggestions on how to actually do it?
Some relevant points:

- ID numbers occur only once in '0106...'
- ID numbers occur only once in 'Main'
- all the ID numbers in '0106...' are in 'Main', but not vice versa
- the records in '0106...) are actually a subset of the records in 'Main'

Thanks,
Patrick

Well, the left join isn't needed since you only want to update records that DO
have a match. It's quite likely that your 106 multitable query is not
updateable. Try

UPDATE Main
SET Source_Dataset_0106_gw_sw = "Yes"
WHERE ID IN
(SELECT ID FROM [0106 treat srcedat gwsw 5]);

 

[PatrickM]

Thanks John, that works great.

Patrick

I can't figure out how to update my table. I've tried using the following
script but looks like I'm trying to do something wrong. I get the error
"Operation must use an updateable query":

UPDATE [0106 treat srcedat gwsw 5] LEFT JOIN Main ON [0106 treat srcedat
gwsw 5].ID = Main.ID SET Main.Source_dataset_0106_gw_sw = "yes";

- '0106 treat srcedat gwsw 5' is a select query with sub-queries
- 'Main' is the table I'm trying to update

Where the ID number in '0106...' matches the ID number in 'Main' I want to
put 'yes' in the field in 'Main'. Any suggestions on how to actually do it?
Some relevant points:

- ID numbers occur only once in '0106...'
- ID numbers occur only once in 'Main'
- all the ID numbers in '0106...' are in 'Main', but not vice versa
- the records in '0106...) are actually a subset of the records in 'Main'

Thanks,
Patrick

Well, the left join isn't needed since you only want to update records that DO
have a match. It's quite likely that your 106 multitable query is not
updateable. Try

UPDATE Main
SET Source_Dataset_0106_gw_sw = "Yes"
WHERE ID IN
(SELECT ID FROM [0106 treat srcedat gwsw 5]);