Loading ...
Sorry, an error occurred while loading the content.
 

RE: [SQLQueriesNoCode] Re: Column value update based on FK relati onship

Expand Messages
  • Travis Truax
    Look at the CASE statement in the online help. ... From: mail_bala [mailto:mail_bala@yahoo.com] Sent: Thursday, April 17, 2008 3:06 PM To:
    Message 1 of 7 , Apr 18, 2008
      Look at the CASE statement in the online help.


      -----Original Message-----
      From: mail_bala [mailto:mail_bala@...]
      Sent: Thursday, April 17, 2008 3:06 PM
      To: SQLQueriesNoCode@yahoogroups.com
      Subject: [SQLQueriesNoCode] Re: Column value update based on FK
      relationship


      Can I do something along these lines:


      UPDATE TABLE1
      SET COLUMN11 = IF (CONDITION1) X ELSE Y ENDIF
      |
      COLUMN11 = IF (CONDITION2) A ELSE B ENDIF
      |
      COLUMN11 = IF (CONDITION3) C ENDIF
      |
      ...

      Basically setting one column value to a cumulative value (OR'd as
      above) where each value is driven by a separate condition. I was able
      to do them as separate UPDATE statements but this means the table
      joins (which are needed for the conditions) were there many times. Not
      sure if this causes those tables to be scanned over and over...?






      --- In SQLQueriesNoCode@yahoogroups.com, "John Warner" <john@...> wrote:
      >
      > You could use an update trigger to hold that logic. Not sure how you
      would
      > code it though. I would guess the trigger would need to be on
      CumulativeInt
      > but I'd have to play with that in a test database. Maybe someone else on
      > the list has some ideas. This is still T-SQL though the dialects have
      > drifted apart with SQL Server.
      >
      > John Warner
      >
      >
      >
      >
      > > -----Original Message-----
      > > From: SQLQueriesNoCode@yahoogroups.com
      > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of mail_bala
      > > Sent: Thursday, April 10, 2008 1:10 PM
      > > To: SQLQueriesNoCode@yahoogroups.com
      > > Subject: [SQLQueriesNoCode] Re: Column value update based on
      > > FK relationship
      > >
      > >
      > > Thanks John. It looks correct to me (will try it later today
      > > for my confirmation).
      > >
      > > One question: is there a way I can 'ignore' further updates
      > > to the same row in Table1 once I know the CumulativeInt has
      > > reached a certain value ? Using the example below, assuming
      > > Table2.IntValue can have only one of (32, 16), once I see a
      > > Table2.IntValue = 16 (or more correctly a non-32), I would
      > > like the value to be set to 16 (i.e. if atleast one
      > > Table2.IntValue is not 32 then Table1.CumulativeInt is 16; if
      > > all are 32 then 32). Something such as
      > >
      > > if (Table2.IntValue is ALL 32)
      > > Table1.CumulativeInt = 32
      > > else
      > > Table1.CumulativeInt = 16
      > >
      > >
      > > TIA
      > >
      > > /bala
      > >
      > > --- In SQLQueriesNoCode@yahoogroups.com, "John Warner"
      > > <john@> wrote:
      > > >
      > > > UPDATE Table1
      > > > SET Table1.CumulativeInt = (Table1.CumlativeInt & Table2.IntValue)
      > > > FROM Table1
      > > > INNER JOIN Table2 ON (Table1.ID = Table2.Table1Id)
      > > >
      > > > I don't Sybase so this could be wrong but it should be close to
      > > > correct. I'm not completely sure how Sybase identifies
      > > objects which
      > > > is where I would suspect errors if they occur.
      > > >
      > > > John Warner
      > > >
      > > >
      > > >
      > > >
      > > > > -----Original Message-----
      > > > > From: SQLQueriesNoCode@yahoogroups.com
      > > > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of mail_bala
      > > > > Sent: Wednesday, April 09, 2008 4:39 PM
      > > > > To: SQLQueriesNoCode@yahoogroups.com
      > > > > Subject: [SQLQueriesNoCode] Column value update based on FK
      > > > > relationship
      > > > >
      > > > >
      > > > > I have two tables Table1 and 2. Table2 has a FK relationship
      > > > > to 1 as shown below:
      > > > >
      > > > > Table1
      > > > > -------
      > > > > ID Col1 CumulativeInt
      > > > > 1 One 0
      > > > > 2 Two 0
      > > > >
      > > > >
      > > > >
      > > > > Table2
      > > > > --------
      > > > > ID Table1Id IntValue
      > > > > 1 1 16
      > > > > 2 1 32
      > > > > 3 2 16
      > > > >
      > > > >
      > > > > for each row in Table1, I want to update the "CumulativeInt"
      > > > > column such that the it is the bitwise AND of the column
      > > > > IntValuein Table2 (or some such other numeric operation).
      > > > >
      > > > > Can I do something like this: (it is incorrect syntax but
      > > > > this is what I'm trying to do)
      > > > >
      > > > > UPDATE Table1 SET CumulativeInt=CumulativeInt&t2.IntValue
      > > > > WHERE
      > > > > t2.Table1Id = t1.ID
      > > > >
      > > > > I'm using Sybase 10.
      > > > >
      > > > > TIA
      > > > >
      > > > >
      > > > >
      > > > > ------------------------------------
      > > > >
      > > > > Yahoo! Groups Links
      > > > >
      > > > >
      > > > >
      > > >
      > >
      > >
      > >
      > > ------------------------------------
      > >
      > > Yahoo! Groups Links
      > >
      > >
      > >
      >



      ------------------------------------

      Yahoo! Groups Links




      *** Email Confidentiality Notice ***
      This message is private and confidential. If you have received this message
      in error, please notify the sender and remove it from your system.
    • mail_bala
      As you can see, I am trying to set a single column to values based on condition. The column is a cumulative result of all conditions. I m not so sure how
      Message 2 of 7 , Apr 19, 2008
        As you can see, I am trying to set a single column to values based on
        condition. The column is a 'cumulative result' of all conditions. I'm
        not so sure how CASE will help here without having all permutations of
        all conditions the conditions such as:

        CASE
        WHEN (CONDITION1 & CONDITION2 & CONDITION3) THEN
        SET COLUMN11 = X|A|C
        WHEN (CONDITION1 & CONDITION2 & !CONDITION3) THEN
        SET COLUMN11 = X|A
        WHEN (CONDITION1 & !CONDITION2 & CONDITION3) THEN
        SET COLUMN11 = X|B|C
        ...
        END CASE;


        Am I missing something ?

        TIA


        --- In SQLQueriesNoCode@yahoogroups.com, Travis Truax
        <travis.truax@...> wrote:
        >
        > Look at the CASE statement in the online help.
        >
        >
        > -----Original Message-----
        > From: mail_bala [mailto:mail_bala@...]
        > Sent: Thursday, April 17, 2008 3:06 PM
        > To: SQLQueriesNoCode@yahoogroups.com
        > Subject: [SQLQueriesNoCode] Re: Column value update based on FK
        > relationship
        >
        >
        > Can I do something along these lines:
        >
        >
        > UPDATE TABLE1
        > SET COLUMN11 = IF (CONDITION1) X ELSE Y ENDIF
        > |
        > COLUMN11 = IF (CONDITION2) A ELSE B ENDIF
        > |
        > COLUMN11 = IF (CONDITION3) C ENDIF
        > |
        > ...
        >
        > Basically setting one column value to a cumulative value (OR'd as
        > above) where each value is driven by a separate condition. I was able
        > to do them as separate UPDATE statements but this means the table
        > joins (which are needed for the conditions) were there many times. Not
        > sure if this causes those tables to be scanned over and over...?
        >
        >
        >
        >
        >
        >
        > --- In SQLQueriesNoCode@yahoogroups.com, "John Warner" <john@> wrote:
        > >
        > > You could use an update trigger to hold that logic. Not sure how you
        > would
        > > code it though. I would guess the trigger would need to be on
        > CumulativeInt
        > > but I'd have to play with that in a test database. Maybe someone
        else on
        > > the list has some ideas. This is still T-SQL though the dialects have
        > > drifted apart with SQL Server.
        > >
        > > John Warner
        > >
        > >
        > >
        > >
        > > > -----Original Message-----
        > > > From: SQLQueriesNoCode@yahoogroups.com
        > > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of mail_bala
        > > > Sent: Thursday, April 10, 2008 1:10 PM
        > > > To: SQLQueriesNoCode@yahoogroups.com
        > > > Subject: [SQLQueriesNoCode] Re: Column value update based on
        > > > FK relationship
        > > >
        > > >
        > > > Thanks John. It looks correct to me (will try it later today
        > > > for my confirmation).
        > > >
        > > > One question: is there a way I can 'ignore' further updates
        > > > to the same row in Table1 once I know the CumulativeInt has
        > > > reached a certain value ? Using the example below, assuming
        > > > Table2.IntValue can have only one of (32, 16), once I see a
        > > > Table2.IntValue = 16 (or more correctly a non-32), I would
        > > > like the value to be set to 16 (i.e. if atleast one
        > > > Table2.IntValue is not 32 then Table1.CumulativeInt is 16; if
        > > > all are 32 then 32). Something such as
        > > >
        > > > if (Table2.IntValue is ALL 32)
        > > > Table1.CumulativeInt = 32
        > > > else
        > > > Table1.CumulativeInt = 16
        > > >
        > > >
        > > > TIA
        > > >
        > > > /bala
        > > >
        > > > --- In SQLQueriesNoCode@yahoogroups.com, "John Warner"
        > > > <john@> wrote:
        > > > >
        > > > > UPDATE Table1
        > > > > SET Table1.CumulativeInt = (Table1.CumlativeInt & Table2.IntValue)
        > > > > FROM Table1
        > > > > INNER JOIN Table2 ON (Table1.ID = Table2.Table1Id)
        > > > >
        > > > > I don't Sybase so this could be wrong but it should be close to
        > > > > correct. I'm not completely sure how Sybase identifies
        > > > objects which
        > > > > is where I would suspect errors if they occur.
        > > > >
        > > > > John Warner
        > > > >
        > > > >
        > > > >
        > > > >
        > > > > > -----Original Message-----
        > > > > > From: SQLQueriesNoCode@yahoogroups.com
        > > > > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of mail_bala
        > > > > > Sent: Wednesday, April 09, 2008 4:39 PM
        > > > > > To: SQLQueriesNoCode@yahoogroups.com
        > > > > > Subject: [SQLQueriesNoCode] Column value update based on FK
        > > > > > relationship
        > > > > >
        > > > > >
        > > > > > I have two tables Table1 and 2. Table2 has a FK relationship
        > > > > > to 1 as shown below:
        > > > > >
        > > > > > Table1
        > > > > > -------
        > > > > > ID Col1 CumulativeInt
        > > > > > 1 One 0
        > > > > > 2 Two 0
        > > > > >
        > > > > >
        > > > > >
        > > > > > Table2
        > > > > > --------
        > > > > > ID Table1Id IntValue
        > > > > > 1 1 16
        > > > > > 2 1 32
        > > > > > 3 2 16
        > > > > >
        > > > > >
        > > > > > for each row in Table1, I want to update the "CumulativeInt"
        > > > > > column such that the it is the bitwise AND of the column
        > > > > > IntValuein Table2 (or some such other numeric operation).
        > > > > >
        > > > > > Can I do something like this: (it is incorrect syntax but
        > > > > > this is what I'm trying to do)
        > > > > >
        > > > > > UPDATE Table1 SET CumulativeInt=CumulativeInt&t2.IntValue
        > > > > > WHERE
        > > > > > t2.Table1Id = t1.ID
        > > > > >
        > > > > > I'm using Sybase 10.
        > > > > >
        > > > > > TIA
        > > > > >
        > > > > >
        > > > > >
        > > > > > ------------------------------------
        > > > > >
        > > > > > Yahoo! Groups Links
        > > > > >
        > > > > >
        > > > > >
        > > > >
        > > >
        > > >
        > > >
        > > > ------------------------------------
        > > >
        > > > Yahoo! Groups Links
        > > >
        > > >
        > > >
        > >
        >
        >
        >
        > ------------------------------------
        >
        > Yahoo! Groups Links
        >
        >
        >
        >
        > *** Email Confidentiality Notice ***
        > This message is private and confidential. If you have received this
        message
        > in error, please notify the sender and remove it from your system.
        >
      • John Warner
        Well what sort of short cut were you looking for that would allow you to ignore some conditions? John Warner ... have ... Table2.IntValue)
        Message 3 of 7 , Apr 19, 2008
          Well what sort of short cut were you looking for that would allow you to
          ignore some conditions?

          John Warner


          > -----Original Message-----
          > From: SQLQueriesNoCode@yahoogroups.com
          > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of mail_bala
          > Sent: Saturday, April 19, 2008 3:18 PM
          > To: SQLQueriesNoCode@yahoogroups.com
          > Subject: [SQLQueriesNoCode] Re: Column value update based on FK relati
          > onship
          >
          > As you can see, I am trying to set a single column to values based on
          > condition. The column is a 'cumulative result' of all conditions. I'm
          > not so sure how CASE will help here without having all permutations of
          > all conditions the conditions such as:
          >
          > CASE
          > WHEN (CONDITION1 & CONDITION2 & CONDITION3) THEN
          > SET COLUMN11 = X|A|C
          > WHEN (CONDITION1 & CONDITION2 & !CONDITION3) THEN
          > SET COLUMN11 = X|A
          > WHEN (CONDITION1 & !CONDITION2 & CONDITION3) THEN
          > SET COLUMN11 = X|B|C
          > ...
          > END CASE;
          >
          >
          > Am I missing something ?
          >
          > TIA
          >
          >
          > --- In SQLQueriesNoCode@yahoogroups.com, Travis Truax
          > <travis.truax@...> wrote:
          > >
          > > Look at the CASE statement in the online help.
          > >
          > >
          > > -----Original Message-----
          > > From: mail_bala [mailto:mail_bala@...]
          > > Sent: Thursday, April 17, 2008 3:06 PM
          > > To: SQLQueriesNoCode@yahoogroups.com
          > > Subject: [SQLQueriesNoCode] Re: Column value update based on FK
          > > relationship
          > >
          > >
          > > Can I do something along these lines:
          > >
          > >
          > > UPDATE TABLE1
          > > SET COLUMN11 = IF (CONDITION1) X ELSE Y ENDIF
          > > |
          > > COLUMN11 = IF (CONDITION2) A ELSE B ENDIF
          > > |
          > > COLUMN11 = IF (CONDITION3) C ENDIF
          > > |
          > > ...
          > >
          > > Basically setting one column value to a cumulative value (OR'd as
          > > above) where each value is driven by a separate condition. I was able
          > > to do them as separate UPDATE statements but this means the table
          > > joins (which are needed for the conditions) were there many times. Not
          > > sure if this causes those tables to be scanned over and over...?
          > >
          > >
          > >
          > >
          > >
          > >
          > > --- In SQLQueriesNoCode@yahoogroups.com, "John Warner" <john@>
          > wrote:
          > > >
          > > > You could use an update trigger to hold that logic. Not sure how you
          > > would
          > > > code it though. I would guess the trigger would need to be on
          > > CumulativeInt
          > > > but I'd have to play with that in a test database. Maybe someone
          > else on
          > > > the list has some ideas. This is still T-SQL though the dialects
          have
          > > > drifted apart with SQL Server.
          > > >
          > > > John Warner
          > > >
          > > >
          > > >
          > > >
          > > > > -----Original Message-----
          > > > > From: SQLQueriesNoCode@yahoogroups.com
          > > > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of mail_bala
          > > > > Sent: Thursday, April 10, 2008 1:10 PM
          > > > > To: SQLQueriesNoCode@yahoogroups.com
          > > > > Subject: [SQLQueriesNoCode] Re: Column value update based on
          > > > > FK relationship
          > > > >
          > > > >
          > > > > Thanks John. It looks correct to me (will try it later today
          > > > > for my confirmation).
          > > > >
          > > > > One question: is there a way I can 'ignore' further updates
          > > > > to the same row in Table1 once I know the CumulativeInt has
          > > > > reached a certain value ? Using the example below, assuming
          > > > > Table2.IntValue can have only one of (32, 16), once I see a
          > > > > Table2.IntValue = 16 (or more correctly a non-32), I would
          > > > > like the value to be set to 16 (i.e. if atleast one
          > > > > Table2.IntValue is not 32 then Table1.CumulativeInt is 16; if
          > > > > all are 32 then 32). Something such as
          > > > >
          > > > > if (Table2.IntValue is ALL 32)
          > > > > Table1.CumulativeInt = 32
          > > > > else
          > > > > Table1.CumulativeInt = 16
          > > > >
          > > > >
          > > > > TIA
          > > > >
          > > > > /bala
          > > > >
          > > > > --- In SQLQueriesNoCode@yahoogroups.com, "John Warner"
          > > > > <john@> wrote:
          > > > > >
          > > > > > UPDATE Table1
          > > > > > SET Table1.CumulativeInt = (Table1.CumlativeInt &
          Table2.IntValue)
          > > > > > FROM Table1
          > > > > > INNER JOIN Table2 ON (Table1.ID = Table2.Table1Id)
          > > > > >
          > > > > > I don't Sybase so this could be wrong but it should be close to
          > > > > > correct. I'm not completely sure how Sybase identifies
          > > > > objects which
          > > > > > is where I would suspect errors if they occur.
          > > > > >
          > > > > > John Warner
          > > > > >
          > > > > >
          > > > > >
          > > > > >
          > > > > > > -----Original Message-----
          > > > > > > From: SQLQueriesNoCode@yahoogroups.com
          > > > > > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of
          > mail_bala
          > > > > > > Sent: Wednesday, April 09, 2008 4:39 PM
          > > > > > > To: SQLQueriesNoCode@yahoogroups.com
          > > > > > > Subject: [SQLQueriesNoCode] Column value update based on FK
          > > > > > > relationship
          > > > > > >
          > > > > > >
          > > > > > > I have two tables Table1 and 2. Table2 has a FK relationship
          > > > > > > to 1 as shown below:
          > > > > > >
          > > > > > > Table1
          > > > > > > -------
          > > > > > > ID Col1 CumulativeInt
          > > > > > > 1 One 0
          > > > > > > 2 Two 0
          > > > > > >
          > > > > > >
          > > > > > >
          > > > > > > Table2
          > > > > > > --------
          > > > > > > ID Table1Id IntValue
          > > > > > > 1 1 16
          > > > > > > 2 1 32
          > > > > > > 3 2 16
          > > > > > >
          > > > > > >
          > > > > > > for each row in Table1, I want to update the "CumulativeInt"
          > > > > > > column such that the it is the bitwise AND of the column
          > > > > > > IntValuein Table2 (or some such other numeric operation).
          > > > > > >
          > > > > > > Can I do something like this: (it is incorrect syntax but
          > > > > > > this is what I'm trying to do)
          > > > > > >
          > > > > > > UPDATE Table1 SET CumulativeInt=CumulativeInt&t2.IntValue
          > > > > > > WHERE
          > > > > > > t2.Table1Id = t1.ID
          > > > > > >
          > > > > > > I'm using Sybase 10.
          > > > > > >
          > > > > > > TIA
          > > > > > >
          > > > > > >
          > > > > > >
          > > > > > > ------------------------------------
          > > > > > >
          > > > > > > Yahoo! Groups Links
          > > > > > >
          > > > > > >
          > > > > > >
          > > > > >
          > > > >
          > > > >
          > > > >
          > > > > ------------------------------------
          > > > >
          > > > > Yahoo! Groups Links
          > > > >
          > > > >
          > > > >
          > > >
          > >
          > >
          > >
          > > ------------------------------------
          > >
          > > Yahoo! Groups Links
          > >
          > >
          > >
          > >
          > > *** Email Confidentiality Notice ***
          > > This message is private and confidential. If you have received this
          > message
          > > in error, please notify the sender and remove it from your system.
          > >
          >
          >
          >
          > ------------------------------------
          >
          > Yahoo! Groups Links
          >
          >
          >
        • mail_bala
          ... I didn t want to ignore conditions. I wanted to see if I can save the number of join s. I m looking to do multiple SET s for the same column in a single
          Message 4 of 7 , Apr 20, 2008
            > Well what sort of short cut were you looking for that would
            > allow you to ignore some conditions?

            I didn't want to ignore conditions. I wanted to see if I can save the
            number of join's. I'm looking to do multiple SET's for the same column
            in a single UPDATE statement based on different conditions all of
            which are OR'd. I understand I can do them i) in different UPDATE
            stmt's using IF-ELSE's or ii) using CASE stmt as pointed to below. But
            the since my condition checks requires joining tables, I thought I can
            save table scan's. Again, I'm only few weeks into my SQL and so if I'm
            missing something lmk.


            Thanks !



            > > > UPDATE TABLE1
            > > > SET COLUMN11 = IF (CONDITION1) X ELSE Y ENDIF
            > > > |
            > > > COLUMN11 = IF (CONDITION2) A ELSE B ENDIF
            > > > |
            > > > COLUMN11 = IF (CONDITION3) C ENDIF
            > > > |
            > > > ...

            --- In SQLQueriesNoCode@yahoogroups.com, "John Warner" <john@...> wrote:
            >
            > Well what sort of short cut were you looking for that would allow you to
            > ignore some conditions?
            >
            > John Warner
            >
            >
            > > -----Original Message-----
            > > From: SQLQueriesNoCode@yahoogroups.com
            > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of mail_bala
            > > Sent: Saturday, April 19, 2008 3:18 PM
            > > To: SQLQueriesNoCode@yahoogroups.com
            > > Subject: [SQLQueriesNoCode] Re: Column value update based on FK relati
            > > onship
            > >
            > > As you can see, I am trying to set a single column to values based on
            > > condition. The column is a 'cumulative result' of all conditions. I'm
            > > not so sure how CASE will help here without having all permutations of
            > > all conditions the conditions such as:
            > >
            > > CASE
            > > WHEN (CONDITION1 & CONDITION2 & CONDITION3) THEN
            > > SET COLUMN11 = X|A|C
            > > WHEN (CONDITION1 & CONDITION2 & !CONDITION3) THEN
            > > SET COLUMN11 = X|A
            > > WHEN (CONDITION1 & !CONDITION2 & CONDITION3) THEN
            > > SET COLUMN11 = X|B|C
            > > ...
            > > END CASE;
            > >
            > >
            > > Am I missing something ?
            > >
            > > TIA
            > >
            > >
            > > --- In SQLQueriesNoCode@yahoogroups.com, Travis Truax
            > > <travis.truax@> wrote:
            > > >
            > > > Look at the CASE statement in the online help.
            > > >
            > > >
            > > > -----Original Message-----
            > > > From: mail_bala [mailto:mail_bala@]
            > > > Sent: Thursday, April 17, 2008 3:06 PM
            > > > To: SQLQueriesNoCode@yahoogroups.com
            > > > Subject: [SQLQueriesNoCode] Re: Column value update based on FK
            > > > relationship
            > > >
            > > >
            > > > Can I do something along these lines:
            > > >
            > > >
            > > > UPDATE TABLE1
            > > > SET COLUMN11 = IF (CONDITION1) X ELSE Y ENDIF
            > > > |
            > > > COLUMN11 = IF (CONDITION2) A ELSE B ENDIF
            > > > |
            > > > COLUMN11 = IF (CONDITION3) C ENDIF
            > > > |
            > > > ...
            > > >
            > > > Basically setting one column value to a cumulative value (OR'd as
            > > > above) where each value is driven by a separate condition. I was
            able
            > > > to do them as separate UPDATE statements but this means the table
            > > > joins (which are needed for the conditions) were there many
            times. Not
            > > > sure if this causes those tables to be scanned over and over...?
            > > >
            > > >
            > > >
            > > >
            > > >
            > > >
            > > > --- In SQLQueriesNoCode@yahoogroups.com, "John Warner" <john@>
            > > wrote:
            > > > >
            > > > > You could use an update trigger to hold that logic. Not sure
            how you
            > > > would
            > > > > code it though. I would guess the trigger would need to be on
            > > > CumulativeInt
            > > > > but I'd have to play with that in a test database. Maybe someone
            > > else on
            > > > > the list has some ideas. This is still T-SQL though the dialects
            > have
            > > > > drifted apart with SQL Server.
            > > > >
            > > > > John Warner
            > > > >
            > > > >
            > > > >
            > > > >
            > > > > > -----Original Message-----
            > > > > > From: SQLQueriesNoCode@yahoogroups.com
            > > > > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of mail_bala
            > > > > > Sent: Thursday, April 10, 2008 1:10 PM
            > > > > > To: SQLQueriesNoCode@yahoogroups.com
            > > > > > Subject: [SQLQueriesNoCode] Re: Column value update based on
            > > > > > FK relationship
            > > > > >
            > > > > >
            > > > > > Thanks John. It looks correct to me (will try it later today
            > > > > > for my confirmation).
            > > > > >
            > > > > > One question: is there a way I can 'ignore' further updates
            > > > > > to the same row in Table1 once I know the CumulativeInt has
            > > > > > reached a certain value ? Using the example below, assuming
            > > > > > Table2.IntValue can have only one of (32, 16), once I see a
            > > > > > Table2.IntValue = 16 (or more correctly a non-32), I would
            > > > > > like the value to be set to 16 (i.e. if atleast one
            > > > > > Table2.IntValue is not 32 then Table1.CumulativeInt is 16; if
            > > > > > all are 32 then 32). Something such as
            > > > > >
            > > > > > if (Table2.IntValue is ALL 32)
            > > > > > Table1.CumulativeInt = 32
            > > > > > else
            > > > > > Table1.CumulativeInt = 16
            > > > > >
            > > > > >
            > > > > > TIA
            > > > > >
            > > > > > /bala
            > > > > >
            > > > > > --- In SQLQueriesNoCode@yahoogroups.com, "John Warner"
            > > > > > <john@> wrote:
            > > > > > >
            > > > > > > UPDATE Table1
            > > > > > > SET Table1.CumulativeInt = (Table1.CumlativeInt &
            > Table2.IntValue)
            > > > > > > FROM Table1
            > > > > > > INNER JOIN Table2 ON (Table1.ID = Table2.Table1Id)
            > > > > > >
            > > > > > > I don't Sybase so this could be wrong but it should be
            close to
            > > > > > > correct. I'm not completely sure how Sybase identifies
            > > > > > objects which
            > > > > > > is where I would suspect errors if they occur.
            > > > > > >
            > > > > > > John Warner
            > > > > > >
            > > > > > >
            > > > > > >
            > > > > > >
            > > > > > > > -----Original Message-----
            > > > > > > > From: SQLQueriesNoCode@yahoogroups.com
            > > > > > > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of
            > > mail_bala
            > > > > > > > Sent: Wednesday, April 09, 2008 4:39 PM
            > > > > > > > To: SQLQueriesNoCode@yahoogroups.com
            > > > > > > > Subject: [SQLQueriesNoCode] Column value update based on FK
            > > > > > > > relationship
            > > > > > > >
            > > > > > > >
            > > > > > > > I have two tables Table1 and 2. Table2 has a FK relationship
            > > > > > > > to 1 as shown below:
            > > > > > > >
            > > > > > > > Table1
            > > > > > > > -------
            > > > > > > > ID Col1 CumulativeInt
            > > > > > > > 1 One 0
            > > > > > > > 2 Two 0
            > > > > > > >
            > > > > > > >
            > > > > > > >
            > > > > > > > Table2
            > > > > > > > --------
            > > > > > > > ID Table1Id IntValue
            > > > > > > > 1 1 16
            > > > > > > > 2 1 32
            > > > > > > > 3 2 16
            > > > > > > >
            > > > > > > >
            > > > > > > > for each row in Table1, I want to update the "CumulativeInt"
            > > > > > > > column such that the it is the bitwise AND of the column
            > > > > > > > IntValuein Table2 (or some such other numeric operation).
            > > > > > > >
            > > > > > > > Can I do something like this: (it is incorrect syntax but
            > > > > > > > this is what I'm trying to do)
            > > > > > > >
            > > > > > > > UPDATE Table1 SET CumulativeInt=CumulativeInt&t2.IntValue
            > > > > > > > WHERE
            > > > > > > > t2.Table1Id = t1.ID
            > > > > > > >
            > > > > > > > I'm using Sybase 10.
            > > > > > > >
            > > > > > > > TIA
            > > > > > > >
            > > > > > > >
            > > > > > > >
            > > > > > > > ------------------------------------
            > > > > > > >
            > > > > > > > Yahoo! Groups Links
            > > > > > > >
            > > > > > > >
            > > > > > > >
            > > > > > >
            > > > > >
            > > > > >
            > > > > >
            > > > > > ------------------------------------
            > > > > >
            > > > > > Yahoo! Groups Links
            > > > > >
            > > > > >
            > > > > >
            > > > >
            > > >
            > > >
            > > >
            > > > ------------------------------------
            > > >
            > > > Yahoo! Groups Links
            > > >
            > > >
            > > >
            > > >
            > > > *** Email Confidentiality Notice ***
            > > > This message is private and confidential. If you have received this
            > > message
            > > > in error, please notify the sender and remove it from your system.
            > > >
            > >
            > >
            > >
            > > ------------------------------------
            > >
            > > Yahoo! Groups Links
            > >
            > >
            > >
            >
          • John Warner
            Break the problem down into simple steps, write code to handle each step. Then start looking at how to glue the steps together. Can you do this via a stored
            Message 5 of 7 , Apr 20, 2008
              Break the problem down into simple steps, write code to handle each step.
              Then start looking at how to 'glue' the steps together. Can you do this
              via a stored Proc?

              John Warner


              > -----Original Message-----
              > From: SQLQueriesNoCode@yahoogroups.com
              > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of mail_bala
              > Sent: Sunday, April 20, 2008 6:27 PM
              > To: SQLQueriesNoCode@yahoogroups.com
              > Subject: [SQLQueriesNoCode] Re: Column value update based on FK relati
              > onship
              >
              > > Well what sort of short cut were you looking for that would
              > > allow you to ignore some conditions?
              >
              > I didn't want to ignore conditions. I wanted to see if I can save the
              > number of join's. I'm looking to do multiple SET's for the same column
              > in a single UPDATE statement based on different conditions all of
              > which are OR'd. I understand I can do them i) in different UPDATE
              > stmt's using IF-ELSE's or ii) using CASE stmt as pointed to below. But
              > the since my condition checks requires joining tables, I thought I can
              > save table scan's. Again, I'm only few weeks into my SQL and so if I'm
              > missing something lmk.
              >
              >
              > Thanks !
              >
              >
              >
              > > > > UPDATE TABLE1
              > > > > SET COLUMN11 = IF (CONDITION1) X ELSE Y ENDIF
              > > > > |
              > > > > COLUMN11 = IF (CONDITION2) A ELSE B ENDIF
              > > > > |
              > > > > COLUMN11 = IF (CONDITION3) C ENDIF
              > > > > |
              > > > > ...
              >
              > --- In SQLQueriesNoCode@yahoogroups.com, "John Warner" <john@...>
              > wrote:
              > >
              > > Well what sort of short cut were you looking for that would allow you
              to
              > > ignore some conditions?
              > >
              > > John Warner
              > >
              > >
              > > > -----Original Message-----
              > > > From: SQLQueriesNoCode@yahoogroups.com
              > > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of mail_bala
              > > > Sent: Saturday, April 19, 2008 3:18 PM
              > > > To: SQLQueriesNoCode@yahoogroups.com
              > > > Subject: [SQLQueriesNoCode] Re: Column value update based on FK
              > relati
              > > > onship
              > > >
              > > > As you can see, I am trying to set a single column to values based
              on
              > > > condition. The column is a 'cumulative result' of all conditions.
              I'm
              > > > not so sure how CASE will help here without having all permutations
              of
              > > > all conditions the conditions such as:
              > > >
              > > > CASE
              > > > WHEN (CONDITION1 & CONDITION2 & CONDITION3) THEN
              > > > SET COLUMN11 = X|A|C
              > > > WHEN (CONDITION1 & CONDITION2 & !CONDITION3) THEN
              > > > SET COLUMN11 = X|A
              > > > WHEN (CONDITION1 & !CONDITION2 & CONDITION3) THEN
              > > > SET COLUMN11 = X|B|C
              > > > ...
              > > > END CASE;
              > > >
              > > >
              > > > Am I missing something ?
              > > >
              > > > TIA
              > > >
              > > >
              > > > --- In SQLQueriesNoCode@yahoogroups.com, Travis Truax
              > > > <travis.truax@> wrote:
              > > > >
              > > > > Look at the CASE statement in the online help.
              > > > >
              > > > >
              > > > > -----Original Message-----
              > > > > From: mail_bala [mailto:mail_bala@]
              > > > > Sent: Thursday, April 17, 2008 3:06 PM
              > > > > To: SQLQueriesNoCode@yahoogroups.com
              > > > > Subject: [SQLQueriesNoCode] Re: Column value update based on FK
              > > > > relationship
              > > > >
              > > > >
              > > > > Can I do something along these lines:
              > > > >
              > > > >
              > > > > UPDATE TABLE1
              > > > > SET COLUMN11 = IF (CONDITION1) X ELSE Y ENDIF
              > > > > |
              > > > > COLUMN11 = IF (CONDITION2) A ELSE B ENDIF
              > > > > |
              > > > > COLUMN11 = IF (CONDITION3) C ENDIF
              > > > > |
              > > > > ...
              > > > >
              > > > > Basically setting one column value to a cumulative value (OR'd as
              > > > > above) where each value is driven by a separate condition. I was
              > able
              > > > > to do them as separate UPDATE statements but this means the table
              > > > > joins (which are needed for the conditions) were there many
              > times. Not
              > > > > sure if this causes those tables to be scanned over and over...?
              > > > >
              > > > >
              > > > >
              > > > >
              > > > >
              > > > >
              > > > > --- In SQLQueriesNoCode@yahoogroups.com, "John Warner" <john@>
              > > > wrote:
              > > > > >
              > > > > > You could use an update trigger to hold that logic. Not sure
              > how you
              > > > > would
              > > > > > code it though. I would guess the trigger would need to be on
              > > > > CumulativeInt
              > > > > > but I'd have to play with that in a test database. Maybe someone
              > > > else on
              > > > > > the list has some ideas. This is still T-SQL though the dialects
              > > have
              > > > > > drifted apart with SQL Server.
              > > > > >
              > > > > > John Warner
              > > > > >
              > > > > >
              > > > > >
              > > > > >
              > > > > > > -----Original Message-----
              > > > > > > From: SQLQueriesNoCode@yahoogroups.com
              > > > > > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of
              > mail_bala
              > > > > > > Sent: Thursday, April 10, 2008 1:10 PM
              > > > > > > To: SQLQueriesNoCode@yahoogroups.com
              > > > > > > Subject: [SQLQueriesNoCode] Re: Column value update based on
              > > > > > > FK relationship
              > > > > > >
              > > > > > >
              > > > > > > Thanks John. It looks correct to me (will try it later today
              > > > > > > for my confirmation).
              > > > > > >
              > > > > > > One question: is there a way I can 'ignore' further updates
              > > > > > > to the same row in Table1 once I know the CumulativeInt has
              > > > > > > reached a certain value ? Using the example below, assuming
              > > > > > > Table2.IntValue can have only one of (32, 16), once I see a
              > > > > > > Table2.IntValue = 16 (or more correctly a non-32), I would
              > > > > > > like the value to be set to 16 (i.e. if atleast one
              > > > > > > Table2.IntValue is not 32 then Table1.CumulativeInt is 16; if
              > > > > > > all are 32 then 32). Something such as
              > > > > > >
              > > > > > > if (Table2.IntValue is ALL 32)
              > > > > > > Table1.CumulativeInt = 32
              > > > > > > else
              > > > > > > Table1.CumulativeInt = 16
              > > > > > >
              > > > > > >
              > > > > > > TIA
              > > > > > >
              > > > > > > /bala
              > > > > > >
              > > > > > > --- In SQLQueriesNoCode@yahoogroups.com, "John Warner"
              > > > > > > <john@> wrote:
              > > > > > > >
              > > > > > > > UPDATE Table1
              > > > > > > > SET Table1.CumulativeInt = (Table1.CumlativeInt &
              > > Table2.IntValue)
              > > > > > > > FROM Table1
              > > > > > > > INNER JOIN Table2 ON (Table1.ID = Table2.Table1Id)
              > > > > > > >
              > > > > > > > I don't Sybase so this could be wrong but it should be
              > close to
              > > > > > > > correct. I'm not completely sure how Sybase identifies
              > > > > > > objects which
              > > > > > > > is where I would suspect errors if they occur.
              > > > > > > >
              > > > > > > > John Warner
              > > > > > > >
              > > > > > > >
              > > > > > > >
              > > > > > > >
              > > > > > > > > -----Original Message-----
              > > > > > > > > From: SQLQueriesNoCode@yahoogroups.com
              > > > > > > > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of
              > > > mail_bala
              > > > > > > > > Sent: Wednesday, April 09, 2008 4:39 PM
              > > > > > > > > To: SQLQueriesNoCode@yahoogroups.com
              > > > > > > > > Subject: [SQLQueriesNoCode] Column value update based on
              > FK
              > > > > > > > > relationship
              > > > > > > > >
              > > > > > > > >
              > > > > > > > > I have two tables Table1 and 2. Table2 has a FK
              relationship
              > > > > > > > > to 1 as shown below:
              > > > > > > > >
              > > > > > > > > Table1
              > > > > > > > > -------
              > > > > > > > > ID Col1 CumulativeInt
              > > > > > > > > 1 One 0
              > > > > > > > > 2 Two 0
              > > > > > > > >
              > > > > > > > >
              > > > > > > > >
              > > > > > > > > Table2
              > > > > > > > > --------
              > > > > > > > > ID Table1Id IntValue
              > > > > > > > > 1 1 16
              > > > > > > > > 2 1 32
              > > > > > > > > 3 2 16
              > > > > > > > >
              > > > > > > > >
              > > > > > > > > for each row in Table1, I want to update the
              "CumulativeInt"
              > > > > > > > > column such that the it is the bitwise AND of the column
              > > > > > > > > IntValuein Table2 (or some such other numeric operation).
              > > > > > > > >
              > > > > > > > > Can I do something like this: (it is incorrect syntax but
              > > > > > > > > this is what I'm trying to do)
              > > > > > > > >
              > > > > > > > > UPDATE Table1 SET CumulativeInt=CumulativeInt&t2.IntValue
              > > > > > > > > WHERE
              > > > > > > > > t2.Table1Id = t1.ID
              > > > > > > > >
              > > > > > > > > I'm using Sybase 10.
              > > > > > > > >
              > > > > > > > > TIA
              > > > > > > > >
              > > > > > > > >
              > > > > > > > >
              > > > > > > > > ------------------------------------
              > > > > > > > >
              > > > > > > > > Yahoo! Groups Links
              > > > > > > > >
              > > > > > > > >
              > > > > > > > >
              > > > > > > >
              > > > > > >
              > > > > > >
              > > > > > >
              > > > > > > ------------------------------------
              > > > > > >
              > > > > > > Yahoo! Groups Links
              > > > > > >
              > > > > > >
              > > > > > >
              > > > > >
              > > > >
              > > > >
              > > > >
              > > > > ------------------------------------
              > > > >
              > > > > Yahoo! Groups Links
              > > > >
              > > > >
              > > > >
              > > > >
              > > > > *** Email Confidentiality Notice ***
              > > > > This message is private and confidential. If you have received
              this
              > > > message
              > > > > in error, please notify the sender and remove it from your system.
              > > > >
              > > >
              > > >
              > > >
              > > > ------------------------------------
              > > >
              > > > Yahoo! Groups Links
              > > >
              > > >
              > > >
              > >
              >
              >
              >
              > ------------------------------------
              >
              > Yahoo! Groups Links
              >
              >
              >
            • Noman Aftab
              Dear Bala, try the following syntax: UPDATE MYTABLE SET COLUMN11 = CASE WHEN (CONDITION1 & CONDITION2 & CONDITION3) THEN X|A|C WHEN (CONDITION1 & CONDITION2 &
              Message 6 of 7 , Apr 20, 2008
                Dear Bala,
                try the following syntax:

                UPDATE MYTABLE
                SET COLUMN11 = CASE
                WHEN (CONDITION1 & CONDITION2 & CONDITION3) THEN X|A|C
                WHEN (CONDITION1 & CONDITION2 & !CONDITION3) THEN X|A
                WHEN (CONDITION1 & !CONDITION2 & CONDITION3) THEN X|B|C
                ELSE DEFAULT_VALUE END,
                COLUMN12 = {SOME OTHER VALUE}

                or something similar


                mail_bala <mail_bala@...> wrote:
                As you can see, I am trying to set a single column to values based on
                condition. The column is a 'cumulative result' of all conditions. I'm
                not so sure how CASE will help here without having all permutations of
                all conditions the conditions such as:

                CASE
                WHEN (CONDITION1 & CONDITION2 & CONDITION3) THEN
                SET COLUMN11 = X|A|C
                WHEN (CONDITION1 & CONDITION2 & !CONDITION3) THEN
                SET COLUMN11 = X|A
                WHEN (CONDITION1 & !CONDITION2 & CONDITION3) THEN
                SET COLUMN11 = X|B|C
                ...
                END CASE;

                Am I missing something ?

                TIA

                --- In SQLQueriesNoCode@yahoogroups.com, Travis Truax
                <travis.truax@...> wrote:
                >
                > Look at the CASE statement in the online help.
                >
                >
                > -----Original Message-----
                > From: mail_bala [mailto:mail_bala@...]
                > Sent: Thursday, April 17, 2008 3:06 PM
                > To: SQLQueriesNoCode@yahoogroups.com
                > Subject: [SQLQueriesNoCode] Re: Column value update based on FK
                > relationship
                >
                >
                > Can I do something along these lines:
                >
                >
                > UPDATE TABLE1
                > SET COLUMN11 = IF (CONDITION1) X ELSE Y ENDIF
                > |
                > COLUMN11 = IF (CONDITION2) A ELSE B ENDIF
                > |
                > COLUMN11 = IF (CONDITION3) C ENDIF
                > |
                > ...
                >
                > Basically setting one column value to a cumulative value (OR'd as
                > above) where each value is driven by a separate condition. I was able
                > to do them as separate UPDATE statements but this means the table
                > joins (which are needed for the conditions) were there many times. Not
                > sure if this causes those tables to be scanned over and over...?
                >
                >
                >
                >
                >
                >
                > --- In SQLQueriesNoCode@yahoogroups.com, "John Warner" <john@> wrote:
                > >
                > > You could use an update trigger to hold that logic. Not sure how you
                > would
                > > code it though. I would guess the trigger would need to be on
                > CumulativeInt
                > > but I'd have to play with that in a test database. Maybe someone
                else on
                > > the list has some ideas. This is still T-SQL though the dialects have
                > > drifted apart with SQL Server.
                > >
                > > John Warner
                > >
                > >
                > >
                > >
                > > > -----Original Message-----
                > > > From: SQLQueriesNoCode@yahoogroups.com
                > > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of mail_bala
                > > > Sent: Thursday, April 10, 2008 1:10 PM
                > > > To: SQLQueriesNoCode@yahoogroups.com
                > > > Subject: [SQLQueriesNoCode] Re: Column value update based on
                > > > FK relationship
                > > >
                > > >
                > > > Thanks John. It looks correct to me (will try it later today
                > > > for my confirmation).
                > > >
                > > > One question: is there a way I can 'ignore' further updates
                > > > to the same row in Table1 once I know the CumulativeInt has
                > > > reached a certain value ? Using the example below, assuming
                > > > Table2.IntValue can have only one of (32, 16), once I see a
                > > > Table2.IntValue = 16 (or more correctly a non-32), I would
                > > > like the value to be set to 16 (i.e. if atleast one
                > > > Table2.IntValue is not 32 then Table1.CumulativeInt is 16; if
                > > > all are 32 then 32). Something such as
                > > >
                > > > if (Table2.IntValue is ALL 32)
                > > > Table1.CumulativeInt = 32
                > > > else
                > > > Table1.CumulativeInt = 16
                > > >
                > > >
                > > > TIA
                > > >
                > > > /bala
                > > >
                > > > --- In SQLQueriesNoCode@yahoogroups.com, "John Warner"
                > > > <john@> wrote:
                > > > >
                > > > > UPDATE Table1
                > > > > SET Table1.CumulativeInt = (Table1.CumlativeInt & Table2.IntValue)
                > > > > FROM Table1
                > > > > INNER JOIN Table2 ON (Table1.ID = Table2.Table1Id)
                > > > >
                > > > > I don't Sybase so this could be wrong but it should be close to
                > > > > correct. I'm not completely sure how Sybase identifies
                > > > objects which
                > > > > is where I would suspect errors if they occur.
                > > > >
                > > > > John Warner
                > > > >
                > > > >
                > > > >
                > > > >
                > > > > > -----Original Message-----
                > > > > > From: SQLQueriesNoCode@yahoogroups.com
                > > > > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of mail_bala
                > > > > > Sent: Wednesday, April 09, 2008 4:39 PM
                > > > > > To: SQLQueriesNoCode@yahoogroups.com
                > > > > > Subject: [SQLQueriesNoCode] Column value update based on FK
                > > > > > relationship
                > > > > >
                > > > > >
                > > > > > I have two tables Table1 and 2. Table2 has a FK relationship
                > > > > > to 1 as shown below:
                > > > > >
                > > > > > Table1
                > > > > > -------
                > > > > > ID Col1 CumulativeInt
                > > > > > 1 One 0
                > > > > > 2 Two 0
                > > > > >
                > > > > >
                > > > > >
                > > > > > Table2
                > > > > > --------
                > > > > > ID Table1Id IntValue
                > > > > > 1 1 16
                > > > > > 2 1 32
                > > > > > 3 2 16
                > > > > >
                > > > > >
                > > > > > for each row in Table1, I want to update the "CumulativeInt"
                > > > > > column such that the it is the bitwise AND of the column
                > > > > > IntValuein Table2 (or some such other numeric operation).
                > > > > >
                > > > > > Can I do something like this: (it is incorrect syntax but
                > > > > > this is what I'm trying to do)
                > > > > >
                > > > > > UPDATE Table1 SET CumulativeInt=CumulativeInt&t2.IntValue
                > > > > > WHERE
                > > > > > t2.Table1Id = t1.ID
                > > > > >
                > > > > > I'm using Sybase 10.
                > > > > >
                > > > > > TIA
                > > > > >
                > > > > >
                > > > > >
                > > > > > ------------------------------------
                > > > > >
                > > > > > Yahoo! Groups Links
                > > > > >
                > > > > >
                > > > > >
                > > > >
                > > >
                > > >
                > > >
                > > > ------------------------------------
                > > >
                > > > Yahoo! Groups Links
                > > >
                > > >
                > > >
                > >
                >
                >
                >
                > ------------------------------------
                >
                > Yahoo! Groups Links
                >
                >
                >
                >
                > *** Email Confidentiality Notice ***
                > This message is private and confidential. If you have received this
                message
                > in error, please notify the sender and remove it from your system.
                >







                Noman Muhammad Aftab
                Software Engineer
                Avanza Solutions
                Tel: +92 21 567 5240 – 42 (ext: 762)
                Fax: +92 21 567 5244

                Cell: +92 333 231 0594
                URL: http://www.avanzasolutions.com

                Email: noman.aftab@...


                ---------------------------------
                Sent from Yahoo! Mail.
                A Smarter Email.

                [Non-text portions of this message have been removed]
              • mail_bala
                Thanks to everyone for suggestions. I did end up with an approach somewhat similar to what John suggested below. It works fine with a small volume of data but
                Message 7 of 7 , Apr 25, 2008
                  Thanks to everyone for suggestions. I did end up with an approach
                  somewhat similar to what John suggested below. It works fine with a
                  small volume of data but I'm going to pump up the volume in the next
                  few days and see how it goes.


                  /bala

                  --- In SQLQueriesNoCode@yahoogroups.com, "John Warner" <john@...> wrote:
                  >
                  > Break the problem down into simple steps, write code to handle each
                  step.
                  > Then start looking at how to 'glue' the steps together. Can you do this
                  > via a stored Proc?
                  >
                  > John Warner
                  >
                  >
                  > > -----Original Message-----
                  > > From: SQLQueriesNoCode@yahoogroups.com
                  > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of mail_bala
                  > > Sent: Sunday, April 20, 2008 6:27 PM
                  > > To: SQLQueriesNoCode@yahoogroups.com
                  > > Subject: [SQLQueriesNoCode] Re: Column value update based on FK relati
                  > > onship
                  > >
                  > > > Well what sort of short cut were you looking for that would
                  > > > allow you to ignore some conditions?
                  > >
                  > > I didn't want to ignore conditions. I wanted to see if I can save the
                  > > number of join's. I'm looking to do multiple SET's for the same column
                  > > in a single UPDATE statement based on different conditions all of
                  > > which are OR'd. I understand I can do them i) in different UPDATE
                  > > stmt's using IF-ELSE's or ii) using CASE stmt as pointed to below. But
                  > > the since my condition checks requires joining tables, I thought I can
                  > > save table scan's. Again, I'm only few weeks into my SQL and so if I'm
                  > > missing something lmk.
                  > >
                  > >
                  > > Thanks !
                  > >
                  > >
                  > >
                  > > > > > UPDATE TABLE1
                  > > > > > SET COLUMN11 = IF (CONDITION1) X ELSE Y ENDIF
                  > > > > > |
                  > > > > > COLUMN11 = IF (CONDITION2) A ELSE B ENDIF
                  > > > > > |
                  > > > > > COLUMN11 = IF (CONDITION3) C ENDIF
                  > > > > > |
                  > > > > > ...
                  > >
                  > > --- In SQLQueriesNoCode@yahoogroups.com, "John Warner" <john@>
                  > > wrote:
                  > > >
                  > > > Well what sort of short cut were you looking for that would
                  allow you
                  > to
                  > > > ignore some conditions?
                  > > >
                  > > > John Warner
                  > > >
                  > > >
                  > > > > -----Original Message-----
                  > > > > From: SQLQueriesNoCode@yahoogroups.com
                  > > > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of mail_bala
                  > > > > Sent: Saturday, April 19, 2008 3:18 PM
                  > > > > To: SQLQueriesNoCode@yahoogroups.com
                  > > > > Subject: [SQLQueriesNoCode] Re: Column value update based on FK
                  > > relati
                  > > > > onship
                  > > > >
                  > > > > As you can see, I am trying to set a single column to values based
                  > on
                  > > > > condition. The column is a 'cumulative result' of all conditions.
                  > I'm
                  > > > > not so sure how CASE will help here without having all
                  permutations
                  > of
                  > > > > all conditions the conditions such as:
                  > > > >
                  > > > > CASE
                  > > > > WHEN (CONDITION1 & CONDITION2 & CONDITION3) THEN
                  > > > > SET COLUMN11 = X|A|C
                  > > > > WHEN (CONDITION1 & CONDITION2 & !CONDITION3) THEN
                  > > > > SET COLUMN11 = X|A
                  > > > > WHEN (CONDITION1 & !CONDITION2 & CONDITION3) THEN
                  > > > > SET COLUMN11 = X|B|C
                  > > > > ...
                  > > > > END CASE;
                  > > > >
                  > > > >
                  > > > > Am I missing something ?
                  > > > >
                  > > > > TIA
                  > > > >
                  > > > >
                  > > > > --- In SQLQueriesNoCode@yahoogroups.com, Travis Truax
                  > > > > <travis.truax@> wrote:
                  > > > > >
                  > > > > > Look at the CASE statement in the online help.
                  > > > > >
                  > > > > >
                  > > > > > -----Original Message-----
                  > > > > > From: mail_bala [mailto:mail_bala@]
                  > > > > > Sent: Thursday, April 17, 2008 3:06 PM
                  > > > > > To: SQLQueriesNoCode@yahoogroups.com
                  > > > > > Subject: [SQLQueriesNoCode] Re: Column value update based on FK
                  > > > > > relationship
                  > > > > >
                  > > > > >
                  > > > > > Can I do something along these lines:
                  > > > > >
                  > > > > >
                  > > > > > UPDATE TABLE1
                  > > > > > SET COLUMN11 = IF (CONDITION1) X ELSE Y ENDIF
                  > > > > > |
                  > > > > > COLUMN11 = IF (CONDITION2) A ELSE B ENDIF
                  > > > > > |
                  > > > > > COLUMN11 = IF (CONDITION3) C ENDIF
                  > > > > > |
                  > > > > > ...
                  > > > > >
                  > > > > > Basically setting one column value to a cumulative value
                  (OR'd as
                  > > > > > above) where each value is driven by a separate condition. I was
                  > > able
                  > > > > > to do them as separate UPDATE statements but this means the
                  table
                  > > > > > joins (which are needed for the conditions) were there many
                  > > times. Not
                  > > > > > sure if this causes those tables to be scanned over and over...?
                  > > > > >
                  > > > > >
                  > > > > >
                  > > > > >
                  > > > > >
                  > > > > >
                  > > > > > --- In SQLQueriesNoCode@yahoogroups.com, "John Warner" <john@>
                  > > > > wrote:
                  > > > > > >
                  > > > > > > You could use an update trigger to hold that logic. Not sure
                  > > how you
                  > > > > > would
                  > > > > > > code it though. I would guess the trigger would need to be on
                  > > > > > CumulativeInt
                  > > > > > > but I'd have to play with that in a test database. Maybe
                  someone
                  > > > > else on
                  > > > > > > the list has some ideas. This is still T-SQL though the
                  dialects
                  > > > have
                  > > > > > > drifted apart with SQL Server.
                  > > > > > >
                  > > > > > > John Warner
                  > > > > > >
                  > > > > > >
                  > > > > > >
                  > > > > > >
                  > > > > > > > -----Original Message-----
                  > > > > > > > From: SQLQueriesNoCode@yahoogroups.com
                  > > > > > > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of
                  > > mail_bala
                  > > > > > > > Sent: Thursday, April 10, 2008 1:10 PM
                  > > > > > > > To: SQLQueriesNoCode@yahoogroups.com
                  > > > > > > > Subject: [SQLQueriesNoCode] Re: Column value update based on
                  > > > > > > > FK relationship
                  > > > > > > >
                  > > > > > > >
                  > > > > > > > Thanks John. It looks correct to me (will try it later today
                  > > > > > > > for my confirmation).
                  > > > > > > >
                  > > > > > > > One question: is there a way I can 'ignore' further updates
                  > > > > > > > to the same row in Table1 once I know the CumulativeInt has
                  > > > > > > > reached a certain value ? Using the example below, assuming
                  > > > > > > > Table2.IntValue can have only one of (32, 16), once I see a
                  > > > > > > > Table2.IntValue = 16 (or more correctly a non-32), I would
                  > > > > > > > like the value to be set to 16 (i.e. if atleast one
                  > > > > > > > Table2.IntValue is not 32 then Table1.CumulativeInt is
                  16; if
                  > > > > > > > all are 32 then 32). Something such as
                  > > > > > > >
                  > > > > > > > if (Table2.IntValue is ALL 32)
                  > > > > > > > Table1.CumulativeInt = 32
                  > > > > > > > else
                  > > > > > > > Table1.CumulativeInt = 16
                  > > > > > > >
                  > > > > > > >
                  > > > > > > > TIA
                  > > > > > > >
                  > > > > > > > /bala
                  > > > > > > >
                  > > > > > > > --- In SQLQueriesNoCode@yahoogroups.com, "John Warner"
                  > > > > > > > <john@> wrote:
                  > > > > > > > >
                  > > > > > > > > UPDATE Table1
                  > > > > > > > > SET Table1.CumulativeInt = (Table1.CumlativeInt &
                  > > > Table2.IntValue)
                  > > > > > > > > FROM Table1
                  > > > > > > > > INNER JOIN Table2 ON (Table1.ID = Table2.Table1Id)
                  > > > > > > > >
                  > > > > > > > > I don't Sybase so this could be wrong but it should be
                  > > close to
                  > > > > > > > > correct. I'm not completely sure how Sybase identifies
                  > > > > > > > objects which
                  > > > > > > > > is where I would suspect errors if they occur.
                  > > > > > > > >
                  > > > > > > > > John Warner
                  > > > > > > > >
                  > > > > > > > >
                  > > > > > > > >
                  > > > > > > > >
                  > > > > > > > > > -----Original Message-----
                  > > > > > > > > > From: SQLQueriesNoCode@yahoogroups.com
                  > > > > > > > > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of
                  > > > > mail_bala
                  > > > > > > > > > Sent: Wednesday, April 09, 2008 4:39 PM
                  > > > > > > > > > To: SQLQueriesNoCode@yahoogroups.com
                  > > > > > > > > > Subject: [SQLQueriesNoCode] Column value update based on
                  > > FK
                  > > > > > > > > > relationship
                  > > > > > > > > >
                  > > > > > > > > >
                  > > > > > > > > > I have two tables Table1 and 2. Table2 has a FK
                  > relationship
                  > > > > > > > > > to 1 as shown below:
                  > > > > > > > > >
                  > > > > > > > > > Table1
                  > > > > > > > > > -------
                  > > > > > > > > > ID Col1 CumulativeInt
                  > > > > > > > > > 1 One 0
                  > > > > > > > > > 2 Two 0
                  > > > > > > > > >
                  > > > > > > > > >
                  > > > > > > > > >
                  > > > > > > > > > Table2
                  > > > > > > > > > --------
                  > > > > > > > > > ID Table1Id IntValue
                  > > > > > > > > > 1 1 16
                  > > > > > > > > > 2 1 32
                  > > > > > > > > > 3 2 16
                  > > > > > > > > >
                  > > > > > > > > >
                  > > > > > > > > > for each row in Table1, I want to update the
                  > "CumulativeInt"
                  > > > > > > > > > column such that the it is the bitwise AND of the column
                  > > > > > > > > > IntValuein Table2 (or some such other numeric
                  operation).
                  > > > > > > > > >
                  > > > > > > > > > Can I do something like this: (it is incorrect
                  syntax but
                  > > > > > > > > > this is what I'm trying to do)
                  > > > > > > > > >
                  > > > > > > > > > UPDATE Table1 SET
                  CumulativeInt=CumulativeInt&t2.IntValue
                  > > > > > > > > > WHERE
                  > > > > > > > > > t2.Table1Id = t1.ID
                  > > > > > > > > >
                  > > > > > > > > > I'm using Sybase 10.
                  > > > > > > > > >
                  > > > > > > > > > TIA
                  > > > > > > > > >
                  > > > > > > > > >
                  > > > > > > > > >
                  > > > > > > > > > ------------------------------------
                  > > > > > > > > >
                  > > > > > > > > > Yahoo! Groups Links
                  > > > > > > > > >
                  > > > > > > > > >
                  > > > > > > > > >
                  > > > > > > > >
                  > > > > > > >
                  > > > > > > >
                  > > > > > > >
                  > > > > > > > ------------------------------------
                  > > > > > > >
                  > > > > > > > Yahoo! Groups Links
                  > > > > > > >
                  > > > > > > >
                  > > > > > > >
                  > > > > > >
                  > > > > >
                  > > > > >
                  > > > > >
                  > > > > > ------------------------------------
                  > > > > >
                  > > > > > Yahoo! Groups Links
                  > > > > >
                  > > > > >
                  > > > > >
                  > > > > >
                  > > > > > *** Email Confidentiality Notice ***
                  > > > > > This message is private and confidential. If you have received
                  > this
                  > > > > message
                  > > > > > in error, please notify the sender and remove it from your
                  system.
                  > > > > >
                  > > > >
                  > > > >
                  > > > >
                  > > > > ------------------------------------
                  > > > >
                  > > > > Yahoo! Groups Links
                  > > > >
                  > > > >
                  > > > >
                  > > >
                  > >
                  > >
                  > >
                  > > ------------------------------------
                  > >
                  > > Yahoo! Groups Links
                  > >
                  > >
                  > >
                  >
                Your message has been successfully submitted and would be delivered to recipients shortly.