s/procedure with inner join/update help

Question

s/procedure with inner join/update help

celticpiping

SSC-Addicted

Points: 406
More actions
October 16, 2015 at 1:27 pm

#305521

Howdy,
I'm trying to do the following:
update a tables value(a single column), based on a query of another database, using
a common column.
Here's what I've cooked up so far:
Declare @trackingNo nvarchar (50)
Set @trackingNo =
(
select tracking_no from P21_Shipping.dbo.shipping_data t1
inner join P21.dbo.oe_hdr t2 on t1.order_no = t2.order_no
where t1.order_no = t2.order_no
and DateInserted < GetDate()
and DateInserted > dateadd(minute, -5, GetDate())
)
print @trackingNo
The error it's returning is:
Msg 512, Level 16, State 1, Line 3
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
So, I'm wanting to query Shipping_data for order_No that matches the same orderNo column value in P21 Database(oe_hdr table),
then update P21 oe_hdr table with "trackingNo from Shipping_data
Make sense?
I'd love to know better ways to approach this: I'm clearly not a guru...
Thanks lads,
Rich

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

celticpiping (10/16/2015)
Howdy,
I'm trying to do the following:
update a tables value(a single column), based on a query of another database, using
a common column.
Here's what I've cooked up so far:
Declare @trackingNo nvarchar (50)
Set @trackingNo =
(
select tracking_no from P21_Shipping.dbo.shipping_data t1
inner join P21.dbo.oe_hdr t2 on t1.order_no = t2.order_no
where t1.order_no = t2.order_no
and DateInserted < GetDate()
and DateInserted > dateadd(minute, -5, GetDate())
)
print @trackingNo
The error it's returning is:
Msg 512, Level 16, State 1, Line 3
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
So, I'm wanting to query Shipping_data for order_No that matches the same orderNo column value in P21 Database(oe_hdr table),
then update P21 oe_hdr table with "trackingNo from Shipping_data
Make sense?
I'd love to know better ways to approach this: I'm clearly not a guru...
Thanks lads,
Rich

The error message is because the query you are using returns more than 1 row and you are trying to set the result of that query to a scalar variable. The error is because the sql engine has no idea which row it should use.

The good news is that there are certainly a number of people around here that can help you. The bad news is we don't have enough information yet to offer any help. Take a look at the following article for a great explanation of what you need to post for people to help you the best. It takes some effort on your end but you will be rewarded with functional (and fast) code. And the bonus is at the end of the journey you should have learned some new things.

http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/[/url]

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

sourav.2007.ghosh SSC Veteran Points: 209 More actions · Answer 2

Hi Rich,

The error that you are getting is inevitable and you might have already figured out by now. So i will not go into that.

I am not sure if i understand your requirement fully, but what i get is - You have two tables, and you want to update a column of table2(say) with the value from another table (table1 say), and both these tables share a common column..

So try the below example, if I understand your requirement then this should work for you.

declare @table1 TABLE(

OrderNumber INT,

TranckingNumber INT

)

declare @table2 TABLE(

OrderNumber INT,

TranckingNumber INT

)

-- insert four values to @table1

INSERT INTO @table1 SELECT 1, 100

INSERT INTO @table1 SELECT 2, 101

INSERT INTO @table1 SELECT 3, 102

INSERT INTO @table1 SELECT 4, 103

-- insert same order numbers to @table2

INSERT INTO @table2(OrderNumber) SELECT 1

INSERT INTO @table2(OrderNumber) SELECT 2

INSERT INTO @table2(OrderNumber) SELECT 3

INSERT INTO @table2(OrderNumber) SELECT 4

SELECT * FROM @table1

SELECT * FROM @table2

-- Now update the Tracking Number from @table1 to @table2

UPDATE t2 SET t2.TranckingNumber = t1.TranckingNumber

FROM @table2 t2 JOIN @table1 t1 ON t2.OrderNumber = t1.OrderNumber

SELECT * FROM @table2

Let me know if this works...

celticpiping SSC-Addicted Points: 406 More actions · Answer 3

Wow, I HATE when I lose an entire post...

Here goes again

So, I'm attempting to do the INSERT statement thing below to help with this issue.

Again, my goal is to match on "order_no" , and poke in "tracking_no from "shipping_data" table into "oe_hdr" table.

Here's shipping_data table:

INSERT INTO shipping_data(tracking_no,order_no,po_no,ship_to_name,ship_to_addr1,ship_to_addr2,ship_to_addr3,ship_to_zip,ship_to_country,ship_to_city,ship_to_state,ship_to_telephone,ship_from_name,ship_from_addr1,ship_from_addr2,ship_from_addr3,ship_from_zip,ship_from_country,ship_from_city,ship_from_state,ship_from_telephone,DateInserted,Processed) VALUES ('1Z0310590354462094','1248407','337913','STOM, & CO., INC.','41 SUN STREET','DOCK C13',NULL,'037731490','United States','NEWPORT','RI',NULL,'Butler Bros Inc','2001 Lisbon Street','PO Box 1375',NULL,'042401311','United States','Lewiston','ME','2077846875',NULL,0);

INSERT INTO shipping_data(tracking_no,order_no,po_no,ship_to_name,ship_to_addr1,ship_to_addr2,ship_to_addr3,ship_to_zip,ship_to_country,ship_to_city,ship_to_state,ship_to_telephone,ship_from_name,ship_from_addr1,ship_from_addr2,ship_from_addr3,ship_from_zip,ship_from_country,ship_from_city,ship_from_state,ship_from_telephone,DateInserted,Processed) VALUES ('1Z0310590353761012','1250639','125471','TAMR','4806 WEST DOCK ROAD',NULL,NULL,'13088','United States','LIVERPOOL','MA',NULL,'Butler Bros Inc','2001 Lisbon Street','PO Box 1375',NULL,'042401311','United States','Lewiston','ME','2077846875',NULL,0);

Here's oe_hdr table:

INSERT INTO oe_hdr(order_no,customer_id,ups_code) VALUES (1002571,101032,'COLL 047-411');

INSERT INTO oe_hdr(order_no,customer_id,ups_code) VALUES (1002732,101324,'COLLECT 024-287');

One other thing is, I had wanted to build in a means to process these in 'batches', meaning say every 5 minutes, fire off the code as a stored procedure, hence the DateTime stuff in the original post. I was attempting to build something that would look for records not processed within the past 5 minutes.

I also thought about a Bit column on shipping_data tabole, and flip a bit from a 0 to a 1 if it's been processed..?

Welcome any & all thoughts, and thanks much for any help!

Rich

ps.

celticpiping SSC-Addicted Points: 406 More actions · Answer 4

SSC,

you pretty much described what I'm trying to do..

Sadly, I have to fess up, I don't understand how the code you present

correlates to the "real" SQL tables...

I've never used a TABLE as variable before..

:/

sourav.2007.ghosh SSC Veteran Points: 209 More actions · Answer 5

Hi There,

You can just replace the table variable with the "Real tables". The inserts are just to complete the demo. It is only the Update block that you really need. So below code should work:

UPDATE hdr SET hdr.ups_code = shipping.tracking_no

FROM oe_hdr hdr JOIN shipping_data shipping ON hdr.order_no = shipping.order_no

You can also add the filter to only update the rows where us_code is null like below and then you can then surely automate this.

where ups_code IS NULL

Let me know if this works.

celticpiping SSC-Addicted Points: 406 More actions · Answer 6

ok, many thanks: lemme see what trouble I can get into with that direction...

Rich

celticpiping SSC-Addicted Points: 406 More actions · Answer 7

well, things got more complicated, by the fact that a given order_no, can have multiple tracking_no values attached to it....

ugh

I soldier on....

sourav.2007.ghosh SSC Veteran Points: 209 More actions · Answer 8

All the best!!

Btw, let me know the new requirement, a sample data representation of what is required will be the best. I will see if I can be of any help.