Multiple updates - 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

SQL Server Development (2000)

Multiple updates

Author

Topic

jparker
Posting Yak Master

118 Posts

Posted - 2005-09-22 : 05:33:05

If I want to do a batch of inserts in one statement I would do something like this : -

insert into tbldeliveries (est_id, delivery_to, cost, supplier_id, status_id, date, delivery_from) select e.est_id, c.con, c.charge, 1, 1, getdate(), 'Temp'
from tblTemp c join tblestimate e on + e.job_id = c.job_no

and all works fine for this.

But what if I wanted to do a batch update. My pseudo syntax would be something like this : -

UPDATE tbltable
SET deliveries = (select all which meet my criteria)
join tables that are important
WHERE (selecting those that are only due to be updated)

I'm not sure you can do a join on an update so does this mean I need to do a loop and do an update individually?

Your thoughts would be greatly appreciated

JP

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-09-22 : 05:38:36

UPDATE tbltable
SET deliveries = jt.Deliveries
from tbltable m
join tbljointable jt
on m.idcol = jt.idcol
WHERE jt.criteriacol = 'Criteria'

Duane.

jparker
Posting Yak Master

118 Posts

Posted - 2005-09-22 : 06:28:46

thanks I forgotten about the all important from keyword :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources