469,949 Members | 2,699 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,949 developers. It's quick & easy.

Updating a field for multiple records

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
Jul 23 '05 #1
3 1127
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;

Jul 23 '05 #2
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)
Jul 23 '05 #3
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.

Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

33 posts views Thread by bill | last post: by
10 posts views Thread by chimambo | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.