Update query
-
I was wondering if someone can help. I'm trying to run this update statement on a test db and am getting 3x the number of rows back than I should. I'm not sure I'm writing it correct, because in the where clause I have criteria for both the tables. Thanks!
Update table1
Set table1.YZBX01 = table2.YZBX01,
table1.YZBX02 = table2.YZBX02,
table1.YZBX03 = table2.YZBX03,
table1.YZBX04 = table2.YZBX04
FROM dbo.table1 RIGHT OUTER JOIN
dbo.table2 ON dbo.table1.YZAN8 = dbo.table2.YZAN8
WHERE (dbo.table2.YZDTEY = 6) AND (dbo.table2.YZPTAX = 'P') AND
(dbo.table2.YZTARA = 'FEDERAL') AND (dbo.table1.YZPTAX = 'H')
Thanks!
-
Could you post some sample data from table1 and table2 as well as an example of the results you are expecting? Is there a reason you chose a RIGHT OUTER JOIN instead of an INNER JOIN? If you only want to update rows in table1 that have a match in table2 on YZAN8, you should not need to use an outer join.
-
Can you give us some test data? Also, can you show what output you get and what output you want? It is hard to tell what your problem is from this information...
UPDATE table1 SET
t1.YZBX01 = t2.YZBX01,
t1.YZBX02 = t2.YZBX02,
t1.YZBX03 = t2.YZBX03,
t1.YZBX04 = t2.YZBX04
FROM dbo.table1 t1
RIGHT JOIN dbo.table2 t2 ON t1.YZAN8 = t2.YZAN8
AND t2.YZDTEY = 6
AND t2.YZPTAX = 'P'
AND t2.YZTARA = 'FEDERAL'
AND t1.YZPTAX = 'H')
I wasn't born stupid - I had to study.
-
Hi...i dont think there is a need for RIGHT OUTER JOIN here,
thatz the cause for the 3x rows.
Rather use INNER JOIN...
Update table1
Set table1.YZBX01 = table2.YZBX01,
table1.YZBX02 = table2.YZBX02,
table1.YZBX03 = table2.YZBX03,
table1.YZBX04 = table2.YZBX04
FROM dbo.table1 INNER JOIN
dbo.table2 ON dbo.table1.YZAN8 = dbo.table2.YZAN8
WHERE (dbo.table2.YZDTEY = 6) AND (dbo.table2.YZPTAX = 'P') AND
(dbo.table2.YZTARA = 'FEDERAL') AND (dbo.table1.YZPTAX = 'H')
hope this helps
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply