INSERT TO UPDATE - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

INSERT TO UPDATE

Author

Topic

wtfn00b
Starting Member

6 Posts

Posted - 2010-10-21 : 08:25:49

Hello, I have this SQL, I need it to be transformet to UPDATE:

INSERT INTO [CAWI].[dbo].[tbl_pls2010] (lsn, p31_2, p32_2, p710_2, p720_2, p731_2, p732_2)
SELECT * FROM
(
  SELECT
    Resp_code, Rnr, Cvalue
  FROM [CAWI].[dbo].[VALUES] WHERE Rnr = '31' OR Rnr = '32' OR Rnr = '710' OR Rnr = '720' OR Rnr = '731' OR Rnr = '732' AND Cnr = '2'
) AS DataTable
PIVOT
(
  MAX(Cvalue)
  FOR Rnr
  IN (
   [31], [32], [710], [720], [731], [732]
  )
) AS PivotTable

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-23 : 02:34:57

[code]UPDATE d
SET d.p31_2 = r.[31],
p32_2=r.[32],
p710_2=r.[710],
p720_2=r.[720],
p731_2=r.[731],
p732_2=r.[732]
FROM [CAWI].[dbo].[tbl_pls2010] d
INNER JOIN (SELECT * FROM
(
SELECT
Resp_code, Rnr, Cvalue
FROM [CAWI].[dbo].[VALUES] WHERE Rnr = '31' OR Rnr = '32' OR Rnr = '710' OR Rnr = '720' OR Rnr = '731' OR Rnr = '732' AND Cnr = '2'
) AS DataTable
PIVOT
(
MAX(Cvalue)
FOR Rnr
IN (
[31], [32], [710], [720], [731], [732]
)
) AS PivotTable
)r
ON r.Resp_code = d.lsn
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

wtfn00b
Starting Member

6 Posts

Posted - 2010-10-25 : 08:44:48

Thank you it's works :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources