UPDATE statement using multiple SET ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2000 Forums

SQL Server Development (2000)

UPDATE statement using multiple SET ?

Author

Topic

Kyle
Starting Member

7 Posts

Posted - 2002-04-17 : 13:16:13

I have a couple updates to do from an ASP page to a single SQL Server table. Unfortunatley I can't use SP's for this.

I know that issuing a compound SQL command with multiple UPDATE statements means that it'll have to loop through the table for as many UPDATE statements as the SQL command contains.

What I want to try is to write an SQL statement using a single UPDATE table and multiple SET commands. Is this possible? If so, how is it done?

dsdeming

479 Posts

Posted - 2002-04-17 : 13:39:44

The syntax goes like this:

update tablename
set columnname1 = value1,
columnname2 = value2
where condition

It will work fine as long as one where condition applies. Otherwise you need multiple updates.

motokevin
Starting Member

36 Posts

Posted - 2002-04-17 : 13:41:01

quote:
Unfortunatley I can't use SP's for this.

You can use SP's to do this.

quote:
What I want to try is to write an SQL statement using a single UPDATE table and multiple SET commands. Is this possible?

Yes it is possible.

quote:
If so, how is it done?

Non-SP solution.

<%
'assuming you have code somewhere that creates a conneciton object to SQL. assume the connection object is called objConnection

dim strCol1Val
dim strCol2Val
dim strCol3Val

'code here to get values for update variables

dim strSQLUpdateTable 'the sql string

strSQLUpdateTable = "Update Table_Name Set Column_1 = " & strCol1Val & ", Column_2 = " & strCol2Val & ", Column_3 = " & strCol3Val & " Where < conditions here >"

objConneciton.Execute(strSQLUpdateTable)
%>
------------
SP Solution

<%
'assuming you have code somewhere that creates a conneciton object to SQL. assume the connection object is called objConnection

dim strCol1Val
dim strCol2Val
dim strCol3Val

'code here to get values for update variables

dim strSQLUpdateTable 'the sql string

strSQLUpdateTable = "Exec sp_Update_Table " & strCol1Val & ", " & strCol2Val & ", " & strCol3Val

objConneciton.Execute(strSQLUpdateTable)
%>

----------
General syntax for UPDATE with multiple SET commands:

UPDATE Table_Name Set
Col_1 = <value1>,
Col_2 = <value2>,
Col_3 = <value3>
WHERE <conditions>

Kyle
Starting Member

7 Posts

Posted - 2002-04-17 : 13:46:46

Sorry, guys. I should have been more clear.

Each SET command needs it's own WHERE qualifiers, like this

UPDATE dbo.xxxxx
SET col1 = 'aa'
WHERE (ID IN (4500, 4511, 4522))
SET col2 = 'bb'
WHERE (ID IN (4533, 4544, 4555))

Thanks for your help !!

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-04-17 : 13:52:06


update dbo.xxx
   set col1 = case 
                  when id in (4500, 4511, 4522) then 'aa'
                  else col1
              end,
       col2 = case
                  when id in (4533, 4544, 4555) then 'bb'
                  else col2
              end

???

Kyle
Starting Member

7 Posts

Posted - 2002-04-17 : 14:15:30

Hmmmm,

That seems close, but SQL Server SQL Verifier bounced it. Incorrect sytax near keyword SET. I tested it exactly as sent.

Kyle
Starting Member

7 Posts

Posted - 2002-04-17 : 14:24:06

Oops... my bad !!

I used multiple SETs again ... it verifies ok now, thanks a million !!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources