July 16, 2011 at 12:22 pm
I have a query that update a target table by joinng this table with other 3 tables to limit records.
My query works.
But a couple of things want to clarify.
Before I do the update, I usually use the select to verify it is the recordset I want to update.
But for this case for it joins multiple tables, I found when I use select to verify records, I got duplicated records, if I use distinct the records look fine.
My question is : is it an common case that when join multiple tables we get duplicated recors, and it is Ok then use update to perform on those records.
or I have to make the select result to no duplicate without using distinct to make the update to work
correctly.
Thanks
July 16, 2011 at 12:34 pm
A question, when performing the select do use either a UNION or UNION ALL statement(s) in the T-SQL ?
Next if you would post your T-SQL along with table definition(s) you are more likely to get a definitive answer.
July 16, 2011 at 12:41 pm
I didn't use union or union all in my from clause.
It's 3 joins with primary keys with other tables.
July 16, 2011 at 1:41 pm
You are more likely to get additional tested assistance if you post the table definitions, some sample data and required results. To do this quickly and easily read the article whose link is the first in my signature block
July 17, 2011 at 12:00 am
sqlfriends (7/16/2011)
My question is : is it an common case that when join multiple tables we get duplicated recors, and it is Ok then use update to perform on those records. or I have to make the select result to no duplicate without using distinct to make the update to workcorrectly.
This usually indicates a design problem, though it is difficult to say for sure without an example. UPDATE will work with duplicates, but the results may be unpredictable if your query logically updates the same target row with different values. This behaviour is unique to UPDATE; if you try the same query written using the new SQL Server 2008 MERGE statement, you would get an error message. Generally, you will want to write your queries such that each target row is updated at most once.
July 17, 2011 at 9:23 am
Thanks, that makes much sense.
I recreate my query use 'the one to many' the one side table as the first table before join, now there is n o duplicated records.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply