Re: UPDATE - UNION

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Vinod Kumar (vinodk_sct_at_NO_SPAM_hotmail.com)
Date: 02/14/05 

Date: Mon, 14 Feb 2005 16:31:12 +0530

Here is my shot at the problem. I am still not clear but will give it my
shot ... You can change the query as:

SELECT id,col1 as MyAlias, 'Col1' as TargetCol FROM TABLE1
UNION ALL
SELECT id,col2 as MyAlias, 'Col2' as TargetCol FROM TABLE1
UNION ALL
SELECT id,col3 as MyAlias, 'Col3' as TargetCol FROM TABLE1

This way you know which is tha column data we are getting. And during the
update do it:

Update Table1
SET Col1 = CASE WHEN TargetCol = 'Col1' THEN <<Updated Value>> END,
Col2 = CASE WHEN TargetCol = 'Col2' THEN <<Updated Value>> END,
Col3 = CASE WHEN TargetCol = 'Col3' THEN <<Updated Value>> END
from #<OurTransposedTable>

PS: I am still not sure to how you update. Can you post the complete repro. 

-- 
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Bob" <Bob@discussions.microsoft.com> wrote in message
news:66BC7A32-59E9-468A-97B4-005A1992585E@microsoft.com...
> I am using SQL SERVER 2000 and transact-sql
>
> description:
>  my table
>    TABLE1 (id,col1,Col2,col3)
>     -> id = primary key
>  my query
>    SELECT id,col1 as MyAlias FROM TABLE1
>    UNION ALL
>    SELECT id,col2 as MyAlias FROM TABLE1
>    UNION ALL
>    SELECT id,col3 as MyAlias FROM TABLE1
>
> With this query, I can only update rows of the first query(SELECT id,col1
as
> MyAlias FROM TABLE1)
>
>
> I'm not english so I'm not sure that my post gives you answers you need.
>
> Thanks for your response
>
>
> "Vinod Kumar" wrote:
>
> > Can you give us more information on what your requirement is. If you can
> > post us the DDL and DML's with sample output it would be nice.
> >
> > Just a distant shot, if you are using something with a view then you can
> > take a look at INSTEAD OF triggers from BOL.
> >
> > -- 
> > HTH,
> > Vinod Kumar
> > MCSE, DBA, MCAD, MCSD
> > http://www.extremeexperts.com
> >
> > Books Online for SQL Server SP3 at
> > http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
> >
> > "Bob" <Bob@discussions.microsoft.com> wrote in message
> > news:3024D47A-C0C6-42BC-83DF-9CA377AFD495@microsoft.com...
> > > I made a query which uses 4 columns of the same table and displays it
in 4
> > rows
> > >
> > > ex : SELECT XX as Col1 FROM TABLE1
> > >       UNION ALL
> > >       SELECT YY as Col1 FROM TABLE1
> > >       .....
> > > I want these rows to be updatable but, I just can update rows of the
first
> > > query.
> > >
> > > How can I resolve this problem.
> >
> >
> >

Relevant Pages

  • Re: UPDATE - UNION
    ... SELECT id,col1 as MyAlias FROM TABLE1 ... UNION ALL ... With this query, I can only update rows of the first query(SELECT id,col1 as ...
    (microsoft.public.sqlserver.programming)
  • Re: Query - Crosstab ?
    ... You need the reverse of a crosstab, so perhaps a UNION query. ... SELECT Company, Product1 AS Product, "1" AS Source FROM Table1 ...
    (microsoft.public.access.queries)
  • Re: Synching Multiple Tables
    ... pull all the data from all the tables into one master table. ... Do you really need a master table, or can you simply create a UNION query ... FROM Table1 ...
    (microsoft.public.access.replication)
  • Re: Access Query
    ... UNION ALL SELECT * FROM Table3; ... Then the second query does the agregating: ... ANYTHING that would require the use of square brackets, ... FROM [SELECT * FROM Table1 ...
    (microsoft.public.access.formscoding)
  • Re: Crosstab or Pivot Frustration
    ... The first UNION query restructures you data into a normalized layout: ... SELECT Region, "Supply" AS Category, Supply AS Result FROM Table1 ... SELECT Region, "SoldOut" AS Category, SoldOut AS Result FROM Table1; ...
    (comp.databases.ms-access)