Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

How do I update column in a table linked to other tables

6 posts in General Discussion Last posting was on 2008-04-17 19:58:03.0Z
Bala Venkata Posted on 2008-04-15 18:35:00.0Z
Sender: 24c.4804f186.1804289383@sybase.com
From: Bala Venkata
Newsgroups: ianywhere.public.general
Subject: How do I update column in a table linked to other tables
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4804f554.293.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 15 Apr 2008 11:35:00 -0700
X-Trace: forums-1-dub 1208284500 10.22.241.41 (15 Apr 2008 11:35:00 -0700)
X-Original-Trace: 15 Apr 2008 11:35:00 -0700, 10.22.241.41
Lines: 23
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6812
Article PK: 5049

I have three tables that are linked as below:

Table1 (ID1, Col1)

Table2 (ID2, ID1, Col21)

Table3 (ID3, ID2, Col31)

Table2 has a FK relationship to Table1. Table3 has FK
relationship to Table2.

For each row in Table1, I need calculate & set the Col1
value based on these
- Col21 values associated with that row (need to compare the
value of Col21 among all rows)
- Col31 values associated with the row in Table1
- Number of rows of Table3 associated with the row in Table1

1. Do I need to create a function for this or can this be
done via a simple UPDATE SQL statement
2. If a function is needed should this be done in a loop
that processes the values one row of Table1 at a time


Breck Carter [sqlanywhere.blogspot.com] Posted on 2008-04-15 18:46:37.0Z
From: "Breck Carter [sqlanywhere.blogspot.com]" <NOSPAM__breck.carter@gmail.com>
Newsgroups: ianywhere.public.general
Subject: Re: How do I update column in a table linked to other tables
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__breck.carter@gmail.com
Message-ID: <tft9045l4iiqq7ubngb65ic8v9k4bumjon@4ax.com>
References: <4804f554.293.1681692777@sybase.com>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 15 Apr 2008 11:46:37 -0700
X-Trace: forums-1-dub 1208285197 10.22.241.152 (15 Apr 2008 11:46:37 -0700)
X-Original-Trace: 15 Apr 2008 11:46:37 -0700, vip152.sybase.com
Lines: 36
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6813
Article PK: 5050

More information is needed in order to give an decent answer; i.e.,
exactly what do you mean by whole "...based on these..." discussion?

Breck

On 15 Apr 2008 11:35:00 -0700, Bala Venkata wrote:

>
>I have three tables that are linked as below:
>
>Table1 (ID1, Col1)
>
>Table2 (ID2, ID1, Col21)
>
>Table3 (ID3, ID2, Col31)
>
>Table2 has a FK relationship to Table1. Table3 has FK
>relationship to Table2.
>
>For each row in Table1, I need calculate & set the Col1
>value based on these
>- Col21 values associated with that row (need to compare the
>value of Col21 among all rows)
>- Col31 values associated with the row in Table1
>- Number of rows of Table3 associated with the row in Table1
>
>1. Do I need to create a function for this or can this be
>done via a simple UPDATE SQL statement
>2. If a function is needed should this be done in a loop
>that processes the values one row of Table1 at a time

--
Breck Carter http://sqlanywhere.blogspot.com/

RisingRoad SQL Anywhere and MobiLink Professional Services
breck.carter@risingroad.com


Bala Venkata Posted on 2008-04-15 19:15:11.0Z
Sender: 24c.4804f186.1804289383@sybase.com
From: Bala Venkata
Newsgroups: ianywhere.public.general
Subject: Re: How do I update column in a table linked to other tables
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4804febf.3d7.1681692777@sybase.com>
References: <tft9045l4iiqq7ubngb65ic8v9k4bumjon@4ax.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 15 Apr 2008 12:15:11 -0700
X-Trace: forums-1-dub 1208286911 10.22.241.41 (15 Apr 2008 12:15:11 -0700)
X-Original-Trace: 15 Apr 2008 12:15:11 -0700, 10.22.241.41
Lines: 87
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6814
Article PK: 5051

Given the following entries,

Table1
--------------
ID1 Col1
--------------
101 0
102 0


Table2
----------------------------
ID2 ID1 Col21
----------------------------
201 101 "t2-1-1"
202 101 "t2-1-2"
203 101 "t2-1-3"
204 102 "t2-2-1"


Table3
----------------------------
ID3 ID2 Col31
----------------------------
301 201 "t3-1-1-1"
302 201 "t3-1-1-2"
303 203 "t3-1-3-1"
304 204 "t4-2-1-1"



- I want to set Col1 for row1 to int value X based on the
Col21 values associated with that (in the above case, the
values of rows 201, 202, 203). That is, if all values of
Col21 for ID 101 are the same set Col1 value to 1; 2
otherwise.
- I also have other conditions on setting Col1 value based
on values in Col31 of table3 that are associated (via
Table2).
- The final condition was the number of rows of Table3
associated with a row in Table1 determines its Col1 value.

I may have more columns in tables 2 and 3 in the future that
I'd need to check on as well.


TIA!

> More information is needed in order to give an decent
> answer; i.e., exactly what do you mean by whole "...based
> on these..." discussion?
>
> Breck
>
> On 15 Apr 2008 11:35:00 -0700, Bala Venkata wrote:
>
> >
> >I have three tables that are linked as below:
> >
> >Table1 (ID1, Col1)
> >
> >Table2 (ID2, ID1, Col21)
> >
> >Table3 (ID3, ID2, Col31)
> >
> >Table2 has a FK relationship to Table1. Table3 has FK
> >relationship to Table2.
> >
> >For each row in Table1, I need calculate & set the Col1
> >value based on these
> >- Col21 values associated with that row (need to compare
> the >value of Col21 among all rows)
> >- Col31 values associated with the row in Table1
> >- Number of rows of Table3 associated with the row in
> Table1 >
> >1. Do I need to create a function for this or can this be
> >done via a simple UPDATE SQL statement
> >2. If a function is needed should this be done in a loop
> >that processes the values one row of Table1 at a time
>
> --
> Breck Carter http://sqlanywhere.blogspot.com/
>
> RisingRoad SQL Anywhere and MobiLink Professional Services
> breck.carter@risingroad.com


"Frank Ploessel" <fpl... Posted on 2008-04-16 07:52:28.0Z
Subject: Re: How do I update column in a table linked to other tables
From: "Frank Ploessel" <fpl...@d_e.i_m_s_h_e_a_l_t_h.c_o_m>
Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-15
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
References: <tft9045l4iiqq7ubngb65ic8v9k4bumjon@4ax.com> <4804febf.3d7.1681692777@sybase.com>
Content-Transfer-Encoding: Quoted-Printable
Message-ID: <op.t9o1hnu2j0bybf@bonw00164.internal.imsglobal.com>
User-Agent: Opera Mail/9.21 (Win32)
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 16 Apr 2008 00:52:28 -0700
X-Trace: forums-1-dub 1208332348 10.22.241.152 (16 Apr 2008 00:52:28 -0700)
X-Original-Trace: 16 Apr 2008 00:52:28 -0700, vip152.sybase.com
Lines: 116
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6815
Article PK: 5052

Bala,

I can give you an answer for your first requirement, but as you just state
there are conditions for case 2 and 3, I can state that I assume there are
solutions in SQL.

For case 1 use:

update table1
set col1 = if (select count(distinct col21)
from table2
where table2.id1 = table1.id1) = 1
then 1
else 2
endif
where table1.id1 = 101



Frank

On Tue, 15 Apr 2008 21:15:11 +0200, Bala <Venkata> wrote:

> Given the following entries,
>
> Table1
> --------------
> ID1 Col1
> --------------
> 101 0
> 102 0
>
>
> Table2
> ----------------------------
> ID2 ID1 Col21
> ----------------------------
> 201 101 "t2-1-1"
> 202 101 "t2-1-2"
> 203 101 "t2-1-3"
> 204 102 "t2-2-1"
>
>
> Table3
> ----------------------------
> ID3 ID2 Col31
> ----------------------------
> 301 201 "t3-1-1-1"
> 302 201 "t3-1-1-2"
> 303 203 "t3-1-3-1"
> 304 204 "t4-2-1-1"
>
>
>
> - I want to set Col1 for row1 to int value X based on the
> Col21 values associated with that (in the above case, the
> values of rows 201, 202, 203). That is, if all values of
> Col21 for ID 101 are the same set Col1 value to 1; 2
> otherwise.
> - I also have other conditions on setting Col1 value based
> on values in Col31 of table3 that are associated (via
> Table2).
> - The final condition was the number of rows of Table3
> associated with a row in Table1 determines its Col1 value.
>
> I may have more columns in tables 2 and 3 in the future that
> I'd need to check on as well.
>
>
> TIA!
>
>
>
>> More information is needed in order to give an decent
>> answer; i.e., exactly what do you mean by whole "...based
>> on these..." discussion?
>>
>> Breck
>>
>> On 15 Apr 2008 11:35:00 -0700, Bala Venkata wrote:
>>
>> >
>> >I have three tables that are linked as below:
>> >
>> >Table1 (ID1, Col1)
>> >
>> >Table2 (ID2, ID1, Col21)
>> >
>> >Table3 (ID3, ID2, Col31)
>> >
>> >Table2 has a FK relationship to Table1. Table3 has FK
>> >relationship to Table2.
>> >
>> >For each row in Table1, I need calculate & set the Col1
>> >value based on these
>> >- Col21 values associated with that row (need to compare
>> the >value of Col21 among all rows)
>> >- Col31 values associated with the row in Table1
>> >- Number of rows of Table3 associated with the row in
>> Table1 >
>> >1. Do I need to create a function for this or can this be
>> >done via a simple UPDATE SQL statement
>> >2. If a function is needed should this be done in a loop
>> >that processes the values one row of Table1 at a time
>>
>> --
>> Breck Carter http://sqlanywhere.blogspot.com/
>>
>> RisingRoad SQL Anywhere and MobiLink Professional Services
>> breck.carter@risingroad.com


Shuchit Posted on 2008-04-16 17:54:25.0Z
Newsgroups: ianywhere.public.general
Subject: Re: How do I update column in a table linked to other tables
From: Shuchit <me@privacy.net>
References: <4804f554.293.1681692777@sybase.com>
Message-ID: <Xns9A825E9078A63svelkarprivacynet@127.0.0.1>
User-Agent: Xnews/2006.08.24 Hamster/2.1.0.11
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 16 Apr 2008 10:54:25 -0700
X-Trace: forums-1-dub 1208368465 10.22.241.152 (16 Apr 2008 10:54:25 -0700)
X-Original-Trace: 16 Apr 2008 10:54:25 -0700, vip152.sybase.com
Lines: 14
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6818
Article PK: 5055


> Table2 has a FK relationship to Table1. Table3 has FK
> relationship to Table2.
>
...snip...
>
> 1. Do I need to create a function for this or can this be
> done via a simple UPDATE SQL statement
> 2. If a function is needed should this be done in a loop
> that processes the values one row of Table1 at a time

Have you looked at using "on update cascade" clause when the Foreign Key
relationship is declared ?

Shuchit


Bala Venkata Posted on 2008-04-17 19:58:03.0Z
Sender: 24c.4804f186.1804289383@sybase.com
From: Bala Venkata
Newsgroups: ianywhere.public.general
Subject: Re: How do I update column in a table linked to other tables
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4807abcb.3b93.1681692777@sybase.com>
References: <Xns9A825E9078A63svelkarprivacynet@127.0.0.1>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 17 Apr 2008 12:58:03 -0700
X-Trace: forums-1-dub 1208462283 10.22.241.41 (17 Apr 2008 12:58:03 -0700)
X-Original-Trace: 17 Apr 2008 12:58:03 -0700, 10.22.241.41
Lines: 30
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6822
Article PK: 5060


> > Table2 has a FK relationship to Table1. Table3 has FK
> > relationship to Table2.
> >
> ...snip...
> >
> > 1. Do I need to create a function for this or can this
> > be done via a simple UPDATE SQL statement
> > 2. If a function is needed should this be done in a loop
> > that processes the values one row of Table1 at a time
>
> Have you looked at using "on update cascade" clause when
> the Foreign Key relationship is declared ?
>

Yes, I thought about it. But I wasn't sure what the impact
of doing multiple 'update's on the main table (one 'update'
possibly for each change on the affected table). Besides,
one of my conditions was setting the main column table when
the linked table was empty (this can be worked around by
init'ing the column I think).

Btw, I guess these are strictly sql-related questions at
this point. Let me know if they can be best answered
elsewhere


THanks again for the time !

/bala