SQL Query Question

17 Sep 2005 8:24 AM

Peri

I am having 3 tables Table1, Table2 and Table3 with the following schema and
values

CREATE TABLE Table1
(TableName VARCHAR(500),
FieldName VARCHAR(500),
UID INT,
TransactionNo INT)

GO

INSERT INTO TABLE1 (TableName, FieldName, UID, TransactionNo)
VALUES ('TABLE2', 'TABLE2.SecCode', 1, 100)

GO

CREATE TABLE Table2
(SecCode VARCHAR(500),
TransactionNo INT)

GO

INSERT INTO Table2 (SecCode, TransactionNo)
VALUES ('IBM', 100)

GO

CREATE TABLE Table3
(FieldValue VARCHAR(500),
UID INT,
TransactionNo INT)

GO

INSERT INTO TABLE3 (FieldValue, UID, TransactionNo)
VALUES ('', 1, 100)
GO

SELECT * FROM TABLE1
SELECT * FROM TABLE2
SELECT * FROM TABLE3

My objective is to insert IBM to FieldValue of TABLE3 using a single sql
query.

The logic I have to use is, from TABLE3 I will have to take the UID->get the
TableName (TABLE2) and FieldName (SecCode) from TABLE1 and get the values
from the TABLE2 (IBM) using the TransactionNo as the link.

A Sql statement like this (This sql statement will not work)
UPDATE TABLE3 SET Table3.FieldValue = TABLE1.FieldName
FROM TABLE3, TABLE1, TABLE1.TableName
WHERE TABLE3.UID = TABLE1.UID AND
TABLE1.TransactionNo = TABLE1.TableName.TransactionNo

--Where -> TABLE1.TableName = TABLE2, TABLE1.FieldName = TABLE2.SecCode
(IBM), TABLE1.TableName.TransactionNo = 100

I know this can be achieved using a cursor and setting the table name and
field names to a variable. Is there any possiblity of writing a single SQL
query ?

--
Thanks and Regards,

Peri

17 Sep 2005 5:59 PM

Damien

Try this:

BEGIN TRAN

UPDATE t3
SET FieldValue = t2.SecCode
FROM table1 t1
    INNER JOIN table2 t2 ON t1.TransactionNo = t2.TransactionNo
    INNER JOIN table3 t3 ON t1.uid = t3.uid
        AND t1.TransactionNo = t3.TransactionNo
        AND t2.TransactionNo = t3.TransactionNo

SELECT * FROM table3

ROLLBACK TRAN

I must warn you I can't tell how this query is going to behave with more
records in your tables so, TEST IT THOROUGHLY! That's why I've wrapped in a
transaction and rolled it back. If you're happy with the results, then
comment out the BEGIN TRAN / ROLLBACK TRAN statements.

Let me know how you get on.

Damien

Show quote

"Peri" wrote:

> I am having 3 tables Table1, Table2 and Table3 with the following schema and
> values
>
> CREATE TABLE Table1
> (TableName VARCHAR(500),
> FieldName VARCHAR(500),
> UID INT,
> TransactionNo INT)
>
> GO
>
> INSERT INTO TABLE1 (TableName, FieldName, UID, TransactionNo)
> VALUES ('TABLE2', 'TABLE2.SecCode', 1, 100)
>
> GO
>
> CREATE TABLE Table2
> (SecCode VARCHAR(500),
> TransactionNo INT)
>
> GO
>
> INSERT INTO Table2 (SecCode, TransactionNo)
> VALUES ('IBM', 100)
>
> GO
>
> CREATE TABLE Table3
> (FieldValue VARCHAR(500),
> UID INT,
> TransactionNo INT)
>
> GO
>
> INSERT INTO TABLE3 (FieldValue, UID, TransactionNo)
> VALUES ('', 1, 100)
> GO
>
> SELECT * FROM TABLE1
> SELECT * FROM TABLE2
> SELECT * FROM TABLE3
>
> My objective is to insert IBM to FieldValue of TABLE3 using a single sql
> query.
>
> The logic I have to use is, from TABLE3 I will have to take the UID->get the
> TableName (TABLE2) and FieldName (SecCode) from TABLE1 and get the values
> from the TABLE2 (IBM) using the TransactionNo as the link.
>
> A Sql statement like this (This sql statement will not work)
> UPDATE TABLE3 SET Table3.FieldValue = TABLE1.FieldName
> FROM TABLE3, TABLE1, TABLE1.TableName
> WHERE TABLE3.UID = TABLE1.UID AND
> TABLE1.TransactionNo = TABLE1.TableName.TransactionNo
>
> --Where -> TABLE1.TableName = TABLE2, TABLE1.FieldName = TABLE2.SecCode
> (IBM), TABLE1.TableName.TransactionNo = 100
>
> I know this can be achieved using a cursor and setting the table name and
> field names to a variable. Is there any possiblity of writing a single SQL
> query ?
>
> --
> Thanks and Regards,
>
> Peri
>
>
>

17 Sep 2005 7:22 PM

Peri

Thanks for you reply. But I want something like this:

Here t2.SecCode is present in t1.FieldName and table2 is present in the
field table1.TableName and t2.Tranasction number should be
table1.TableName.TransactionNo. FYI - The table name and field name for
fetching the value is present in another table.

Is there any other way out ?

