|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Case statement questionIs it possible to update a specific field based on the case statement? Something like this, for example: UPDATE Person SET CASE WHEN HB = '1' THEN HasBrother = '1' WHEN HS = '1' THEN HasSister = '1' END FROM <Some other table> ...... I know one way is to specify both fields and have CASE statement for both of them, something like: UPDATE Person SET HasBrother = CASE WHEN HB = '1' THEN '1' ELSE HasBrother END HasSister = CASE WHEN HB = '1' THEN '1' ELSE HasSister END FROM <Some other table> ....... But I am curious if first examle was possible. Thanks Goran Djuranovic Vidim po imenu da ces me razumeti i ako ti napisem na srpskom. Mozes da
napises nesto kao: UPDATE Person SET HasBrother = CASE HB = '1' THEN '1' ELSE HasBrother END, HasSister = CASE HS = '1' THEN '1' ELSE HasSister END FROM ... ... Tako ce ti promeniti polje u 1 ako je zadovoljen uslov, a inace ce ostati isto. Nije bas to sto si trazio, ali zavrsava posao. Pozdrav, Ivan Goran Djuranovic wrote: Show quote > Hi All, > Is it possible to update a specific field based on the case statement? > Something like this, for example: > > UPDATE Person SET > CASE > WHEN HB = '1' THEN HasBrother = '1' > WHEN HS = '1' THEN HasSister = '1' > END > FROM <Some other table> > ..... > > > I know one way is to specify both fields and have CASE statement for both of them, something like: > UPDATE Person SET > HasBrother = CASE > WHEN HB = '1' THEN '1' > ELSE HasBrother > END > HasSister = CASE > WHEN HB = '1' THEN '1' > ELSE HasSister > END > FROM <Some other table> > ...... > > But I am curious if first examle was possible. > > Thanks > Goran Djuranovic > > ------=_NextPart_000_0006_01C6ACAC.E6750960 > Content-Type: text/html; charset=iso-8859-1 > Content-Transfer-Encoding: quoted-printable > X-Google-AttachSize: 3247 > > <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> > <HTML><HEAD> > <META http-equiv=Content-Type content="text/html; charset=iso-8859-1"> > <META content="MSHTML 6.00.2900.2912" name=GENERATOR> > <STYLE></STYLE> > </HEAD> > <BODY bgColor=#ffffff> > <DIV><FONT face=Arial size=2>Hi All,</FONT></DIV> > <DIV><FONT face=Arial size=2>Is it possible to update a specific field based on > the case statement?</FONT></DIV> > <DIV><FONT face=Arial size=2>Something like this, for example:</FONT></DIV> > <DIV><FONT face=Arial size=2></FONT> </DIV> > <DIV><FONT face=Arial size=2>UPDATE Person SET</FONT></DIV> > <DIV><FONT face=Arial size=2>CASE</FONT></DIV> > <DIV><FONT face=Arial size=2> WHEN HB = '1' > THEN HasBrother = '1'</FONT></DIV> > <DIV><FONT face=Arial size=2> WHEN HS = '1' > THEN HasSister = '1'</FONT></DIV> > <DIV><FONT face=Arial size=2>END</FONT></DIV> > <DIV><FONT face=Arial size=2>FROM <Some other table></FONT></DIV> > <DIV><FONT face=Arial size=2>.....</FONT></DIV> > <DIV><FONT face=Arial size=2></FONT> </DIV> > <DIV><FONT face=Arial size=2></FONT> </DIV> > <DIV><FONT face=Arial size=2>I know one way is to specify both fields and have > CASE statement for both of them, something like:</FONT></DIV> > <DIV><FONT face=Arial size=2> > <DIV><FONT face=Arial size=2>UPDATE Person SET</FONT></DIV> > <DIV>HasBrother = <FONT face=Arial size=2>CASE</FONT> > <DIV><FONT face=Arial size=2> > WHEN > HB = '1' THEN '1'</FONT></DIV> > <DIV><FONT face=Arial size=2> > ELSE > HasBrother</FONT></DIV> > <DIV><FONT face=Arial > size=2> END</FONT></DIV> > <DIV>HasSister = <FONT face=Arial size=2>CASE</FONT> > <DIV><FONT face=Arial size=2> > WHEN > HB = '1' THEN '1'</FONT></DIV> > <DIV><FONT face=Arial > size=2> > ELSE HasSister</FONT></DIV> > <DIV><FONT face=Arial > size=2> END</FONT></DIV></DIV></DIV> > <DIV><FONT face=Arial size=2>FROM <Some other > table></FONT></DIV></FONT></DIV> > <DIV><FONT face=Arial size=2>......</FONT></DIV> > <DIV><FONT face=Arial size=2></FONT> </DIV> > <DIV><FONT face=Arial size=2>But I am curious if first examle was > possible.</FONT></DIV> > <DIV><FONT face=Arial size=2></FONT> </DIV> > <DIV><FONT face=Arial size=2>Thanks</FONT></DIV> > <DIV><FONT face=Arial size=2>Goran Djuranovic</FONT></DIV> > <DIV><FONT face=Arial size=2></FONT> </DIV></BODY></HTML> > > ------=_NextPart_000_0006_01C6ACAC.E6750960-- Hvala Ivane. Srpski odlicno pricam i jos bolje razumijem :-).
Ako procitas ponovo moje pitanje, vidjeces da sam tvoju soluciju vec naveo u istom, i da me interesuje da li ista solucija moze da se napise u nekom drugom smislu. Mada, cisto sumnjam. Jos jednom hvala. Goran Show quote "ivan" <ivanmilo***@gmail.com> wrote in message news:1153491208.641745.74970@75g2000cwc.googlegroups.com... > Vidim po imenu da ces me razumeti i ako ti napisem na srpskom. Mozes da > napises nesto kao: > > UPDATE Person > SET HasBrother = CASE HB = '1' THEN '1' ELSE HasBrother END, > HasSister = CASE HS = '1' THEN '1' ELSE HasSister END > FROM ... ... > > Tako ce ti promeniti polje u 1 ako je zadovoljen uslov, a inace ce > ostati isto. Nije bas to sto si trazio, ali zavrsava posao. > > Pozdrav, > Ivan > > Goran Djuranovic wrote: >> Hi All, >> Is it possible to update a specific field based on the case statement? >> Something like this, for example: >> >> UPDATE Person SET >> CASE >> WHEN HB = '1' THEN HasBrother = '1' >> WHEN HS = '1' THEN HasSister = '1' >> END >> FROM <Some other table> >> ..... >> >> >> I know one way is to specify both fields and have CASE statement for both >> of them, something like: >> UPDATE Person SET >> HasBrother = CASE >> WHEN HB = '1' THEN '1' >> ELSE HasBrother >> END >> HasSister = CASE >> WHEN HB = '1' THEN '1' >> ELSE HasSister >> END >> FROM <Some other table> >> ...... >> >> But I am curious if first examle was possible. >> >> Thanks >> Goran Djuranovic >> >> ------=_NextPart_000_0006_01C6ACAC.E6750960 >> Content-Type: text/html; charset=iso-8859-1 >> Content-Transfer-Encoding: quoted-printable >> X-Google-AttachSize: 3247 >> >> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> >> <HTML><HEAD> >> <META http-equiv=Content-Type content="text/html; charset=iso-8859-1"> >> <META content="MSHTML 6.00.2900.2912" name=GENERATOR> >> <STYLE></STYLE> >> </HEAD> >> <BODY bgColor=#ffffff> >> <DIV><FONT face=Arial size=2>Hi All,</FONT></DIV> >> <DIV><FONT face=Arial size=2>Is it possible to update a specific field >> based on >> the case statement?</FONT></DIV> >> <DIV><FONT face=Arial size=2>Something like this, for >> example:</FONT></DIV> >> <DIV><FONT face=Arial size=2></FONT> </DIV> >> <DIV><FONT face=Arial size=2>UPDATE Person SET</FONT></DIV> >> <DIV><FONT face=Arial size=2>CASE</FONT></DIV> >> <DIV><FONT face=Arial size=2> WHEN HB = '1' >> THEN HasBrother = '1'</FONT></DIV> >> <DIV><FONT face=Arial size=2> WHEN HS = '1' >> THEN HasSister = '1'</FONT></DIV> >> <DIV><FONT face=Arial size=2>END</FONT></DIV> >> <DIV><FONT face=Arial size=2>FROM <Some other table></FONT></DIV> >> <DIV><FONT face=Arial size=2>.....</FONT></DIV> >> <DIV><FONT face=Arial size=2></FONT> </DIV> >> <DIV><FONT face=Arial size=2></FONT> </DIV> >> <DIV><FONT face=Arial size=2>I know one way is to specify both fields and >> have >> CASE statement for both of them, something like:</FONT></DIV> >> <DIV><FONT face=Arial size=2> >> <DIV><FONT face=Arial size=2>UPDATE Person SET</FONT></DIV> >> <DIV>HasBrother = <FONT face=Arial size=2>CASE</FONT> >> <DIV><FONT face=Arial size=2> >> >> WHEN >> HB = '1' THEN '1'</FONT></DIV> >> <DIV><FONT face=Arial size=2> >> >> ELSE >> HasBrother</FONT></DIV> >> <DIV><FONT face=Arial >> size=2> END</FONT></DIV> >> <DIV>HasSister = <FONT face=Arial size=2>CASE</FONT> >> <DIV><FONT face=Arial size=2> >> >> WHEN >> HB = '1' THEN '1'</FONT></DIV> >> <DIV><FONT face=Arial >> size=2> >> ELSE HasSister</FONT></DIV> >> <DIV><FONT face=Arial >> size=2> END</FONT></DIV></DIV></DIV> >> <DIV><FONT face=Arial size=2>FROM <Some other >> table></FONT></DIV></FONT></DIV> >> <DIV><FONT face=Arial size=2>......</FONT></DIV> >> <DIV><FONT face=Arial size=2></FONT> </DIV> >> <DIV><FONT face=Arial size=2>But I am curious if first examle was >> possible.</FONT></DIV> >> <DIV><FONT face=Arial size=2></FONT> </DIV> >> <DIV><FONT face=Arial size=2>Thanks</FONT></DIV> >> <DIV><FONT face=Arial size=2>Goran Djuranovic</FONT></DIV> >> <DIV><FONT face=Arial size=2></FONT> </DIV></BODY></HTML> >> >> ------=_NextPart_000_0006_01C6ACAC.E6750960-- > CASE is an expression and not a statement. It must return a scalar
value. Celko,
If the CASE function cannot be used for the solution to my previous question, is there any other way of doing it. Of course, other than dynamically building the SQL statement. TIA Goran Show quote "--CELKO--" <jcelko***@earthlink.net> wrote in message news:1153498545.345423.68640@75g2000cwc.googlegroups.com... > CASE is an expression and not a statement. It must return a scalar > value. > >> If the CASE function cannot be used for the solution to my previous question, is there any other way of doing it. << Better schema design and encodings! SQL is a data language and not aprocedural language. CREATE TABLE Persons ( .. sibling_status DEFAULT 0 INTEGER NOT NULL CHECK (sibling_status IN (0, 1, 2, 3)), ...); 0= no siblings 1= has brothers 2= has sisters 3= has brothers and sisters >> Of course, other than dynamically building the SQL statement. << Writing dynamic SQL is like playhing a video game where you have noidea what will happen next, so you just start shooting at the problem at run time :) Hello Goran,
I agree with Hugo that it is not possible for the first example and the workaround you have provided seems to be the best option. If you still have concerns on this, please feel free to let's know. Best Regards, Peter Yang MCSE2000/2003, MCSA, MCDBA Microsoft Online Community Support ================================================== Get notification to my posts through email? Please refer to http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif ications <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>. Note: The MSDN Managed Newsgroup support offering is for non-urgent issues where an initial response from the community or a Microsoft Support Engineer within 1 business day is acceptable. Please note that each follow up response may take approximately 2 business days as the support professional working with you may need further investigation to reach the most efficient resolution. The offering is not appropriate for situations that require urgent, real-time or phone-based interactions or complex project analysis and dump analysis issues. Issues of this nature are best handled working with a dedicated Microsoft Support Engineer by contacting Microsoft Customer Support Services (CSS) at <http://msdn.microsoft.com/subscriptions/support/default.aspx>. ================================================== This posting is provided "AS IS" with no warranties, and confers no rights. On Fri, 21 Jul 2006 10:03:22 -0400, Goran Djuranovic wrote:
>Hi All, Hi Goran,>Is it possible to update a specific field based on the case statement? >Something like this, for example: > >UPDATE Person SET >CASE > WHEN HB = '1' THEN HasBrother = '1' > WHEN HS = '1' THEN HasSister = '1' >END >FROM <Some other table> >..... (snip) I have no idea what Ivan has been writing to you (it looks like a Slavic language, but there are many of those, and I know none of them). But if his answer is that this is not possible, then he is correct. You'll have to use the method that yoou've already found yourself: >UPDATE Person SET >HasBrother = CASE > WHEN HB = '1' THEN '1' > ELSE HasBrother > END >HasSister = CASE > WHEN HB = '1' THEN '1' > ELSE HasSister > END >FROM <Some other table> >...... -- Hugo Kornelis, SQL Server MVP  |
|||||||||||||||||||||||
Other interesting topics