Update Queries

[cscott]

I'm trying to make an update query that will update one set of fields WHERE X=Yes and another set WHERE Y=Yes. I'm using the code like "UPDATE SET Tasks.Main=-1 WHERE TASKS.ALLTASKS = Yes;

Can I add another set of updates with another condition something like:
"UPDATE SET Tasks.Main=-1 WHERE TASKS.ALLTASKS = Yes OR UPDATE SET Tasks.OtherMain=-1 Where TASKS.OTHERTASKS=Yes?"

It would seem possible, but I can't seem to get the syntax right if it can be done.

Thanks for any ideas.

 

[BNPMike]

I think you can only have one selection criterion as the database will first go and collect the records before it starts to process them. Thus your where will have to be for (x=yes or y=yes). You can however put an iif in the update values for each of the fields you want to update, setting main to 1 if say alltasks is yes otherwise leaving the existing value (ie setting it to itself), and similarly for othertasks.

 

[tlbroadbent]

You could do something like this. This query will update both columns in one pass. If the criteria for a column update is not met, the query updates the column to the current value.

Update TableName
Set Tasks.Main=IIF(TASKS.ALLTASKS=Yes,-1,Tasks.Main),
Tasks.OtherMain=IIF(TASKS.OTHERTASKS=Yes,-1,Tasks.OtherMain)
Where TASKS.ALLTASKS=Yes
Or TASKS.OTHERTASKS=Yes Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.