How to make delete update complex queries using join ?

Question

5.00/5 (1 vote)

See more:

Hi ,

below are my scripts which i have created using subqueries

SQL

UPDATE Report
SET ReporttypeId = (SELECT ReportTypeId
FROM ReportType AS rt WITH (NOLOCK)
WHERE code = 'SYSTEM' AND Name= 'System')
WHERE ReportTypeId = (SELECT ReportTypeId
FROM ReportType AS rt WITH (NOLOCK)
WHERE code = 'ADMIN' AND Name= 'Admin')

GO

Delete from ReportType
Where ReportTypeId =
(SELECT ReportTypeId
FROM ReportType AS rt WITH (NOLOCK)
WHERE code = 'ADMIN' AND Name= 'Admin')

Is there any better way to achive the same using joins ??

Posted 20-Oct-15 22:22pm

Torakami

Add a Solution

Comments

jaket-cp 24-Nov-15 4:05am

Have a read of DELETE (Transact-SQL):
https://msdn.microsoft.com/en-us/library/ms189835.aspx
Using joins and subqueries to data in one table to delete rows in another table

Should help you out on how to use joins with delete

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

jgakenhe · Answer 1 · 2015-11-23T17:51:00

That is a good query. I don't see any wrong with it, except you might want to consider refactoring the table hint NO LOCK from it. I am not a fan of NO LOCK as it can cause dirty reads and it can not only miss records but read one before and after it has been updated by a different process.

Mycroft Holmes · Answer 2 · 2015-10-20T22:37:00

Solution 1

This looks like a 1 off maintenance problem so there should be no need for optimisation. You could get fancy and create a table var to hold the from/to values but it is hardly worth it.

Posted 20-Oct-15 22:37pm

Mycroft Holmes