Query Update - subquery?

Hi,

I have one table name: art

column:

symbol_art price1 price2
----------- ------- -------
AG-0001 20 40
AG-0001S null null
AG-0002 40 60
AG-0002S null null
....
How paste in null price1 and price2 from oryginal symbol_art AG-0001,AG-0002 ?
(duplicate symbol_art %-%'S ' it's always the same for oryginal symbol_art)

thanks for any help
Tom

Re: Query Update - subquery?

On Dec 29, 12:11=A0pm, "info"
wrote:
> Hi,
>
> I have one table name: art
>
> column:
>
> symbol_art =A0 =A0 =A0 =A0 =A0price1 =A0 =A0price2
> ----------- =A0 =A0 =A0 =A0 =A0------- =A0 -------
> AG-0001 =A0 =A0 =A0 =A0 =A0 20 =A0 =A0 =A0 =A0 =A040
> AG-0001S =A0 =A0 =A0 =A0 null =A0 =A0 =A0 =A0null
> AG-0002 =A0 =A0 =A0 =A0 =A0 40 =A0 =A0 =A0 =A0 =A060
> AG-0002S =A0 =A0 =A0 =A0 null =A0 =A0 =A0 =A0null
> ...
> How paste in null price1 and price2 from oryginal symbol_art AG-0001,AG-00=
02 ?
> (duplicate symbol_art =A0%-%'S ' =A0it's always the same for oryginal symb=
ol_art)
>
> thanks for any help
> Tom


declare @t table(symbol_art varchar(100), price1 int,price2 int)
insert into @t
select 'AG-0001', 20, 40 union all
select 'AG-0001S', null, null union all
select 'AG-0002', 40 , 60 union all
select 'AG-0002S', null , null


update t1
set price1=3Dt2.price1,
price2=3Dt2.price2
from @t t1 inner join
(
select symbol_art,price1,price2 from @t where price1 is not null and
price2 is not null
) as t2
on t1.symbol_art like t2.symbol_art+'%'
where t1.price1 is null and t1.price2 is null

select * from @t

Re: Query Update - subquery?

On Sat, 29 Dec 2007 08:11:55 +0100, "info"
wrote:

>Hi,
>
>I have one table name: art
>
>column:
>
>symbol_art price1 price2
>----------- ------- -------
>AG-0001 20 40
>AG-0001S null null
>AG-0002 40 60
>AG-0002S null null
>...
>How paste in null price1 and price2 from oryginal symbol_art AG-0001,AG-0002 ?
>(duplicate symbol_art %-%'S ' it's always the same for oryginal symbol_art)
>
>thanks for any help
>Tom

As a side note, it should be understood that the way the symbol_art
column is being used it should have been two columns. The sort of
complications that result from not using two columns require a
complicated query. With two columns it would have been simple and
obvious.

Roy Harvey
Beacon Falls, CT

Re: Query Update - subquery?

>> How paste in null price1 and price2 from original symbol_art AG-0001,AG-0002 ? (duplicate symbol_art %-%'S ' it's always the same for original symbol_art) <<

Why don't you create a VIEW with the long and short art_symbols in
it? It will always be right, not require storage, save constant
updating, etc.