SQL Update with Table Join Syntax 1

[dfrazell]

I need to update the production date field (PROD_DATE) in my Work Order table (WORK_ORDER) based on a unique join on Order Number to an intermediate table (WO_LINK). The WO_LINK table provides the unique information to update the correct Work Order record. The order number and the production date are passed in as variables.

For Example:
Order Number = S15000

WO_LINK:
Where WO_LINK_ORDER = S15000
------------------------------
WO_LINK_ID: WO12345
WO_LINK_LOT_ID: 1
WO_LINK_SUB_ID: 1

So I would want to update the Work_Order table for S15000 where
WO_LINK_ORDER = ‘S15000’ and
WO_LINK_ID = WORK_ORDER.WO_ID and 
WO_LINK_LOT_ID = WORK_ORDER.WO_LOT_ID and 
WO_LINK_SUB_ID = WORK_ORDER.WO_SUB_ID

How do I write the SQL update statement for perform this action?

 

[SQLSister]

the syntax for using another table in a join when doing an update is like this:

update t1
set field1 = t2.someotherfield
from table1 t1
join table2 t2 on t1.someid = t2.someid
where t2.myfield = 'test'
[\code]
I think you can adapt this to your current situation yourself.

"NOTHING is more important in a database than integrity." ESquared

 

[dfrazell]

This is what I came up with but I didn't use a 'where' clause. Is it correct? I ran it in test and it seemed to work ok.

update work_order
set prod_date = @Prod_Date
from work_order WKO
inner join WO_LINK WOL on
WOL.id        = @Ord_Num and
WOL.WO_ID     = WKO.BASE_ID  and
WOL.WO_LOT_ID = WKO.LOT_ID   and
WOL.WO_SUB_ID = WKO.SUB_ID

 

[SQLSister]

Actaully you put the where clause in the join.
Try these two select statments and make sure there isn't a difference

select * from 
from work_order WKO
inner join WO_LINK WOL on
WOL.id        = @Ord_Num and
WOL.WO_ID     = WKO.BASE_ID  and
WOL.WO_LOT_ID = WKO.LOT_ID   and
WOL.WO_SUB_ID = WKO.SUB_ID

select * from 
from work_order WKO
inner join WO_LINK WOL on
WOL.WO_ID     = WKO.BASE_ID  and
WOL.WO_LOT_ID = WKO.LOT_ID   and
WOL.WO_SUB_ID = WKO.SUB_ID
where WOL.id  = @Ord_Num

I have found there are sometimes differnces when putting a where condition in the join if it isn't a left join.

"NOTHING is more important in a database than integrity." ESquared

 

[dfrazell]

Both selects return the same result.

My update just got a little more complicated because I realized my Prod_Date is stored in another file. Here is what I have.

update work_order
set Prod_Date = JMJC.JM_Pnd_Prod_Date
from work_order WKO

inner join WO_LINK WOL on
WOL.id = 'S17954' and
WOL.WO_ID     = WKO.BASE_ID  and
WOL.WO_LOT_ID = WKO.LOT_ID   and
WOL.WO_SUB_ID = WKO.SUB_ID

inner join JM_Job_Changes JMJC on
JMJC.JM_Order_ID  = 'S17954'
JMJC.JM_Pnd_Prod_Date is not null

Where 'S17954' would be a variable.

I ran this in test and it said that 1700(+) rows affected. That isn't correct. Only one record should be updated.

 

[dfrazell]

I had the wrong database selected in query analyzer but I don't know what it would have update because there shouldn't be any matching records in that database. When I had the correct database selected it said 1 row affected. How can I ensure this thing fails safe once I roll this into production? Is it better to use the Where clause? I don't want to accidentally update more than the one matching record.

 

[dfrazell]

Is this the best way to write this update statement? I want to ensure I'm only updating one unique work order and not accidentally updating other ones. Is it better to move the order ID out of the inner joins and added them to a where clause? This update will be used 100's of times a day so I want to make sure it is solid. Thanks.

update WKO
set Prod_Date = JMJC.JM_Pnd_Prod_Date
from work_order WKO

inner join WO_LINK WOL on
WOL.id = 'S17954' and
WOL.WO_ID     = WKO.BASE_ID  and
WOL.WO_LOT_ID = WKO.LOT_ID   and
WOL.WO_SUB_ID = WKO.SUB_ID

inner join JM_Job_Changes JMJC on
JMJC.JM_Order_ID  = 'S17954'
JMJC.JM_Pnd_Prod_Date is not null

 

[SQLSister]

best way to tell waht you are going to update before doing the update is this

update WKO
set Prod_Date = JMJC.JM_Pnd_Prod_Date
--select wko.Prod_Date ,JMJC.JM_Pnd_Prod_Date, wko.*
from work_order WKO

inner join WO_LINK WOL on
WOL.id = 'S17954' and
WOL.WO_ID     = WKO.BASE_ID  and
WOL.WO_LOT_ID = WKO.LOT_ID   and
WOL.WO_SUB_ID = WKO.SUB_ID

inner join JM_Job_Changes JMJC on
JMJC.JM_Order_ID  = 'S17954'
JMJC.JM_Pnd_Prod_Date is not null

then run the commented out select part (With the rest of the query below it as well) to see what records you are chaging and what you are changing them to before you run the update..

"NOTHING is more important in a database than integrity." ESquared

 

[dfrazell]

Thanks - that makes sense. I've never looked at an update that way before.