February 21, 2006 at 10:56 pm
Is there any alternative of the following query without using subquery.
UPDATE TableA
Set ColumnA = 'Test'
WHERE ColumnB = (SELECT ColumnC
FROM TableB
WHERE ColumnD = @Name)
HERE 'ColumnB' of 'TableA' is the FK from 'columnC' of 'TableB'
February 21, 2006 at 11:24 pm
UPDATE TableA
Set ColumnA = 'Test'
from TableA join TableB on
TableA .ColumnB = TableB.ColumnC
WHERE TableB.ColumnD = @Name
I think this Query should work for you
February 21, 2006 at 11:47 pm
wat do u think which query will give better performance.
February 22, 2006 at 12:10 am
also tell me is there any way to update the columns of more than one table in a single Update statement.
February 22, 2006 at 2:00 am
Join is always faster compare to use of any sub query.
And about second quote... I don't think so that you can update multiple table in single Query
February 23, 2006 at 10:09 am
I believe that the join will take longer because the other will simply rasie an error without actually doing any updates.
You can update multiple tables with a single Update. There are some restrictions and things that you must set up ahead of time.
You will need to create an "updatable view". From the BOL on "updatable views":
The DELETE, INSERT, and UPDATE statements can reference a view as long as SQL Server can translate the user's update request unambiguously to updates in the base tables referenced in the view's definition.
If the server can't unambiguously figure out what is to be updated, you may need to use an INSTEAD OF trigger.
Excerpt from the BOL on "INSTEAD OF triggers":
"The primary advantage of INSTEAD OF triggers is that they allow views that would not be updatable support updates. A view comprising multiple base tables must use an INSTEAD OF trigger to support inserts, updates and deletes that reference data in the tables. Another advantage of INSTEAD OF triggers is that they allow you to code logic that can reject parts of a batch while allowing other parts of a batch succeed."
These two excerpts seem to contradict each other. The first claims that you can update some types of multi-table views, and the second claims that you con only do that with INSTEAD OF triggers.
I've never tried it myself. Let us know what you figure out!
jg
February 23, 2006 at 2:22 pm
There is an error.
You are updating whole TableA, not only rows matched.
UPDATE A
Set ColumnA = 'Test'
from TableA A
inner join TableB on A.ColumnB = TableB.ColumnC
WHERE TableB.ColumnD = @Name
OR
UPDATE TableA
Set ColumnA = 'Test'
from TableB
WHERE TableA .ColumnB = TableB.ColumnC
AND TableB.ColumnD = @Name
_____________
Code for TallyGenerator
February 23, 2006 at 9:13 pm
Sergiy , your first Query and My Query is almost same only difference is you have added alias and use that Alias in update statement.
Tell me performance wise is there any difference or does both Query will give diff result...??
February 23, 2006 at 10:10 pm
Update TableA
SET...
FROM TableA
Those Tablea's are actually DIFFERENT tables!
And you don't have anything to join them in your query.
That's why your result will not be the same as you expect.
_____________
Code for TallyGenerator
February 23, 2006 at 10:18 pm
JG can u plz tell me wat error will be raised by the "other query"
February 23, 2006 at 11:55 pm
Thanx Sergiy for clearing me
February 24, 2006 at 6:22 am
OOPS. I read it wrong. My apologies.
Must drink more coffee before replying.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply