update query selection criteria

[jcf27]

The following update query runs OK, except that it clears out fields corresponding to rows not meeting the criteria. How can I make it update only the selected rows and leave others as they are?

Thanks in advance.

J.

update address fa
set address = (select psc.value
FROM sec_code p
WHERE (fa.name = p.name)

 

[jimbopalmer]

update address fa
set address = (select psc.value
FROM sec_code p
WHERE (fa.name = p.name)
WHERE name in (select name from sec_code)


should do it, but it may well be slower I tried to remain child-like, all I acheived was childish.

 

[dnoeth]

If your update modifies a large percentage of your data you could try:

update address fa
set address = COALESCE((select psc.value
FROM sec_code p
WHERE (fa.name = p.name), address)

To DBMS should be smart enough to update only those rows where the address changes.

Dieter

 

[tlbroadbent]

Try this.

UPDATE Address
SET address = p.value
FROM Address fa
INNER JOIN sec_code p
ON fa.name = p.name
WHERE fa.Address<>p.Value Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.