Advanced Query - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Advanced Query

Author

Topic

lane0618
Posting Yak Master

134 Posts

Posted - 2002-05-15 : 18:19:53

I have a table that associates an item with a ptdraw field. The problem is that the ptdraw is only specified for items with -000. It looks like this:

ITEM PTDRAW
11963-000 5011937
11963-001
11963-002
11963-003
11963-004
11963-005
11963-006
11963-007
11963-008

I want it to look like this:

ITEM PTDRAW PTDRAW2
11963-000 5011937 5011937
11963-001 5011937
11963-002 5011937
11963-003 5011937
11963-004 5011937
11963-005 5011937
11963-006 5011937
11963-007 5011937
11963-008 5011937

I cant update the table, so I need a query or stored procedure that gives me the list above.

Thanks!
Lane

Edited by - lane0618 on 05/15/2002 18:21:59

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-15 : 18:36:05

UPDATE P1
SET P1.PTDRAW=P2.PTDRAW
FROM myTable P1 INNER JOIN myTable P2 ON Left(P1.ITEM, 5) + '-000'=P2.ITEM
WHERE Right(P1.ITEM, 4)<>'-000' AND Right(P2.ITEM, 4)='-000'
AND P1.PTDRAW Is Null

Sorry, didn't see the part about not updating:

SELECT P1.ITEM, P2.PTDRAW
FROM myTable P1 INNER JOIN myTable P2 ON Left(P1.ITEM, 5) + '-000'=P2.ITEM

Edited by - robvolk on 05/15/2002 18:40:27

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-05-15 : 18:41:12

Um Rob, you better turn that into a view!

quote:

I cant update the table

DavidM

"SQL-3 is an abomination.."

lane0618
Posting Yak Master

134 Posts

Posted - 2002-05-15 : 19:00:34

Thanks, but the empty ptdraw fields are still not filled in with the value from the items with -000. What am I doing wrong?

SELECT P1.ITEM, P1.PTDRAW
FROM dbo.ANA P1 INNER JOIN
dbo.ANA P2 ON LEFT(P1.ITEM, 5) + '-000' = P2.ITEM

Thanks very much for your help!
Lane

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-15 : 19:17:24

You're pulling from P1 in both columns:

SELECT P1.ITEM, P1.PTDRAW
FROM dbo.ANA P1 INNER JOIN
dbo.ANA P2 ON LEFT(P1.ITEM, 5) + '-000' = P2.ITEM

This is what you want:

SELECT P1.ITEM, P2.PTDRAW
FROM dbo.ANA P1 INNER JOIN
dbo.ANA P2 ON LEFT(P1.ITEM, 5) + '-000' = P2.ITEM

lane0618
Posting Yak Master

134 Posts

Posted - 2002-05-15 : 19:39:32

That does the trick! Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources