Query Update - subquery?

[info]

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

[Madhivanan]

Re: Query Update - subquery?

On Dec 29, 12:11 pm, "info" <informatyk@fit ness[CUT]authority.pl>
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

declare @t table(symbol_ar t 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=t2.price 1,
price2=t2.price 2
from @t t1 inner join
(
select symbol_art,pric e1,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

[Roy Harvey (SQL Server MVP)]

Re: Query Update - subquery?

On Sat, 29 Dec 2007 08:11:55 +0100, "info"
<informatyk@fit ness[CUT]authority.plwro te:

>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

[--CELKO--]

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.