Updating a row based on another row in the table.

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

Updating a row based on another row in the table.

Author

Topic

SergioM
Posting Yak Master

170 Posts

Posted - 2013-08-06 : 17:43:36

I have a table in SQL server which has two rows. One has an ID of 'Bag CL55412'. Another has an ID of 'Bag CL55412-Cpy'. The Price for the first one is $99.99. I want to make the price for the second one 2% more & $.99 more.

The data looks like this

ID	ChannelPrice	CompanyID
Bag CL55412	99.99	111
Bag CL55412-Cpy	102.99	500

The SQL to select that formula looks like this.

SELECT [ID]
      ,[ChannelPrice]
      ,(CEILING([ChannelPrice]*1.02)+.99)
      ,CompanyID
FROM [SC].[dbo].[Product]
WHERE ID like '%CL55412%'

To Update, I can think of something like this, but it will update based on itself, not a different row in the table.

UPDATE [SC].[dbo].[Product] SET ChannelPrice=(CEILING([ChannelPrice]*1.02)+.99)

How would I get it to update as I want it to? The origin CompanyID will always be 111 & the destination company ID will always be 500 for all of the respective rows that need to be updated

-Sergio
I use Microsoft SQL 2008

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-08-06 : 17:46:50

UPDATE [SC].[dbo].[Product]
SET ChannelPrice=(SELECT (CEILING([ChannelPrice]*1.02)+.99) FROM Product WHERE ID = 'Bag CL55412')
WHERE ID = 'Bag CL55412-Cpy'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

SergioM
Posting Yak Master

170 Posts

Posted - 2013-08-06 : 17:51:13

Thanks Tara! I foolishly forgot to mention that it is not just for 1 row. That was only an example. This would need to update every row in that table.

-Sergio
I use Microsoft SQL 2008

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-08-06 : 19:15:17

It is not clear what criteria you wanted to use to match the target and source rows when you have large data, here is an example of how to update a column value from one row from the same column from another row from the same table.
[CODE]

-- TEST DATA:
DECLARE @Product TABLE(ID VARCHAR(20), ChannelPrice Numeric(12,2), CompanyID INT);
INSERT INTO @Product VALUES
('Bag CL55412', 99.99, 111),
('Bag CL55412-Cpy', 102.99, 500),
('Bag DL55412', 80, 111),
('Bag DL55412-Cpy', 100, 500),
('Bag EL55412', 90, 111),
('Bag EL55412-Cpy', 105, 500);

; WITH CTE AS
(SELECT ROW_NUMBER() OVER(PARTITION BY LEFT(ID, 11) ORDER BY ID) AS RN, * FROM @Product)
UPDATE P
SET ChannelPrice=(SELECT (CEILING([ChannelPrice]*1.02)+.99) FROM CTE WHERE LEFT(ID, 11) = LEFT(P.ID, 11) and RN = 2)
FROM @Product P INNER JOIN CTE T ON T.ID = P.ID and T.RN = 1;

-- DATA BEFORE UPDATE:
Bag CL55412 99.99 111
Bag CL55412-Cpy 102.99 500
Bag DL55412 80.00 111
Bag DL55412-Cpy 100.00 500
Bag EL55412 90.00 111
Bag EL55412-Cpy 105.00 500

-- DATA AFTER UPDATE:
Bag CL55412 106.99 111
Bag CL55412-Cpy 102.99 500
Bag DL55412 102.99 111
Bag DL55412-Cpy 100.00 500
Bag EL55412 108.99 111
Bag EL55412-Cpy 105.00 500
[/CODE]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources