Need help on Update - 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 2005 Forums

Transact-SQL (2005)

Need help on Update

Author

Topic

chrpeter
Starting Member

31 Posts

Posted - 2008-03-10 : 17:12:07

I'm trying to do an update on a table, we'll call it order_line.
This has similar columns to a view I made,Ord_PO_vw.
Right now line_no has the same value as po_line
I need to update the line_no on the order_line table to be the same as ord_line on the view.
Given these conditions Order_line.order = Ord_PO_vw.order and Order_line.line_no = Ord_PO_vw.po_line

Here are a few lines from the table and view
order_line table
Order_no---line_no
22092-------1
22092-------2
22104-------1
22104-------2
22139-------1

Ord_PO_vw
Order_no--Ext------PO-------------po_line--ord_line
22092-----0--------PO000000071----1--------2
22092-----0--------PO000000071----2--------3
22104-----0--------PO000000012----1--------2
22104-----0--------PO000000012----2--------1
22139-----0--------PO000000023----1--------2

This is what I've tried
update a set a.line_no = b.ord_line from order_line a inner join Ord_PO_vw b on a.order_no = b.order_no and a.line_no = b.po_line
but I don't think this is correct.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-10 : 17:52:43

[code]
Update order_line
SET line_no = ord_line
FROM order_line a inner join ord_PO_vw b
on a.Order_no = b.Order_no and
a.line_no = b.PO_line

[/code]

looks correct...what output are you getting with yours?

Poor planning on your part does not constitute an emergency on my part.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-11 : 07:16:29

quote:
Originally posted by chrpeter

I'm trying to do an update on a table, we'll call it order_line.
This has similar columns to a view I made,Ord_PO_vw.
Right now line_no has the same value as po_line
I need to update the line_no on the order_line table to be the same as ord_line on the view.
Given these conditions Order_line.order = Ord_PO_vw.order and Order_line.line_no = Ord_PO_vw.po_line

Here are a few lines from the table and view
order_line table
Order---line_no
22092----1
22092----2
22104----1
22104----2
22139----1

Ord_PO_vw
Order--Ext------PO-------------po_line--ord_line
22092--0--------PO000000071----1--------2
22092--0--------PO000000071----2--------3
22104--0--------PO000000012----1--------2
22104--0--------PO000000012----2--------1
22139--0--------PO000000023----1--------2

This is what I've tried
update a set a.line_no = b.ord_line from order_line a inner join Ord_PO_vw b on a.order_no = b.order_no and a.line_no = b.po_line
but I don't think this is correct.

why do you say its incorrect? what result are you expecting & what are you getting?

chrpeter
Starting Member

31 Posts

Posted - 2008-03-11 : 09:52:41

Well I was using these select statements to check the results, and the numbers weren't adding up. So if the update looks right then these select statements are probably wrong.

OK This statement should return 548 results before the update as there are only 548 rows in the order_line table, and 0 after.
select b.*,a.order_no,a.line_no from order_line a inner join Ord_PO_vw b on a.order_no = b.order_no and a.line_no = b.po_line
order by b.order_no, b.po_no, b.po_line

This one should return 0 results before the update and 548 after.
select b.order_no, b.order_ext, b.po_no, b.po_line, b.ord_line, b.order_no,a.line_no from order_line a inner join Ord_PO_vw b on a.order_no = b.order_no and a.line_no = b.ord_line
order by b.order_no, b.po_no, b.po_line

EDIT: Well I think I realize why these aren't returning the correct number of results, it's because the view has more rows than the table, and I'm using an inner join. But when I change the joins to left outer joins I'm still not getting the correct results.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-11 : 09:57:07

quote:
Originally posted by chrpeter

This is what I've tried
update a set a.line_no = b.ord_line from order_line a inner join Ord_PO_vw b on a.order~~_no~~ = b.order~~_no~~ and a.line_no = b.po_line
but I don't think this is correct.

There are no "_no" in the order number columns.

E 12°55'05.25"
N 56°04'39.16"

chrpeter
Starting Member

31 Posts

Posted - 2008-03-11 : 10:03:47

quote:
Originally posted by Peso

quote:
Originally posted by chrpeter

This is what I've tried
update a set a.line_no = b.ord_line from order_line a inner join Ord_PO_vw b on a.order~~_no~~ = b.order~~_no~~ and a.line_no = b.po_line
but I don't think this is correct.

There are no "_no" in the order number columns.

E 12°55'05.25"
N 56°04'39.16"

That's my fault, there are "_no"'s in those column names, I'll edit my original post

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-11 : 10:06:11

quote:
Originally posted by chrpeter

EDIT: Well I think I realize why these aren't returning the correct number of results, it's because the view has more rows than the table, and I'm using an inner join. But when I change the joins to left outer joins I'm still not getting the correct results.

An inner join would return less than a left join, provided the joining columns match.

If the view has more results than the table, then the returning records likely contains duplicates.

What is the join of the view?

Poor planning on your part does not constitute an emergency on my part.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-11 : 10:08:57

Are order_no columns of type int in both tables?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources