April 18, 2008 at 12:49 pm
I have the below data in a table called say "kipp".
I am trying to get a script to work that will look at the proj_id base (which is the 5 digit proj_id, 11293), if that base id has a desc, then fill that desc in on the below proj_id's that belong to the base (ie. 11293.000,11293.000.00...) and so on... I cant seem to figure it out as a newbie to SQL Server. Any Help would be greatly appreciated.
Thanks!!
proj_id desc
------------------------------ ------------------------------
11293 Description1
11293.000
11293.000.00
11293.001
11293.001.01
11293.001.02
11294 Description2
11294.000
11294.000.00
11294.001
11294.001.01
11294.001.02
April 18, 2008 at 1:41 pm
update dbo.kipp
set [desc] = (select distinct b.[desc] from dbo.kipp b where substring(a.proj_id, 1,5) = b.proj_id )
from dbo.kipp a
Try that, Others might have a better way
April 18, 2008 at 2:17 pm
xuanly (4/18/2008)
update dbo.kippset [desc] = (select distinct b.[desc] from dbo.kipp b where substring(a.proj_id, 1,5) = b.proj_id )
from dbo.kipp a
Try that, Others might have a better way
And here is another way, along with test code:
create table dbo.kipp (
proj_id varchar(25) not null,
proj_desc varchar(25) null
);
insert into dbo.kipp (proj_id, proj_desc)
select '11293 ','Description1' union all
select '11293.000 ','' union all
select '11293.000.00','' union all
select '11293.001 ','' union all
select '11293.001.01','' union all
select '11293.001.02','' union all
select '11294 ','Description2' union all
select '11294.000 ','' union all
select '11294.000.00','' union all
select '11294.001 ','' union all
select '11294.001.01','' union all
select '11294.001.02','';
select * from dbo.kipp;
with BaseProjects (
proj_id,
proj_desc
) as (
select
proj_id,
proj_desc
from
dbo.kipp
where
len(proj_id) = 5
and len(isnull(proj_desc,'')) > 0
)
update dbo.kipp set
proj_desc = bp.proj_desc
from
BaseProjects bp
inner join dbo.kipp k
on (bp.proj_id = left(k.proj_id,5))
where
bp.proj_desc <> k.proj_desc
select * from dbo.kipp;
drop table dbo.kipp
😎
April 21, 2008 at 5:11 am
Hello, thanks the input, I tried it this morning and it is givng me an error, that I am not sure how to deal with....
Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'BaseProjects'.
below is what I have:
SELECT proj_id,fund_srce_desc INTO #KIPP
FROM PROJ_GOVT_CONTR
order by proj_id
select * from #KIPP
with BaseProjects (
proj_id,
fund_srce_desc
) as (
select
proj_id,
fund_srce_desc
from
#KIPP
where
len(proj_id) = 5
and len(isnull(proj_desc,'')) > 0
)
update #KIPP set
fund_srce_desc = bp.fund_srce_desc
from
BaseProjects bp
inner join fund_srce_desc k
on (bp.proj_id = left(k.proj_id,5))
where
bp.proj_desc <> k.proj_desc
select * from #KIPP;
--drop table kipp
April 21, 2008 at 5:53 am
hey there... tahnsk for your help... I posted the result as I was getting an error... Sure it is something silly, I just cant figure it out. Would you mind looking at it and see if you can figure out what went wrong?
Thanks in Advance.
April 21, 2008 at 6:43 am
kipp (4/21/2008)
Hello, thanks the input, I tried it this morning and it is givng me an error, that I am not sure how to deal with....Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'BaseProjects'.
below is what I have:
SELECT proj_id,fund_srce_desc INTO #KIPP
FROM PROJ_GOVT_CONTR
order by proj_id
select * from #KIPP
with BaseProjects (
proj_id,
fund_srce_desc
) as (
select
proj_id,
fund_srce_desc
from
#KIPP
where
len(proj_id) = 5
and len(isnull(proj_desc,'')) > 0
)
update #KIPP set
fund_srce_desc = bp.fund_srce_desc
from
BaseProjects bp
inner join fund_srce_desc k
on (bp.proj_id = left(k.proj_id,5))
where
bp.proj_desc <> k.proj_desc
select * from #KIPP;
--drop table kipp
below is what I have:
SELECT proj_id,fund_srce_desc INTO #KIPP
FROM PROJ_GOVT_CONTR
order by proj_id
select * from #KIPP; <<<---- missing a ; here.
with BaseProjects (
proj_id,
fund_srce_desc
) as (
select
proj_id,
fund_srce_desc
from
#KIPP
where
len(proj_id) = 5
and len(isnull(proj_desc,'')) > 0
)
update #KIPP set
fund_srce_desc = bp.fund_srce_desc
from
BaseProjects bp
inner join fund_srce_desc k
on (bp.proj_id = left(k.proj_id,5))
where
bp.proj_desc <> k.proj_desc
select * from #KIPP;
--drop table kipp
April 21, 2008 at 7:07 am
Try this:
SELECT
proj_id,
fund_srce_desc
INTO
#KIPP
FROM
PROJ_GOVT_CONTR
order by
proj_id
select
*
from
#KIPP;
with BaseProjects (
proj_id,
fund_srce_desc
) as (
select
proj_id,
fund_srce_desc
from
#KIPP
where
len(proj_id) = 5
and len(isnull(proj_desc,'')) > 0
)
update #KIPP set
fund_srce_desc = bp.fund_srce_desc
from
BaseProjects bp
inner join fund_srce_desc k
on (bp.proj_id = left(k.proj_id,5))
where
bp.proj_desc <> k.proj_desc;
select * from #KIPP;
drop table #kipp;
April 21, 2008 at 7:12 am
hey Lynn, thanks... for some reason it gives me an error on the "with"...
Msg 156, Level 15, State 1, Line 16
Incorrect syntax near the keyword 'with'
I am not sure why?
April 21, 2008 at 7:15 am
What version of SQL Server are you using?
April 21, 2008 at 7:20 am
I sm on SQL Server 8.0.2039
April 21, 2008 at 7:22 am
That's why. I provided a SQL Server 2005 solution since you posted your request for help in a SQL Server 2005 forum. CTE's are not available in SQL Server 2000.
April 21, 2008 at 7:24 am
oops...
Is there any help you can provide me with this or do I have to repost?
April 21, 2008 at 7:26 am
is there a forum for 2000?
April 21, 2008 at 7:26 am
Try this:
SELECT
proj_id,
fund_srce_desc
INTO
#KIPP
FROM
PROJ_GOVT_CONTR
order by
proj_id
select
*
from
#KIPP;
update #KIPP set
fund_srce_desc = bp.fund_srce_desc
from
( select
proj_id,
fund_srce_desc
from
#KIPP
where
len(proj_id) = 5
and len(isnull(proj_desc,'')) > 0
) bp
inner join fund_srce_desc k
on (bp.proj_id = left(k.proj_id,5))
where
bp.proj_desc <> k.proj_desc;
select * from #KIPP;
drop table #kipp;
April 21, 2008 at 7:37 am
I am so sorry to keep bugging you with my lack of knowledge...
is there supposed to be something by the 😉 below?
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply