December 7, 2009 at 1:40 pm
How can I accopmlish the following? I want to update the Cust_Line_Del table based on information from the Cust_Order_Line and Customer table. I am not sure how to join the tables in an update statement. Any help will be greatly appreciated.
/code
UPDATE CUST_LINE_DEL SET USER_ORDER_QTY = '0'
WHERE CUSTOMER.ID LIKE 'F%' AND CUST_ORDER_LINE.DESIRED_SHIP_DATE < DATEADD(dd,60,getdate())
/code
December 7, 2009 at 1:54 pm
bpowers (12/7/2009)
How can I accopmlish the following? I want to update the Cust_Line_Del table based on information from the Cust_Order_Line and Customer table. I am not sure how to join the tables in an update statement. Any help will be greatly appreciated.
UPDATE CUST_LINE_DEL SET USER_ORDER_QTY = '0'WHERE CUSTOMER.ID LIKE 'F%' AND CUST_ORDER_LINE.DESIRED_SHIP_DATE < DATEADD(dd,60,getdate())
update table1
set t.somevalue = t2.somevalue
from table1 t
inner join table2 t2
on t2.tableid = t.tableid
Reference
http://technet.microsoft.com/en-us/library/ms177523.aspx
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 7, 2009 at 2:55 pm
This updated all the records regardless of dates. What am I doing wrong?
/code
UPDATE CUST_LINE_DEL SET USER_ORDER_QTY = '9'
FROM CUST_LINE_DEL
JOIN CUST_ORDER_LINE ON CUST_LINE_DEL.CUST_ORDER_ID = CUST_ORDER_LINE.CUST_ORDER_ID
JOIN CUSTOMER_ORDER ON CUST_ORDER_LINE.CUST_ORDER_ID = CUSTOMER_ORDER.ID
WHERE CUSTOMER_ORDER.ID LIKE 'F%' AND CUST_ORDER_LINE.DESIRED_SHIP_DATE < DATEADD(dd,60,getdate())
/code
December 7, 2009 at 10:01 pm
bpowers (12/7/2009)
This updated all the records regardless of dates. What am I doing wrong?
UPDATE CUST_LINE_DEL SET USER_ORDER_QTY = '9'
FROM CUST_LINE_DEL
JOIN CUST_ORDER_LINE ON CUST_LINE_DEL.CUST_ORDER_ID = CUST_ORDER_LINE.CUST_ORDER_ID
JOIN CUSTOMER_ORDER ON CUST_ORDER_LINE.CUST_ORDER_ID = CUSTOMER_ORDER.ID
WHERE CUSTOMER_ORDER.ID LIKE 'F%' AND CUST_ORDER_LINE.DESIRED_SHIP_DATE < DATEADD(dd,60,getdate())
Are there any dates in your table that do not meet this criteria?
CUST_ORDER_LINE.DESIRED_SHIP_DATE < DATEADD(dd,60,getdate())
Try a
Select * from cust_order_line where desired_ship_date > DATEADD(dd,60,getdate()
Does that return any records? If not, then all of your records meet your criteria and thus all orders would be updated.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 8, 2009 at 7:08 am
I have 200 records in my test database that did not meet the criteria. I changed one record to meet the criteria and executed the script. It updated all records.
December 8, 2009 at 12:52 pm
Please post sample data and your table schema. This will aid in troubleshooting your scenario.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 9, 2009 at 9:10 am
I got it. I did not need to join one of the tables. Thanks for all the help.
/code
UPDATE CUST_LINE_DEL SET USER_ORDER_QTY = '9'
FROM CUST_LINE_DEL
JOIN CUST_ORDER_LINE ON CUST_LINE_DEL.CUST_ORDER_ID = CUST_ORDER_LINE.CUST_ORDER_ID
WHERE CUSTOMER_ORDER.ID LIKE 'F%' AND CUST_ORDER_LINE.DESIRED_SHIP_DATE < DATEADD(dd,60,getdate())
/code
December 9, 2009 at 9:48 am
That's good to know.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 9, 2009 at 10:07 am
What do I need to place at the beginning and end of my code to get it to look correctly on the posts? /code obviously did not work.
December 9, 2009 at 10:14 am
Use the brackets [ and ].
Or click on the IfCode to the left that has code="sql" with the brackets.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 9, 2009 at 11:44 am
UPDATE CUST_LINE_DEL SET USER_ORDER_QTY = '9'
FROM CUST_LINE_DEL
JOIN CUST_ORDER_LINE ON CUST_LINE_DEL.CUST_ORDER_ID = CUST_ORDER_LINE.CUST_ORDER_ID
WHERE CUSTOMER_ORDER.ID LIKE 'F%' AND CUST_ORDER_LINE.DESIRED_SHIP_DATE < DATEADD(dd,60,getdate())
Got it. Thanks
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply