Update the column - 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 2005 Forums

Transact-SQL (2005)

Update the column

Author

Topic

lovehui
Yak Posting Veteran

60 Posts

Posted - 2009-05-27 : 18:07:05

Hi,

I create a temp table and insert records.
Then I want to update a column.

Can we do like

update #Data set CHANGE =
abs(ENTERED_AMOUNT - abs(TENDER_AMOUNT)) * -1 where TYPE not in (1, 2)

I play it around, sometimes I get a null return or get an error

Subquery returned more than 1 value

Thanks

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2009-05-27 : 23:56:38

Updates don't return any data, so if you get data returned you must have a SELECT as well as the UPDATE?

The statement you gave doesn't have a subquery in it, so that error about a subquery returning more than one value must be in another statement too.

lovehui
Yak Posting Veteran

60 Posts

Posted - 2009-05-28 : 10:15:32

can you look at this one?
Subquery returned more than 1 value.
Is something wrong? If I comment out this then this error disappears.

exec('update #Data set TRAN_TYPE = 
(select 
	CASE A.TRAN_CD 

		WHEN 1 THEN ''Sale''
		WHEN 2 THEN ''Return''
		WHEN 3 THEN ''Exchange''
		WHEN 4 THEN ''Price Match''
				ELSE ''Other''
	END
FROM HEADER A WHERE #Data.TRANS_NUMBER = A.TRAN_ID)')

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-28 : 10:43:09

Try this


exec('update d set TRAN_TYPE = t.TRAN_TYPE from #Data as d inner join
(select TRAN_ID,
	CASE A.TRAN_CD 

		WHEN 1 THEN ''Sale''
		WHEN 2 THEN ''Return''
		WHEN 3 THEN ''Exchange''
		WHEN 4 THEN ''Price Match''
				ELSE ''Other''
	END as TRAN_TYPE
FROM HEADER ) as t on WHERE d.TRANS_NUMBER = t.TRAN_ID)')

Madhivanan

Failing to plan is Planning to fail

lovehui
Yak Posting Veteran

60 Posts

Posted - 2009-05-28 : 11:00:33

Invalid column name 'TRAN_ID'.
exec('update d set TRAN_TYPE = t.TRAN_TYPE from #Data as d inner join
(select TRAN_ID,
CASE TRAN_CD

WHEN 1 THEN ''Sale''
WHEN 2 THEN ''Return''
WHEN 3 THEN ''Exchange''
WHEN 4 THEN ''Price Match''
ELSE ''Other''
END as TRAN_TYPE
FROM HEADER ) as t on WHERE d.TRANS_NUMBER = t.TRAN_ID')

lovehui
Yak Posting Veteran

60 Posts

Posted - 2009-05-28 : 11:20:23

Ignore it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-28 : 11:39:24

whats the need of dynamic sql here?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-29 : 02:04:50