Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old February 19th, 2006, 06:28 AM
Friend of Wrox
 
Join Date: Oct 2005
Location: , , United Kingdom.
Posts: 173
Thanks: 0
Thanked 2 Times in 1 Post
Default UPDATING 1 row with another row in same table

Hi All

This sort of leads on from another post of mine:

http://p2p.wrox.com/topic.asp?TOPIC_ID=40233

... COuld anyone advise me on what the SQL syntax would be to update 1 row with another rows data within the same table. e.g..

UPDATE TABLE1 (COL1, COL2, COL3) WHERE COL1 = 7 With the data from TABLE1 (COL1, COL2, COL3) WHERE COL1 = 4

Many thanks!

Rit
__________________
Rit
www.designandonline.co.uk
INSPIRE | CREATE | DELIVER
  #2 (permalink)  
Old February 19th, 2006, 06:51 AM
Friend of Wrox
 
Join Date: Oct 2005
Location: , , United Kingdom.
Posts: 173
Thanks: 0
Thanked 2 Times in 1 Post
Default

Sorry let me just tweak my example:

UPDATE TABLE1 (COL2, COL3) WHERE COL1 = 7 With the data from TABLE1 (COL2, COL3) WHERE COL1 = 4

... removed COL1 from update.
  #3 (permalink)  
Old February 19th, 2006, 06:53 AM
Friend of Wrox
 
Join Date: Oct 2005
Location: , , United Kingdom.
Posts: 173
Thanks: 0
Thanked 2 Times in 1 Post
Default

... Does this look like good practice?..

UPDATE TABLE1
SET
    COL2 = (SELECT COL2 FROM TABLE1 WHERE (COL1 = 4)),
    COL3 = (SELECT COL3 FROM TABLE1 WHERE (COL1 = 4))

WHERE COL1 = 7
  #4 (permalink)  
Old February 19th, 2006, 08:55 AM
SQLScott's Avatar
Wrox Author
 
Join Date: Dec 2004
Location: Wellington, FL , USA.
Posts: 338
Thanks: 0
Thanked 2 Times in 2 Posts
Default

Yes, your last example code will work (you don't need the parinthesis around the where clause.

UPDATE TABLE1
SET
    COL2 = (SELECT COL2 FROM TABLE1 WHERE COL1 = 4),
    COL3 = (SELECT COL3 FROM TABLE1 WHERE COL1 = 4)

WHERE COL1 = 7




Similar Threads
Thread Thread Starter Forum Replies Last Post
Scrollbar in row of Datagrid Except Header Row Theone84 ASP.NET 2.0 Professional 0 August 11th, 2008 12:10 AM
To retrieve a row by giving row number prad_a MySQL 1 March 22nd, 2007 11:20 PM
Read a column and Search Row by Row in another col AyatKh Excel VBA 2 January 26th, 2005 12:02 PM
first row of a table Adam H-W SQL Language 2 January 11th, 2005 10:15 AM
Manage data row by row in datagrid Dragonist Classic ASP Databases 5 July 29th, 2004 04:17 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.