December 26, 2011 at 3:23 am
I have three tables: locn, acs, aco.
locn and acs have common key of [lid]
acs and aco have common key [uid]
locn and aco have no common key
locn has columns: lid, did
acs: has columns: lid,uid
aco has columns: uid, type
I want to update all values of did in locn where the type in aco corresponding to the uid for the did column is 'd'
I've done this:
update a set a.[did]='z' from locn a
join acs b on a.[lid]=b.[lid]
join aco c on c.[uid]=b.[uid]
where c.[type]='c'
But all of the values if did in table locn get set to 'z' regardless of whether they contain value 'd' or not.
I know you can solve this using EXISTS but wondering if it's possible using JOINS
December 26, 2011 at 3:54 am
-- check first using the corresponding SELECT:
SELECT a.*, b.*, c.*
from locn a
join acs b on a.[lid]=b.[lid]
join aco c on c.[uid]=b.[uid]
where c.[type]='c'
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]
December 27, 2011 at 5:48 am
I would try something like this:
update a set did='z' where lid in
( select lid from b where uid in
( select uid from c where type='c') )
December 27, 2011 at 8:31 am
update a set a.[did]='z' from locn a
join acs b on a.[lid]=b.[lid]
join aco c on c.[uid]=b.[uid]
where c.[type]='c'
You want a (locn) updated where a.did = 'd', per your description:
I want to update all values of did in locn where the type in aco corresponding to the uid for the did column is 'd'
Why isn't the script:
update a set a.[did]='z' from locn a
join acs b on a.[lid]=b.[lid]
join aco c on c.[uid]=b.[uid]
where c.[type]='c'
and a.did = 'd'
Am I misreading your requirements?
- 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
December 28, 2011 at 3:52 am
This will help you,
update A SET A.DID='X'
from
LOCN A
join
ACS B
On
A.LID = B.LID
JOIN
ACO C
ON
B.UID = C.UID
where
C.TYPE = 'd'
December 28, 2011 at 8:19 am
tharan.info (12/28/2011)
This will help you,update A SET A.DID='X'
from
LOCN A
join
ACS B
On
A.LID = B.LID
JOIN
ACO C
ON
B.UID = C.UID
where
C.TYPE = 'd'
How is setting the column to 'X' going to help when the requirements ask for 'z'? :ermm:
GSquared has the solution in his post; OP just needed another condition in the where clause.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply