vary the select statement in a from clause

hey all,

i have a sql like so:

update mytable
set mycol=val
from
(
if (cond)
begin
select myval from t1
end
else
begin
select * from t2
end
) tbl

is there a way to make the if(cond) work? probably doesn't look like a good 
idea anyway?

thanks,
rodchar
0
Utf
4/15/2010 7:51:02 PM
sqlserver.programming 1873 articles. 0 followers. Follow

3 Replies
683 Views

Similar Articles

[PageSpeed] 3

It will be best to simply use IF statement and run two separate queries based on the conditions:

IF (cond)
   UPDATE mytable ... FROM t1
ELSE
   UPDATE mytable ... FROM t2

-- 
Plamen Ratchev
http://www.SQLStudio.com
0
Plamen
4/15/2010 10:05:55 PM
UPDATE Fooobar
  SET x
     = CASE WHEN <search condition>
            THEN <scalar expression #1>
            ELSE <scalar expression #2> END;

0
CELKO
4/16/2010 1:42:50 PM
thanks for the help,
rod.

"Plamen Ratchev" wrote:

> It will be best to simply use IF statement and run two separate queries based on the conditions:
> 
> IF (cond)
>    UPDATE mytable ... FROM t1
> ELSE
>    UPDATE mytable ... FROM t2
> 
> -- 
> Plamen Ratchev
> http://www.SQLStudio.com
> .
> 
0
Utf
4/16/2010 1:52:01 PM
Reply:

Similar Artilces:

Groups of select people to send mail to, can this be done?
I need to know how to group together certain contacts that may always get the same message. My husband is coaching flag football and I want to group all the players in one folder or such in order to find them all quickly. Can this be done and how? Thank you, Barb See Address Book Help | Create a group of contacts To send a message to everyone in the Group, please the Group name in the TO field. NB: Most ISPs won't allow sending one (1) message to more than 50 (sometimes fewer) recipients. Scroggygal wrote: > I need to know how to group together certain contac...

IIf Statement in Query Criteria 12-11-09
I have the following code in a query: SELECT qryPointsWithDropOffDates.EMPLOYEEID, qryPointsWithDropOffDates.SSN, qryPointsWithDropOffDates.NAME, qryDisciplineNoticesReprint.LASTINCIDENTDATE, qryPointsWithDropOffDates.INCIDENTDATE, qryPointsWithDropOffDates.TYPESHORT, qryPointsWithDropOffDates.POINTVALUE FROM qryDisciplineNoticesReprint INNER JOIN qryPointsWithDropOffDates ON qryDisciplineNoticesReprint.SSN=qryPointsWithDropOffDates.SSN WHERE (((qryPointsWithDropOffDates.INCIDENTDATE) Between [qryDisciplineNoticesReprint.CALCDATE] And [qryDisciplineNoticesReprint.LASTINCIDENTD...

how to select next blank entry line via code
I have a vb add-in that adds items to the transaction screen from a userform, and it works great, except that is doesn't place the cursor in a new blank line, ready for the next entry on the transaction. is there a way do this via qsrules in code? thanks, kevin I found this when I was searching this forum for "code" sounds like a similar problem Your add-in probably works, but it is the grid that is problematic. It's in memory already but somehow the grid does not update automatically. If you scroll down to the very bottom of the grid by pressing the down key, and t...

Combine multiple if statements in one text box
On a form I have a text box that I want to populate with a calculated number from another form. The number I need to see depends on 2 check boxes "RevisedGrant" and "Waive10Pct" Can anyone tell me what is wrong with the code. I have had it a number of diff way. Enclosing the iff in () etc. This problem is taking me much too long to figure out. Please Help!! =IIf([RevisedGrant]=True And [Waive10Pct]=True, Forms!DataEntryFrm!ItemSubFrm![Total$ofItemsRequestedSubfrm].Form![Revised Less 10Pct]), elseIf([RevisedGrant]=True And [Waive10Pct]=False, Forms!DataEntryFrm!I...

SELECT Issue
I'm having trouble with a select statement and wondering if some experts could help:) I have a VARCHAR field in a table called IDFormula which contains an INT PRIMARY KEY value from a column in the same table(with brackets around it). For example if the table was called Unit, and one of the primary key values was 341, then somewhere in the IDFormula column there may be a [341] in it as such: [341]+1. I need to select any row from the table which has a primary key that can be found in an IDFormula column. I hope this is clear. Here is some example code I threw together. DECLAR...

Convert text to number in IIF statement
Hi, I have a query where I am using an iif statement so that my field we either equal text or number depending on the value of another field. Here is my iif statement Portfolio1: IIf([qry_mhfsac4dPassThrough.SecId]="60934n583","FPA",IIf([qry_mhfsac4dPassThrough.SecId]="825252646","Aim",CInt([portfolio]))) I am basically saying that if the SecID of a security is x then"FPA" or "AIM" otherwise I want to see the portfolio number. However, whether I use the formula cint() or val() or a couple others that I have tried, this field still c...

Formula to format selective part in a text
Hi all, In MS Excel, in th below formula what additional formula do I add to make selective text in different formats (ex. bold)? Ex: ="Mission 1 Start Date: "&text(A1,"DD-MMM-YYYY") In the result I want only the date part of the result text to be in bold letters. Thanks in advance. No can do in a FORMULA. You must convert to a string -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Gladiator" <Gladiator@discussions.microsoft.com> wrote in message news:7E825FD9-C276-43CF-BFB4-A7F3D6FB932E@microsoft.com.....

Pre-select visible sheets in listbox
Hi All, I have a user form which has a listbox which lists all the sheets in the workbook. It allows the user to pick from the list which sheets they would like to see, and hide the unselected ones. What I would like the listbox to do when the form opens, is to pre- select the sheets that are already visible, so that the user can just modify the selction instead of starting from scratch. Any help that people can give would be appreciated cheers Paul As a user, I would think that it would make more sense to ask me to select the sheets to hide--but that doesn't change ...

change display font to actual selected font
My excel worksheet is using the font comic sans ms although in the Normal page layout, the font used is Arial. In the format toolbar, comic sans ms is selected and when the sheet is printed, it appears correctly. What's your zoom level? If you change the zoom factor to 100% does it look ok? If yes, then maybe this post by Debra Dalgleish will help: You can change a registry setting to prevent this from happening (make a backup copy of the registry first): 1. From the Start button, choose Run 2. Type regedit then click OK 3. Click the + sign to the left of HKEY_CURRENT_USER 4. Open ...

Code to select active workbook worksheet(s) in Excel 2007
Code to select active workbook worksheet(s) in Excel 2007 Need help I have limited experience in VBA and have been looking online and in the Excel 2007 books I have but can not seem to locate/grasp what I need. I require: Module to run automatically when workbook is started. Dropdown to provide selections: (1) workbook book to be used normally or (2) a dropdown that will allow opening, editing, and closing of any/all of active workbook worksheets. Option (2) to remain available while workbook is open. Workbook will have up to 30 sheets, maybe more. Thanks in advance ...

Multiple IF statement
I am formatting cell D47 and wish the following IF conditions:- D47 to read 2.00 unless D6>3175 but<3800 then to read 2.50, if D6 >3800 but<4500 then read 3.00, if D6>4500 then read 3.5 How should this be written in the cell. Many thanks in advance for your help. Dave Hi Dave =IF(AND(D6>3175,D6<=3800),2.5, IF(AND(D6>3800,D6<=4500),3.0, IF(D6>4500,3.5,2.0))) I assumed you wanted the upper level included as part of the range in each case, so I made it <=3800 etc. Otherwise, values of 3800 and 4500 would be missed altogether. -- Regards Roger Govier &q...

flakey "Select All" behaviour on list boxes
Win XP, Office 2003I am changing the contents of a table driving a list box and thenselecting every item in the list box. (code shown below)I am getting very flakey results with the highlighting - particularlywhen going from more items, to less - highlighting where there are norecords, the "fewer case" records are not highlighted, etc.Tried obvious things like removing all highlighting first, removingthe row source of the list box and then reapplying it.Does anyone know how to do a a really cleansing reset on a list box ?Many thanks in advanceTony Epton BuildTmpTaskDetail ...

Outlook "automatically select entire word" problem.
I am working with the journal in Outlook 2003 on an XP machine. When selecting text the entire word is automatically selected (more specifically the period at the end of the previous sentence). I want to turn this feature off. When I inspect the "When selecting text, automatically select entire word" option (Tools|Options, Other tab, Advanced Options) it is greyed out but unchecked. Does anyone know 1) why this is greyed out, 2) can I really beleive it is unchecked, and 3) is there any way to solve my problem? Thanks. Check the same location in Word - Tools->Options. --� M...

Selecting individual records for a report
I have a table with 200+ contacts. I want to generate letters to 7-8 of them, but there is no common criteria upon which I can run a query. I'd like to create the letters, then when the report is run be able to select which records from the table receive the letter. No idea how to go about this (other than exporting to excel, deleting non-receivers, and doing a Word mailmerge). Suggestions? If there truly is no relationship, you could create a new table of records to report with a single field that is the primary key of you contacts table. Add or delete record...

Select Cell Range for Mail Merge Document
I'm using Word to pull information from an Excel spreadsheet that I extracted from a database... essentially, demographical information used to print up a bunch of labels for tax season. Step 1: Main Document Fine... no problems here. Step 2: Data Source I select Get Data>Open Data Source, and then go to the spreadsheet in question. After selecting this, I see Data: TW2003Label!Entire Spreadsheet How do I get it to select only the cells containing data and not the entire spreadsheet? Thanks in advance. ...

Sort in an IIf statement
To all: I am using Access 2003. I have a IIf statement in a query that looks like this: Owner: (IIf([LLCName]="Smith Properties","SP#" & (IIf([LLCNumber]>0,[LLCNumber],"XX")),"SD")) The LLCNumber field is stored as a number. The LLCName is stored as text. It returns things like: SP#101 SP#89 SP#45 SD SP#XX Right now, it is returning them with all of the 100s before the 90's as it recognizes 1 is before 9, etc. I would like to sort these by the LLCNumber. Is that possible? Thanks in advance for your help! At the bottom of the SQL sta...

brackets in a sql statement
have a field name that is separated by a spacethat i need to reference in a sql statement. i know i can put brackets around the field name, but the sql statement also has brackets. how can i code? i have tried putting quotes, extra brackets, parens around the field name but nothing works. here is the sql statement sqlSelect = "SELECT AllClaims.* FROM[SELECT Src.*, Nz(P2P.ProductionCode,'MISSING CODE') AS ProdCode FROM cntrlD_ITP" & strthisgrpid & " AS Src LEFT JOIN PendToProduction AS P2P ON Src.[Pend code] = P2P.PendCode]. AS AllClaims LEFT J...

PO Number on the statement form
I would like to add the PO number on the statement form, how possible is this? ...

Easier ways to selectively open a .wks as read-only...
Excel has a way via its internal file-open mechanism any worksheet as read only. This is great if you want to prevent damage to a sheet (yours or god forbid somebody elses). If you're a pathological user of Autosave like I am (to wit my related post on Autosave), you'll know how good an idea this can be. I know there are some freeware alternatives to Autosave (Autosafe, for example) but as a corporate user, I can't really use/install freeware. So I'm wondering: is there an *EASIER* way to allow me to open a .wks as read-only? Going through Excel's open command is kind ...

MS Excel IF Statement w/pictures?
is there a way to write a statement in Excel (like an IF Statement), that would display a specific picture if a certain #/word/etc is typed in a cell? For example in A1 if I typed in ball, it would automatically bring up a pic I have saved of a ball in B1, whereas if I typed dog, it would put a dog in B1, etc..? If you know how to do this can you PLEASE email me at ratedr1@aol.com as its hard for me to check the Ngs at work... Thanks in advance Scott Try this site: http://www.mcgimpsey.com/excel/lookuppics.html HTH "Scott" wrote: > is there a way to write a statement in Exce...

VBA AutoFilter how to apply 2 ranges for selection to copy
Using XL 2003 & 97 Currently the code below works fine. It selects values equal to or greater than 20000 but less than 38999. Cells.AutoFilter Field:=3, Criteria1:=">=20000", Operator:=xlAnd, _ Criteria2:="<38999" Range("A1").Select ...... (copy paste routine) What is the most efficient syntax to convert the above code to handle: ">=20000 and =<35000" AND ">=35500 and <38999" TIA Dennis I think you've got trouble. First, autofilter can only have 2 criteria. And secondly, I do...

Option to disable Auto word select functionality?
I've switched over to using a version of Word as my default mail editor since I couldn't find a way to change this behavior directly in the Outlook settings so this is really more out of curiosity. Is it possible to modify the Outlook editor default behavior, such as Word Auto Selection (man this irritates me) to be off? What version of Outlook are you using? -- -Ben- Ben M. Schorr, MVP Roland Schorr & Tower http://www.rolandschorr.com http://www.officeforlawyers.com/outlook.htm Author: The Lawyer's Guide to Microsoft Outlook 2007: http://tinyurl.com/ol4la...

selecting 20 closest values from a list of 200 values
Hi there I have a list of 200 schools in a list and each of them has a numeric value. I want to be able to extract in groups of 20 the schools with the closest values to every single school on the list. Can anyone please help as it's driving me mad!!! -- Michele Just sort the list by scores. This will put schools with similar scores "close together". Then the top 20 will be the first group, the next 20 will be the second group, etc. -- Gary''s Student - gsnu200799 ...

Case statement vs Change/lookup table
Hello All Gurus, This is a problem that I have encountered and was wondering what would be a better approach to the solution I have a view that is pulling data from a table which is fed from an external application. Some of the data due to external situations is wrong/spelled wrong, in the past when this happened I updated another view with a complex set of case statements. This is fast and stable, however, it is for only three instances as they are the only mistakes and they happen always, aka the data is always spelled wrong for these three instances. Now there is a situation wher...

Sending Selected Cells to an Outlook Task or Email
I would love to be able to select a row (SHIFT+SPACE) and then click a button or use another key combo (i.e. CTRL+SHIFT+T) and send the selected cells (with formats) to the body of a new Outlook Task or Email. Is this possible? Thanks in advance for your help. Regards, magmike ...