Updating a field for multiple records

[Jeff Magouirk]

Dear all,

I need to update one field in a table for a given record and visit number.

Example below is how the table looks -

SID VISIT DLCO
101 0 12
101 1 16
102 0 18
102 2 10
103 1 12
103 2 14

Here is how I would like it to look. The changes are the starred items.

SID VISIT DLCO
101 0 14*
101 1 16
102 0 18
102 2 16*
103 1 12*
103 2 14

I know it is an UPDATE statement, but I am not sure how to use it when I
need to update more than one record.

Thanks for the help in advance.

Jeff

[--CELKO--]

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications. Here is my guess:

CREATE TABLE Foobar
(sid INTEGER NOT NULL,
visit_nbr INTEGER NOT NULL,
dlco INTEGER NOT NULL,
PRIMARY KEY (sid,visit_nbr ));

update one field in a table for a given record and visit number.<<

Rows are not records and columns are not fields!

What is your rule for picking the subset of rows you marked? You can
do with stored procedure liek this.

UPDATE Foobar
SET dlco = @new_vlaue
WHERE sid = @my_sid
AND visit_nbr = @my_visit_nbr;

[Hugo Kornelis]

On Mon, 21 Mar 2005 17:18:49 -0700, Jeff Magouirk wrote:

I need to update one field in a table for a given record and visit number.

Example below is how the table looks -

SID VISIT DLCO
101 0 12
101 1 16
102 0 18
102 2 10
103 1 12
103 2 14

Here is how I would like it to look. The changes are the starred items.

SID VISIT DLCO
101 0 14*
101 1 16
102 0 18
102 2 16*
103 1 12*
103 2 14

I know it is an UPDATE statement, but I am not sure how to use it when I
need to update more than one record.

Hi Jeff,

You forgot to explain the logic: why are these three rows updated, but
none of the others? Why are the new values 14, 16, and 12?

The following UPDATE statement will make the required change on the
sample data you posted. If it as anything like what you want, I can't
tell.

UPDATE MyTable
SET DLCO = 216 - 2 * (SID - VISIT)
WHERE SID + VISIT + DLCO < 117

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Tzvika Barenholz]

I think you are looking for something like

update tbl1 set dlco = case sid when 101 then 14 else when 102 then 16
else when 103 then 12 else dlco end where sid in(101,102,103)

i am a little rusty with the case syntax so it may not compile :-) ....
but i think this is what you meant.