how do i delete or update using 2 tables

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

Transact-SQL (2000)

how do i delete or update using 2 tables

Author

Topic

csphard
Posting Yak Master

113 Posts

Posted - 2003-07-15 : 18:16:06

My goal is to delete information from one table based on a comparison
of 2 tables.
first i thought i would delete some information using a where clause that compared to tables. Then i realize it did not know where to delete from

delete from
due_evals d,
emp_information_test e
where d.datetobefiled between '01/01/2003' and '02/28/2003'
and d_evals.empid = e.empid
and e.bureau in ('375')
and d.payloc_comp_date is null
and d.eval_type = 'Probation'

second i thought i would update a field in the table with the word delete where the criteria matches. Then delete using a where clause searching for the word delete in that field niether worked. HELP

update due_evals set hr_updated_by = 'delete'
where due_evals.datetobefiled between '01/01/2003' and '02/28/2003'
and due_evals.empid = emp_information_test.empid
and emp_information_test.bureau in ('375')
and due_evals.payloc_comp_date is null
and due_evals.eval_type = 'Probation'

chadmat
The Chadinator

1974 Posts

Posted - 2003-07-15 : 18:31:36

Try:

delete <TheTableToDeleteFrom>
from
due_evals d,
emp_information_test e
where d.datetobefiled between '01/01/2003' and '02/28/2003'
and d_evals.empid = e.empid
and e.bureau in ('375')
and d.payloc_comp_date is null
and d.eval_type = 'Probation'

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2003-07-15 : 18:58:49

Until you get your query right use SELECT instead to view the results that you expect and when your happy with that change the SELECT to DELETE just so you dont inadvertenly delete data you want and saves restoring from backups so you can keep testing. Same goes for updates.
Thats what i do anyway - a useful tip i received

Example:
SELECT MyTable.*
FROM MyTable
WHERE MyColumnIndex > 10

View/confirm results then change to

DELETE MyTable
FROM MyTable
WHERE MyColumnIndex > 10

Andy

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-15 : 19:03:08

I would definitely follow Andy's advice, especially if this is for production. You don't want to modify ANY data until you know the SELECT works.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources