Select: copy field content from previous row

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select: copy field content from previous row

Author

Topic

Peter01
Starting Member

16 Posts

Posted - 2014-08-12 : 19:58:15

Hello SQLTeam,

I´m fairly new to SQL and I´m puzzle with the query below. Please help!
Best regards,
Peter

I have 1 table with some empty product codes that I have imported from a txt file. And I would like to fill in the empty product codes by taking the previous product code

date product_code
01/01/14 | BIC01 |
03/01/14 | |
01/01/14 | CASH |
04/01/14 | BIC01 |
02/01/14 | CASH |
05/01/14 | |
03/01/14 | CASH |

I though of using the LAG function but the table does not have any specific order.
So after running the select, the table should look like:

date product_code
01/01/14 | BIC01 |
03/01/14 | BIC01 |
01/01/14 | CASH |
04/01/14 | BIC01 |
02/01/14 | CASH |
05/01/14 | CASH |
03/01/14 | CASH |

Can anyone help, please?
Thanks,
Pater

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-13 : 07:40:18

You need to have some ordering, or the effect of your query (whatever you work up) will be unpredictable.

Peter01
Starting Member

16 Posts

Posted - 2014-08-13 : 13:03:14

quote:
Originally posted by gbritton

You need to have some ordering, or the effect of your query (whatever you work up) will be unpredictable.

Peter01
Starting Member

16 Posts

Posted - 2014-08-13 : 16:37:44

Many thanks Gbritton.

I followed your advised and added a new column with the desired order:

alter table Products
add ID_linea int identity(1,1)

and now my table has 3 columns:

date | product_code | ID_Linea
01/01/14 | BIC01 |1
03/01/14 | |2
01/01/14 | |3
04/01/14 | BIC01 |4
02/01/14 | CASH |5
05/01/14 | |6
03/01/14 | CASH |7
But I still don´t know how to write a select to fill in the empty product_code with the previous product_code.
Can anyone help, please?
Peter

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-13 : 17:18:26

This might work for you:


declare @t table (date date, product_code varchar(10), ID_Linea int)
insert into @t(date, product_code, ID_Linea) values
('01/01/14 ',' BIC01 ',1),
('03/01/14 ',' ',2),
('01/01/14 ',' ',3 ),
('04/01/14 ',' BIC01 ',4 ),
('02/01/14 ',' CASH ',5),
('05/01/14 ',' ',6),
('03/01/14 ',' CASH ',7)

update t1
set product_code = t2.product_code
from @t t1
cross apply (
	select top (1) * from @t t2
	where t1.ID_Linea > t2.ID_Linea
	and t2.product_code <> ''
	order by t2.ID_Linea desc) t2

select * from @t

Peter01
Starting Member

16 Posts

Posted - 2014-08-13 : 19:07:35

Bang On!!!
Many thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources