how to process each record of select results

4 Nov 2005 3:23 PM

vinod

Hi All,
I have some SQL query returns numbers of records. I want to process
each of this record.
consider the example
suppose my select query returns following results

col1      col2      col3
NY         1        <some value>
CA          2       <some value>
TN          3       ...........
....
.....

Now using SQL query i want to check col1 & col2 from these results for
each row & update col3
Could anyone guide how to go about this? Any site, book or example will
be great help.

Vinod

4 Nov 2005 3:31 PM

Tom Moreau

Please post your DDL and expected results.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com

Show quote

"vinod" <vinod.pat***@gmail.com> wrote in message
news:1131117814.213427.279970@g47g2000cwa.googlegroups.com...
> Hi All,
> I have some SQL query returns numbers of records. I want to process
> each of this record.
> consider the example
> suppose my select query returns following results
>
> col1      col2      col3
> NY         1        <some value>
> CA          2       <some value>
> TN          3       ...........
> ...
> ....
>
> Now using SQL query i want to check col1 & col2 from these results for
> each row & update col3
> Could anyone guide how to go about this? Any site, book or example will
> be great help.
>
> Vinod
>

4 Nov 2005 3:53 PM

vinod

Thaks for reply
Sorry i posted over simplified version.
The col name & values i have given in example is result of some complex
query
Let me explain you my basic query is

SELECT DISTINCT A.TBL_NM, B.COL_NM
FROM TBL_DEFINE_COL B
LEFT JOIN TBL_DEFINE A
ON A.CDTBL_ID = B.CDTBL_ID WHERE B.COL_NM

which returns list of table names & column names
tablel name            column name
TABLE1                   COL1
TABLE1                   COL2
TABLE2                    COL1
TABLE3                    COL3
not i want to find out for row whether the column is primary key column
of that table or not
means whether the COL1 column is primary key column of TABLE1 or not

Vinod

4 Nov 2005 4:11 PM

Tom Moreau

This query gives you a list of all columns participating in PK constraints:

select
*
from
information_schema.key_column_usage
where
1 in (objectproperty (object_id (constraint_name), 'CnstIsClustKey')
, objectproperty (object_id (constraint_name), 'CnstIsNonclustKey'))

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com

Show quote

"vinod" <vinod.pat***@gmail.com> wrote in message
news:1131119590.344451.159770@g14g2000cwa.googlegroups.com...
> Thaks for reply
> Sorry i posted over simplified version.
> The col name & values i have given in example is result of some complex
> query
> Let me explain you my basic query is
>
> SELECT DISTINCT A.TBL_NM, B.COL_NM
> FROM TBL_DEFINE_COL B
> LEFT JOIN TBL_DEFINE A
> ON A.CDTBL_ID = B.CDTBL_ID WHERE B.COL_NM
>
> which returns list of table names & column names
> tablel name            column name
> TABLE1                   COL1
> TABLE1                   COL2
> TABLE2                    COL1
> TABLE3                    COL3
> not i want to find out for row whether the column is primary key column
> of that table or not
> means whether the COL1 column is primary key column of TABLE1 or not
>
> Vinod
>

4 Nov 2005 4:24 PM

David Portas

Try the following (untested):

SELECT DISTINCT A.tbl_nm, B.col_nm,
CASE WHEN K.column_name IS NOT NULL
THEN 'Y' ELSE 'N' END AS is_primary_key
FROM tbl_define_col AS B
LEFT JOIN tbl_define AS A
ON A.cdtbl_id = B.cdtbl_id
LEFT JOIN information_schema.table_constraints AS C
ON C.constraint_type = 'PRIMARY KEY'
   AND A.tbl_nm = C.table_name
LEFT JOIN information_schema.key_column_usage AS K
ON K.table_name = C.table_name
   AND K.constraint_name = C.constraint_name
   AND K.column_name = B.col_nm ;

--
David Portas
SQL Server MVP
--

4 Nov 2005 4:41 PM

vinod

Thanks a lot David !!!
Thats what i was looking for

5 Nov 2005 1:47 AM

HS_Ted

Show quote

"David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message
news:1131121459.372782.267180@g14g2000cwa.googlegroups.com...
> Try the following (untested):
>
> SELECT DISTINCT A.tbl_nm, B.col_nm,
> CASE WHEN K.column_name IS NOT NULL
> THEN 'Y' ELSE 'N' END AS is_primary_key
> FROM tbl_define_col AS B
> LEFT JOIN tbl_define AS A
> ON A.cdtbl_id = B.cdtbl_id
> LEFT JOIN information_schema.table_constraints AS C
> ON C.constraint_type = 'PRIMARY KEY'
>   AND A.tbl_nm = C.table_name
> LEFT JOIN information_schema.key_column_usage AS K
> ON K.table_name = C.table_name
>   AND K.constraint_name = C.constraint_name
>   AND K.column_name = B.col_nm ;
>
> --
> David Portas
> SQL Server MVP
> --
>

I sometimes think I have a good handle on T-Sql. Then David makes
a post and I realize it's best to hang myself.

Damn.

4 Nov 2005 3:31 PM

David Portas

When you say "update col3", do you mean you want to update the value in
the original table? If so there's no need to query and then process
each row. In fact the point of SQL and relational databases is that you
can operate on whole sets of rows at once. In this case it looks like
you'll just need a single UPDATE:

UPDATE your_table
SET col3 = /* unspecified */
WHERE ...

Unfortunately you haven't given us enough information to explain what
goes after the =.

If you need more help please come back with a better spec. See:
http://www.aspfaq.com/etiquette.asp?id=5006

Hope this helps.

--
David Portas
SQL Server MVP
--