sql help

pts.
Tags:
AS/400
SQL
SQL Server
is there a way to update multiple records in a table the meet criteria from another table. here is what i'm trying update table/detail set condition = 'r' where condition = ' ' and number_d = (select number_h from table/header where date between 050101 and 051231) thanks in advance

Answer Wiki

Thanks. We'll let you know when a new response is added.

The way you cited,

update table/detail
set condition = ‘r’
where condition = ‘ ‘ and number_d = (select number_h from table/header where
date between 050101 and 051231)

is one way to do it. Here’s a more efficient way, especially if number_h is indexed:

UPDATE table/detail d
SET condition = ‘r’
WHERE condition=’ ‘ AND
EXISTS(
SELECT 1
FROM table/header h
WHERE d.number_d=h.number_h AND
h.date BETWEEN 050101 AND 051231)

Your optimal indeces for this is:

CREATE INDEX firstIndex ON table/detail(condition);
CREATE INDEX secondIndex ON table/header(number_h,date);

If your data allows, use CREATE UNIQUE INDEX for the second index.

==========================================================

I believe the statement should be:<pre> update table/detail
set condition = ‘r’
where condition = ‘ ‘ and number_d in (select number_h from table/header where
date between 050101 and 051231)</pre>
The IN () clause introduces a sub-select and replaces the “=”. Note that an “=” condition necessarily implies one and only one selected value. IN () implies a possible set of values of which one might equal number_d.

Tom

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • YuVa47
    Tom, Its very nice that you are answering all these questions and your answers are very clever, but have you observed that in the 'Daily Question and Answer Update for Nov 23, 2009' you have answered questions which was from 2005? :-) YuVa
    1,300 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following