--
Thanks and Regards,

Peri

Show quote

"Damien" <Dam***@discussions.microsoft.com> wrote in message
news:147AE63C-098F-45DD-B939-45FAA345CC99@microsoft.com...
> Try this:
>
> BEGIN TRAN
>
> UPDATE t3
> SET FieldValue = t2.SecCode
> FROM table1 t1
> INNER JOIN table2 t2 ON t1.TransactionNo = t2.TransactionNo
> INNER JOIN table3 t3 ON t1.uid = t3.uid
> AND t1.TransactionNo = t3.TransactionNo
> AND t2.TransactionNo = t3.TransactionNo
>
> SELECT * FROM table3
>
> ROLLBACK TRAN
>
> I must warn you I can't tell how this query is going to behave with more
> records in your tables so, TEST IT THOROUGHLY! That's why I've wrapped in
a
> transaction and rolled it back. If you're happy with the results, then
> comment out the BEGIN TRAN / ROLLBACK TRAN statements.
>
> Let me know how you get on.
>
>
> Damien
>
> "Peri" wrote:
>
> > I am having 3 tables Table1, Table2 and Table3 with the following schema
and
> > values
> >
> > CREATE TABLE Table1
> > (TableName VARCHAR(500),
> > FieldName VARCHAR(500),
> > UID INT,
> > TransactionNo INT)
> >
> > GO
> >
> > INSERT INTO TABLE1 (TableName, FieldName, UID, TransactionNo)
> > VALUES ('TABLE2', 'TABLE2.SecCode', 1, 100)
> >
> > GO
> >
> > CREATE TABLE Table2
> > (SecCode VARCHAR(500),
> > TransactionNo INT)
> >
> > GO
> >
> > INSERT INTO Table2 (SecCode, TransactionNo)
> > VALUES ('IBM', 100)
> >
> > GO
> >
> > CREATE TABLE Table3
> > (FieldValue VARCHAR(500),
> > UID INT,
> > TransactionNo INT)
> >
> > GO
> >
> > INSERT INTO TABLE3 (FieldValue, UID, TransactionNo)
> > VALUES ('', 1, 100)
> > GO
> >
> > SELECT * FROM TABLE1
> > SELECT * FROM TABLE2
> > SELECT * FROM TABLE3
> >
> > My objective is to insert IBM to FieldValue of TABLE3 using a single
sql
> > query.
> >
> > The logic I have to use is, from TABLE3 I will have to take the UID->get
the
> > TableName (TABLE2) and FieldName (SecCode) from TABLE1 and get the
values
> > from the TABLE2 (IBM) using the TransactionNo as the link.
> >
> > A Sql statement like this (This sql statement will not work)
> > UPDATE TABLE3 SET Table3.FieldValue = TABLE1.FieldName
> > FROM TABLE3, TABLE1, TABLE1.TableName
> > WHERE TABLE3.UID = TABLE1.UID AND
> > TABLE1.TransactionNo = TABLE1.TableName.TransactionNo
> >
> > --Where -> TABLE1.TableName = TABLE2, TABLE1.FieldName = TABLE2.SecCode
> > (IBM), TABLE1.TableName.TransactionNo = 100
> >
> > I know this can be achieved using a cursor and setting the table name
and
> > field names to a variable. Is there any possiblity of writing a single
SQL
> > query ?
> >
> > --
> > Thanks and Regards,
> >
> > Peri
> >
> >
> >

18 Sep 2005 2:10 AM

--CELKO--

>> Is there any other way out ? <<

Redesign your schema properly. One of the most important rules for
programming in SQL is NEVER mix data and metadata in the schema.

Also, do you know that the proprietary UPDATE.. FROM syntax is
unpredictable?

18 Sep 2005 10:23 PM

Erland Sommarskog

Peri (Peri@newsgroups.nospam) writes:
Show quote

> The logic I have to use is, from TABLE3 I will have to take the UID->get
> the TableName (TABLE2) and FieldName (SecCode) from TABLE1 and get the
> values from the TABLE2 (IBM) using the TransactionNo as the link.
>
> A Sql statement like this (This sql statement will not work)
> UPDATE TABLE3 SET Table3.FieldValue = TABLE1.FieldName
> FROM TABLE3, TABLE1, TABLE1.TableName
> WHERE TABLE3.UID = TABLE1.UID AND
> TABLE1.TransactionNo = TABLE1.TableName.TransactionNo
>
> --Where -> TABLE1.TableName = TABLE2, TABLE1.FieldName = TABLE2.SecCode
> (IBM), TABLE1.TableName.TransactionNo = 100
>
> I know this can be achieved using a cursor and setting the table name and
> field names to a variable. Is there any possiblity of writing a single SQL
> query ?

Here is a query that gives an outline of what you can do. Extended to many
tables and columns, it will get painful:

   UPDATE TABLE3
   SET    FieldValue = CASE WHEN T1.TableName = 'TABLE2' AND
                                 T1.FieldName = 'TABLE2.SecCode' THEN
                            (SELECT T2.SecCode
                             FROM   TABLE2 T2
                             WHERE T2.TransactionNo = T1.TransactionNo)
                            ELSE T3.FieldValue
                       END
   FROM TABLE3 T3, TABLE1 T1
   WHERE T3.UID = T1.UID

There might be all reason to review the table design.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp