Update subquery or join 1

[mossbs]

Hi guys,

Trying to bulk update a date column in a table (attribute) with a a date value from a seperate table (member).

syntax at the moment looks like....

--join

update attribute a
join member m on m.individual_ref = a.individual_ref
set a.valid_to = m.leave_date
where a.individual_ref in
(select individual_ref from member 
where leave_date is not null and individual_ref is not null)

 this join is throwing errors (assuming you cannot uise joins in update statements?)

-- or straight subquery

update attribute
set valid_to = (select leave_date from member)
where a.individual_ref in
(select individual_ref from member 
where leave_date is not null and individual_ref is not null)

however getting error that subquery returns more than one value.... any ideas how to do this?

Cheers all.

Dan.

 

[bborissov]

JOIN:
and the right syntax is

update attribute
       set attribute.valid_to = member.leave_date
FROM attribute
INNER JOIN member on member.individual_ref = attribute.individual_ref AND 
                     member.leave_date IS NOT NULL
WHERE attribute.individual_ref IS NOT NULL

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[mossbs]

fantastic - cheers boris!!

Dan.

 

[sasadear]

when you write program ,SQL must be direct,never to sql in sql,such as
(select leave_date from member)
where a.individual_ref in
(select individual_ref from member
where leave_date is not null and individual_ref is not null)

such programmer will slow

http://www.dearok.com