how to update multiple rows at once

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to update multiple rows at once

Author

Topic

sbbkillshot
Starting Member

3 Posts

Posted - 2005-08-10 : 20:40:19

ok my data base has 4 columns id,fname,lname,email and 3 rows 1,2,3
I made a simple update form that accesses the data and displays all three rows with the data in them:

<cfquery name="QUIZ" datasource="test">
SELECT id,fname,lname,email
FROM info
order by id
/cfquery
<HTML>
<HEAD>
<TITLE>Update an Employee</TITLE>
</HEAD>
<BODY>
<H1>Update an Employee</H1>
<FORM ACTION="databaseupdater.cfm" METHOD="POST">
<cfoutput query="QUIZ">
<INPUT TYPE="hidden" NAME="id" VALUE="#id#">
<P>
First name:
<INPUT TYPE="text" NAME="fname" SIZE="15" MAXLENGTH="30" VALUE="#Trim(fname)#">
<BR>
Last name:
<INPUT TYPE="text" NAME="lname" SIZE="15" MAXLENGTH="30" VALUE="#Trim(lname)#">
<BR>
E-Mail:
<INPUT TYPE="text" NAME="email" SIZE="15" MAXLENGTH="30" VALUE="#Trim(email)#">
<P>
</cfoutput>
<INPUT TYPE="submit" VALUE="update">
<INPUT TYPE="reset" VALUE="Clear">
</FORM>
</BODY>
</HTML>

you can use this form to send the updated data to databaseupdater.cfm:

<CFQUERY
DATASOURCE="test"
>
UPDATE info
SET fname='#fname#',
lname='#lname#',
email='#email#'
</CFQUERY>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled Document</title>
</head>

<body>
Thank You
</body>
</html>

the problem Grieg RN is each column in the database gets updated with all 3 rows of data in each column in stead of seperating the data in its correct row and column. I don't know much about the problem im just starting out please help. Thanks

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-08-10 : 20:58:39

You need to identify the row you want to update with a WHERE clause:

UPDATE info
SET fname='#fname#',
lname='#lname#',
email='#email#'
where
id = #id#

CODO ERGO SUM

sbbkillshot
Starting Member

3 Posts

Posted - 2005-08-10 : 21:05:16

in this case I want to update each row 1,2 and 3 but each row will have different data.

sbbkillshot
Starting Member

3 Posts

Posted - 2005-08-10 : 21:42:06

Ok let me ask it like this say I had a data base consisting of 200 rows all with unique id #s 1-200 and one column named info, and a enrty form that had 200 text fields on it. Once that form was completely filled in with data upon submitting that form the data collected would be entered into the info column of the data base each piece of data going into the row id that corresponeded to it in the question form.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-08-11 : 06:47:23

then you'll have 100 update's

Go with the flow & have fun! Else fight the flow

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-08-11 : 07:53:57

Take a look at these:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=19565
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12538
http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=13935
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=21690

There are a number of techniques described that would let you avoid doing multiple updates or inserts in a loop.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources