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
 SQL Server 2005 Forums
 Transact-SQL (2005)
 [Resolved] Update table w records from same table

Author  Topic 

snufse
Constraint Violating Yak Guru

469 Posts
Posted - 2009-07-30 : 08:57:39
I have a query that populates a temp table.


I have the situation where the field "itemname" in some cases do not have a value (ie no value exsists in the data base). This applies to records where the field "costcode" has a "-" as part of the value (see below data extract example)

01001   		Mobilization   		460.00		HR
01001-AV		NULL		        104.00		HR
01001-ID		NULL		        208.00		HR
01003   		GPS Model/Base 	        10.00		HR
01007   		Wells/pumps    		9.00		HR
01007-ID		NULL		        16.00		HR
01010   		Project Manager		3784.00		HR
01010-AV		NULL		        8.00		HR

Now, I would like to update the temp table with missing item names. The rule that would apply is as follows:
1. Get records that contains a "-" in the costcode and that have Null value in "itemname"
2. Update "itemname" for these records using "itemname" from another record in the same table.

Example: record 01001-AV is a candidate and "itemname" for this record will come from 01001, record 01001-ID is also a candidate and "itemname" will comde from 01001

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2009-07-30 : 09:03:17
[code]
UPDATE t
SET itemname = s.itemname
FROM yourtable t
INNER JOIN yourtable s ON t.custcode LIKE s.custcode + '%'
WHERE t.custcode LIKE '%-%'
AND t.itemname IS NULL
AND s.itemname IS NOT NULL
[/code]

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

snufse
Constraint Violating Yak Guru

469 Posts
Posted - 2009-07-30 : 09:43:00
Hello KH,

Have following:

UPDATE t
SET    jde_item_name  = s.jde_item_name
FROM   #JDE_EqmTable t
       INNER JOIN #Jde_EqmTable s ON t.jde_cost_code LIKE s.jde_cost_code + '%'
WHERE  t.jde_cost_code LIKE '%-%'
AND    t.jde_item_name IS NULL
AND    s.jde_item_name IS NOT NULL


Not update taking place, seems the join does not find a match
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2009-07-30 : 09:55:00
Looks ok to me


DECLARE @JDE_EqmTable TABLE
(
	jde_cost_code	varchar(10),
	jde_item_name	varchar(20),
	value		decimal(10,2),
	code		varchar(2)
)
INSERT INTO @JDE_EqmTable
SELECT	'01001',   		'Mobilization',   	460.00,		'HR' UNION ALL
SELECT	'01001-AV',		NULL,		        104.00,		'HR' UNION ALL
SELECT	'01001-ID',		NULL,		        208.00,		'HR' UNION ALL
SELECT	'01003',   		'GPS Model/Base', 	10.00,		'HR' UNION ALL
SELECT	'01007',   		'Wells/pumps',    	9.00,		'HR' UNION ALL
SELECT	'01007-ID',		NULL,		        16.00,		'HR' UNION ALL
SELECT	'01010',   		'Project Manager',	3784.00,	'HR' UNION ALL
SELECT	'01010-AV',		NULL,		        8.00,		'HR' 

UPDATE t
SET    jde_item_name  = s.jde_item_name
FROM   @JDE_EqmTable t
       INNER JOIN @JDE_EqmTable s ON t.jde_cost_code LIKE s.jde_cost_code + '%'
WHERE  t.jde_cost_code LIKE '%-%'
AND    t.jde_item_name IS NULL
AND    s.jde_item_name IS NOT NULL

SELECT	*
FROM	@JDE_EqmTable

/*
jde_cost_code jde_item_name        value        code 
------------- -------------------- ------------ ---- 
01001         Mobilization         460.00       HR
01001-AV      Mobilization         104.00       HR
01001-ID      Mobilization         208.00       HR
01003         GPS Model/Base       10.00        HR
01007         Wells/pumps          9.00         HR
01007-ID      Wells/pumps          16.00        HR
01010         Project Manager      3784.00      HR
01010-AV      Project Manager      8.00         HR

(8 row(s) affected)
*/


KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

snufse
Constraint Violating Yak Guru

469 Posts
Posted - 2009-07-30 : 11:08:40
Hi KH,

Tried your latest post and it worked.

When I try with my code it does not get a match on the join. Could the reason be that I am using #Jde_EqmTable and you are using @Jde_EqmTable?

CREATE TABLE	#JDE_EqmTable
		(  jde_cost_code nvarchar(15) unique,
                   jde_item_name char(15), 
                   jde_sum_hrs decimal(10,2),
                   jde_uom char(2)
                                       )

insert into #JDE_EqmTable(jde_cost_code, 
                          jde_sum_hrs,
                          jde_uom) 

Exec ('Call QGPL.get_eqmqty(?)', @JobNumber1) AT AS400SRV_MSDASQL

UPDATE t
SET    jde_item_name  = s.jde_item_name
FROM   #JDE_EqmTable t
       INNER JOIN #JDE_EqmTable s ON t.jde_cost_code LIKE s.jde_cost_code + '%'
WHERE  t.jde_cost_code LIKE '%-%'
AND    t.jde_item_name IS NULL
AND    s.jde_item_name IS NOT NULL

select * from #JDE_EqmTable

drop TABLE	#JDE_EqmTable
Go to Top of Page

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts
Posted - 2009-07-30 : 11:15:20
Can you have a SELECT * before you do the update and see if the data gets inserted properly. My guess is, the data in the # table is incorrect giving you trouble.

I just tried this...and it worked fine.

CREATE TABLE	#JDE_EqmTable
		(  jde_cost_code nvarchar(15) unique,
                   jde_item_name char(15), 
                   jde_sum_hrs decimal(10,2),
                   jde_uom char(2)
                                       )


insert into #JDE_EqmTable
SELECT	'01001',   		'Mobilization',   	460.00,		'HR' UNION ALL
SELECT	'01001-AV',		NULL,		        104.00,		'HR' UNION ALL
SELECT	'01001-ID',		NULL,		        208.00,		'HR' UNION ALL
SELECT	'01003',   		'GPS Model/Base', 	10.00,		'HR' UNION ALL
SELECT	'01007',   		'Wells/pumps',    	9.00,		'HR' UNION ALL
SELECT	'01007-ID',		NULL,		        16.00,		'HR' UNION ALL
SELECT	'01010',   		'Project Manager',	3784.00,	'HR' UNION ALL
SELECT	'01010-AV',		NULL,		        8.00,		'HR'


UPDATE t
SET    jde_item_name  = s.jde_item_name
FROM   #JDE_EqmTable t
       INNER JOIN #JDE_EqmTable s ON t.jde_cost_code LIKE s.jde_cost_code + '%'
WHERE  t.jde_cost_code LIKE '%-%'
AND    t.jde_item_name IS NULL
AND    s.jde_item_name IS NOT NULL


jde_cost_code   jde_item_name   jde_sum_hrs                             jde_uom
--------------- --------------- --------------------------------------- -------
01001           Mobilization    460.00                                  HR
01001-AV        Mobilization    104.00                                  HR
01001-ID        Mobilization    208.00                                  HR
01003           GPS Model/Base  10.00                                   HR
01007           Wells/pumps     9.00                                    HR
01007-ID        Wells/pumps     16.00                                   HR
01010           Project Manager 3784.00                                 HR
01010-AV        Project Manager 8.00                                    HR
Go to Top of Page

snufse
Constraint Violating Yak Guru

469 Posts
Posted - 2009-07-30 : 11:24:53
My select before the update:

01001   	Mobilization   	460.00	HR
01001-AV	NULL	        104.00	HR
01001-ID	NULL	        208.00	HR
01003   	GPS Model/Base 	10.00	HR
01007   	Wells/pumps    	9.00	HR
01007-ID	NULL	        16.00	HR
01010   	Project Manager	3784.00	HR
01010-AV	NULL	        8.00	HR
01010-ID	NULL	        389.00	HR
01011   	Milling In Hous	232.00	HR
01020   	Survey Crew    	4190.00	HR
01020-AV	NULL	        8.00	HR
01020-ID	NULL	        104.00	HR


Now when I try before the update also, I get no records:

select * 
FROM   #JDE_EqmTable t
       INNER JOIN #JDE_EqmTable s ON t.jde_cost_code LIKE s.jde_cost_code + '%'
WHERE  t.jde_cost_code LIKE '%-%'
AND    t.jde_item_name IS NULL
AND    s.jde_item_name IS NOT NULL


If I run this:

select t.jde_cost_code, s.jde_cost_code, t.jde_item_name, s.jde_item_name, t.jde_sum_hrs, s.jde_sum_hrs
FROM   #JDE_EqmTable t
       INNER JOIN #JDE_EqmTable s ON t.jde_cost_code LIKE s.jde_cost_code + '%'
WHERE  t.jde_cost_code LIKE '%-%'


I get:

01001-AV	01001-AV	NULL	NULL	104.00	104.00
01001-ID	01001-ID	NULL	NULL	208.00	208.00
01007-ID	01007-ID	NULL	NULL	16.00	16.00
01010-AV	01010-AV	NULL	NULL	8.00	8.00
01010-ID	01010-ID	NULL	NULL	389.00	389.00
01020-AV	01020-AV	NULL	NULL	8.00	8.00
01020-ID	01020-ID	NULL	NULL	104.00	104.00
01110-AV	01110-AV	NULL	NULL	72.00	72.00


I know the problem is on my side but I cannot find out where ...
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2009-07-30 : 11:41:58
can you try


select '[' + jde_cost_code + ']'
FROM   #JDE_EqmTable t


any extra character at the end ? maybe a CR / LF or some other characters ?

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

snufse
Constraint Violating Yak Guru

469 Posts
Posted - 2009-07-30 : 11:50:36
Tried:

select '[' + jde_cost_code + ']'
FROM   #JDE_EqmTable t


Result:

[01001   ]
[01001-AV]
[01001-ID]
[01003   ]
[01007   ]
[01007-ID]
[01010   ]
[01010-AV]
[01010-ID]
[01011   ]
[01020   ]
[01020-AV]


Changed this line of code (added rtrim)

INNER JOIN #JDE_EqmTable s ON t.jde_cost_code LIKE rtrim(s.jde_cost_code) + '%'

Now it worked fine.

Thank you all guys, sometimes data coming from db2/400 are tricky.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site