0

I am going to perform an UPDATE on the result of a query in SQL Server 2008 but it seems UPDATE needs a name for table it is not possible.

I am going to do this 

UPDATE (SELECT ~)
set x = "1"
where x IS NULL

BUT really I have problem in "VIEW" to execute this code in SQL Server 2008.

My table:

CREATE TABLE [dbo].[Table_1]
(
    [id] [int] NOT NULL,
    [rep] [nchar](10) NOT NULL,
    [qty] [int] NOT NULL,
    [sell] [int] NOT NULL,
    [settled] [bit] NULL
) ON [PRIMARY]

My code for update and the select inside is : 

update (
SELECT rep, Expr1, Expr2, Expr3, Expr4, Expr5
FROM (SELECT     o.rep, o.Expr1, o.Expr2, p.rep AS Expr3, p.Expr1 AS Expr4, p.Expr2 AS Expr5
FROM (SELECT     oo.rep, SUM(oo.qty) AS Expr1, SUM(oo.sell) AS Expr2
FROM          dbo.Table_1 AS oo
WHERE      (oo.settled = 1)
GROUP BY oo.rep) AS o FULL OUTER JOIN
(SELECT     pp.rep, SUM(pp.qty) AS Expr1, SUM(pp.sell) AS Expr2
FROM          dbo.Table_1 AS pp
WHERE      (pp.settled = 0)
GROUP BY pp.rep) AS p ON o.rep = p.rep) AS derivedtbl_1
)

set rep = Expr4
where rep IS NULL

Is there any body to help me to find it how is it possible to perform an update on the result of a query?

I have read many script for other DBs but SQL Server 2008 is different as I think.

The main aim to do this code is to fill null fields in "rep" which are created after FULL OUTER JOIN. I will appreciate if is there any other easier way to fill the fields instead of update. This is my idea.

Thanks for your time .

Sample date :

Table :

rep    qty   sell     settled
------------------------------
a       10     20      true
b       20     40      true
c       10     20      False
a       11     22      False

The result is like this : 

rep    Expr1    Expr2    Expr3      Expr4     Expr5
-----------------------------------------------------
a      10        20       a         11          22
b      20        40       null      null        null
null   null      null     c         10          20

(I am trying to fill the rep for Expr3 with name of operator instead of null)

Kinbds

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mo Fotouhi
  • 3
  • 2

1 Answers1

0

You are talking about Correlated Update feature in Oracle. That's not the case with SQL Server though. You need to simplify it and make some updates on the specific table.

Refer :

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60899

update one table with data from another

Community
  • 1
  • 1
Ash
  • 2,575
  • 2
  • 19
  • 27
  • Thanks for your help , but actually I just have one table not two! – Mo Fotouhi Oct 01 '14 at 02:51
  • I am going to separate the records by one logic field – Mo Fotouhi Oct 01 '14 at 02:51
  • That's the way to go forward. Bravo ! – Ash Oct 01 '14 at 02:52
  • and after that group by name , but some name get null because there is not value for them . and that is why I need to update the query to fill those field. – Mo Fotouhi Oct 01 '14 at 02:52
  • I think all you want is the GET the results of query with no NULL values. You don't want any updates to physical tables, right? – Ash Oct 01 '14 at 02:53
  • for replacing NULL values in SELECT query, you can have ISNULL(o.rep, p.Expr1) as rep. Or you can use CASE in query.http://msdn.microsoft.com/en-au/library/ms181765.aspx – Ash Oct 01 '14 at 03:00