October 26, 2010 at 11:03 am
Hi All -
I am having a brain lapse and could use your help. I have the following query which returns the rows I need:
select * from TABLE1 t1, TABLE2 t2
where t1.field1 in (select t2.field1
from TABLE2 t2, TABLE3 t3
where t2.field3 = t3.field3
and t1.field4 = t2.field4)
and t1.field4 = t2.field4
What I need is to update a column from TABLE1 and set it to 'Y' where it is in the result set of the above query. Example:
Update Table1
set fieldx = 'Y'
where (results from above query).
I am sure there is an easy way to do this but I am drawing a blank.
October 26, 2010 at 11:30 am
You need to write the query properly first - if this returns the correct rows, it's by accident, not by design. Something like this:
select t1.*, t2.*, t3.*
FROM TABLE1 t1
INNER JOIN TABLE2 t2 ON t1.field4 = t2.field4 AND t1.field1 = t2.field1
INNER JOIN TABLE3 t3 ON t2.field3 = t3.field3
I'm not sure of this will work but it won't be far off - the worst that can happen is you'll have to set up a derived table from TABLE3 to bring the cardinality in line with the other tables.
This is how the update would look:
UPDATE t1 SET <<column>> = 'Y'
FROM TABLE1 t1
INNER JOIN TABLE2 t2 ON t1.field4 = t2.field4 AND t1.field1 = t2.field1
INNER JOIN TABLE3 t3 ON t2.field3 = t3.field3
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 26, 2010 at 11:42 am
Replace "select *" with your update statement, and you'll have valid T-SQL. Wrap it in a transaction, use the Output clause to test it, and roll it back. Once you're certain it will do what you need, rerun with a commit instead of a rollback.
HOWEVER, I recommend rewriting the query to follow the ANSI-92 join standard, instead of defining your join criteria in the Where clause. Probably won't change this query, but has a big effect on other queries, and consistency helps in documentation and maintenance.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply