quote:

---

Subqueries in UPDATE, DELETE, and INSERT Statements
Subqueries can be nested in UPDATE, DELETE, and INSERT statements, as well as in SELECT statements.

The following query doubles the price of all books published by New Moon Books. The query updates the titles table; its subquery references the publishers table.

UPDATE titles
SET price = price * 2
WHERE pub_id IN
(SELECT pub_id
FROM publishers
WHERE pub_name = 'New Moon Books')

Here's an equivalent UPDATE statement using a join:

UPDATE titles
SET price = price * 2
FROM titles INNER JOIN publishers ON titles.pub_id = publishers.pub_id
AND pub_name = 'New Moon Books'

---

Update G
set clbal=amount+gcl.amt from Gen 
	inner join
	(select top 100 percent glcode, (case when sum(txn.amount)=' ' or 
	sum(txn.amount) is null then 0 else sum(txn.amount) end) as tamt from txn group by 
	glcode order by glcode) as gcl
on G.glcode = gcl.glcode

quote:

---

Originally posted by madhivanan

Before trying this take a backup of Gen table and if the updation is wrong restore it

Update G
set clbal=amount+gcl.amt from Gen 
	inner join
	(select top 100 percent glcode, (case when sum(txn.amount)=' ' or 
	sum(txn.amount) is null then 0 else sum(txn.amount) end) as tamt from txn group by 
	glcode order by glcode) as gcl
on G.glcode = gcl.glcode

Madhivanan

Failing to plan is Planning to fail

---

UPDATE MyTable
SET ColA = 'FOO'
WHERE ColB = 'BAR'

UPDATE U
SET 
     ColA = 'FOO'
FROM MyTable AS U
WHERE ColB = 'BAR'

-- UPDATE U
SET 
-- SELECT ColB, ColC, ColA AS [ColA_ORIGINAL], 
     ColA = 'FOO'
-- SELECT *
FROM MyTable AS U
WHERE ColB = 'BAR'

BEGIN TRANSACTION
UPDATE U
SET 
-- SELECT ColB, ColC, ColA AS [ColA_ORIGINAL], 
     ColA = 'FOO'
-- SELECT *
FROM MyTable AS U
WHERE ColB = 'BAR'
--
ROLLBACK
-- COMMIT

CREATE TABLE TEST051022
(
	MyPK varchar(10) NOT NULL,
	MyData varchar(50) NOT NULL,
	PRIMARY KEY
	(
		MyPK
	)
)
GO
PRINT 'Insert initial 1'
GO
INSERT INTO TEST051022 VALUES (10, 'Ten (Original)')
GO

-- OK, We have "some" data in the table
-- Now we will generate an error, and then insert an extra row

PRINT 'Begin Trans'
GO
BEGIN TRANSACTION
GO

-- This will cause error and will rollback the transaction!
PRINT 'Re-create duplicate table'
GO
CREATE TABLE TEST051022
(
	MyPK varchar(10) NOT NULL,
	MyData varchar(50) NOT NULL,
	PRIMARY KEY
	(
		MyPK
	)
)
GO

-- This will succeed 
-- NOTE The transaction is now "dead" so this is OUTSIDE the transaction block!
PRINT 'Insert 20'
GO
INSERT INTO TEST051022 VALUES (20, 'Twenty')
GO

-- This rollback will fail!
PRINT 'Rollback'
GO
ROLLBACK
GO

PRINT 'Rows in the table are:'
GO
SELECT * FROM TEST051022
GO
DROP TABLE TEST051022
GO

Insert initial 1

(1 row(s) affected)

Begin Trans
Re-create duplicate table
Server: Msg 2714, Level 16, State 6, Line 1
There is already an object named 'TEST051022' in the database.

Insert 20

(1 row(s) affected)

Rollback
Server: Msg 3903, Level 16, State 1, Line 1
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

Rows in the table are:
MyPK       MyData                                             
---------- -------------------------------------------------- 
10         Ten (Original)
20         Twenty

(2 row(s) affected)