Update with joins? - 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

Transact-SQL (2000)

Update with joins?

Author

Topic

Girlnet
Yak Posting Veteran

77 Posts

Posted - 2003-10-20 : 12:18:40

Here's the an select statement that works beautifully (thanks to Brett). Can I make this an update statement?

Instead of the Select, I want to do an update. Something like . . .

UPDATE SPE_PLAN_DTL SET ORDERQTY=((B.SCRAP/1000)+1)*(B.AMT * A.QTY)???

SELECT ((B.SCRAP/1000)+1)*(B.AMT * A.QTY) AS ORDERQTY,B.LN_NO,B.ROW_ID,B.T_ID

FROM (
SELECT QTY,ROW_ID,MOHEADERKEY
FROM SPE_PLAN_DTL
WHERE LN_NO IS NOT NULL
)
AS A
LEFT JOIN
( SELECT ROW_ID,QTY AS AMT,SCRAP,T_ID,LN_NO
FROM SPE_PLAN_DTL B
WHERE LN_NO IS NULL
)
AS B

ON B.ROW_ID = A.ROW_ID WHERE A.MOHEADERKEY=@ROW

Nothing I do seems to work. Thanks much for any assistance!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-20 : 12:23:23

Here is how to do an UPDATE statement with joins:

UPDATE Table1
SET a.Column2 = b.Column2
FROM Table1 a
INNER JOIN Table2 b ON a.Column1 = b.Column2
WHERE...

So, maybe something like this:

UPDATE SPE_PLAN_DTL
SET ORDERQTY = ((B.SCRAP/1000) + 1) * (B.AMT * A.QTY)
FROM (
SELECT QTY,ROW_ID,MOHEADERKEY
FROM SPE_PLAN_DTL
WHERE LN_NO IS NOT NULL
)
AS A
LEFT JOIN
( SELECT ROW_ID,QTY AS AMT,SCRAP,T_ID,LN_NO
FROM SPE_PLAN_DTL B
WHERE LN_NO IS NULL
)
AS B
ON B.ROW_ID = A.ROW_ID WHERE A.MOHEADERKEY = @ROW

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-20 : 12:34:16

Isn't ORDERQTY a derived column?

I didn't think it existed in your table....

Brett

8-)

Girlnet
Yak Posting Veteran

77 Posts

Posted - 2003-10-20 : 12:47:45

Thanks for your help Tara. That does work without producing any errors. And it was how I was trying to do it. The only thing is that all my ORDERQTY fields are updated with the same value, throughout the entire table. ???? I'm stumped. I've been looking at this all day.

Girlnet
Yak Posting Veteran

77 Posts

Posted - 2003-10-20 : 12:49:21

Hi Brett! I since have added the field to my existing table, trying to get this to work.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-20 : 13:00:52

To help you further, I would need DDL for tables, DML for sample data, and a sample of what the expected results should look like. DDL is CREATE TABLE statements for all of the tables involved in the query. DML is INSERT INTO statements for sample data.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-20 : 13:06:09

It's an extension of the following thread...

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=29788

And yes the DDL would help...but is T_ID an identity column?

But for a TOTAL shot in the dark...how about:


UPDATE SPE_PLAN_DTL SET ORDERQTY=xxx.ORDERQTY
FROM (
	SELECT ((B.SCRAP/1000)+1)*(B.AMT * A.QTY) AS ORDERQTY,B.LN_NO,B.ROW_ID,B.T_ID	
	     FROM ( SELECT QTY,ROW_ID,MOHEADERKEY
		      FROM SPE_PLAN_DTL 
		     WHERE LN_NO IS NOT NULL)AS A
	LEFT JOIN ( SELECT ROW_ID,QTY AS AMT,SCRAP,T_ID,LN_NO
		      FROM SPE_PLAN_DTL B
		     WHERE LN_NO IS NULL) AS B
	       ON B.ROW_ID = A.ROW_ID WHERE A.MOHEADERKEY=@ROW 
) AS XXX

WHERE T_ID = xxx.T_ID

Brett

8-)

Girlnet
Yak Posting Veteran

77 Posts

Posted - 2003-10-20 : 13:47:11

We are close . . . I can feel it. Good shot in the dark! It doesn't like my T_ID. T_ID is the identity column.

WHERE xxx.T_ID = T_ID

No matter what I put, I get "Ambiguous column name 'T_ID'"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-20 : 13:51:14

WHERE SPE_PLAN_DTL.T_ID = xxx.T_ID or maybe WHERE xxx.T_ID = SPE_PLAN_DTL.T_ID

Tara

Girlnet
Yak Posting Veteran

77 Posts

Posted - 2003-10-20 : 14:02:22

That's it! You are the bomb!!!

Works like a charm.

Girlnet
Yak Posting Veteran

77 Posts

Posted - 2003-10-20 : 14:05:05

Sorry! Forgot to say . . . YAHOOOOOOOO!!!!!!!!!!!!!! And thanks guys!!

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-20 : 14:06:30

Tammy,

I would say that if you're starting development, you should normalize your data now...

Your problems will become much more expansive in the future...

and with that said (the disclaimer thing)...we'll still be here...

Alway like a challenge...

Brett

8-)

Girlnet
Yak Posting Veteran

77 Posts

Posted - 2003-10-20 : 14:28:03

I know what you're saying Brett. And I'm putting it all through a re-think.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources