Update Statement - 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 2000 Forums

Transact-SQL (2000)

Update Statement

Author

Topic

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-04-21 : 07:34:37

Hi i would like to include this statement in a update statement, can some please advise if it is possible. as am getting errors when i run it?

UPDATE (TABLE NAME)
SET COL_NAME = ''
WHERE CASEID IN ( SELECT tbl_Voy_RCATransfer_1.StartTime AS Field_time, dbo.tbl_Voy_RCATransfer.StartTime AS Cancel_time, dbo.tbl_Voy_RCATransfer.Cancelled, tbl_Voy_RCATransfer_1.ServiceEvent, tbl_Voy_RCATransfer_1.SerialNum, dbo.tbl_Voy_RCATransfer.CaseID
FROM dbo.tbl_Voy_RCATransfer INNER JOIN
dbo.tbl_Voy_RCATransfer tbl_Voy_RCATransfer_1 ON dbo.tbl_Voy_RCATransfer.SerialNum = tbl_Voy_RCATransfer_1.SerialNum
GROUP BY dbo.tbl_Voy_RCATransfer.Cancelled, tbl_Voy_RCATransfer_1.ServiceEvent, tbl_Voy_RCATransfer_1.SerialNum, tbl_Voy_RCATransfer_1.StartTime,
dbo.tbl_Voy_RCATransfer.StartTime, dbo.tbl_Voy_RCATransfer.CaseID
HAVING (dbo.tbl_Voy_RCATransfer.Cancelled = 't') AND (tbl_Voy_RCATransfer_1.ServiceEvent = 't'))
ORDER BY dbo.tbl_Voy_RCATransfer.StartTime

Kristen
Test

22859 Posts

Posted - 2006-04-21 : 08:17:28

You need to specify the actual name of the table instead of:

UPDATE (TABLE NAME)

and you can't have an ORDER BY in an update statement

If that isn't the answer please post the error message you are getting - bit hard to guess what it might be without it!

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-21 : 09:04:09

Also your subquery should have only one column returned

Madhivanan

Failing to plan is Planning to fail

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-04-21 : 11:00:25

It works fine now, here is what it looks like, also i can't use more than one expression in the select statement if i use the IN key word.

UPDATE dbo.tbl_Voy_RCATransfer
SET Cancel_Field_Calls = 1
WHERE CASEID IN (SELECT dbo.tbl_Voy_RCATransfer.CASEID
FROM dbo.tbl_Voy_RCATransfer INNER JOIN
dbo.tbl_Voy_RCATransfer tbl_Voy_RCATransfer_1 ON dbo.tbl_Voy_RCATransfer.SerialNum = tbl_Voy_RCATransfer_1.SerialNum
-- AND dbo.tbl_Voy_RCATransfer.SvsRID = tbl_Voy_RCATransfer_1.SvsRID
AND dbo.tbl_Voy_RCATransfer.CountryCD3 = tbl_Voy_RCATransfer_1.CountryCD3
AND (dbo.tbl_Voy_RCATransfer.ServiceEvent = 't') AND (tbl_Voy_RCATransfer_1.Cancelled = 't'))

Kristen
Test

22859 Posts

Posted - 2006-04-21 : 14:41:17

"i can't use more than one expression in the select statement if i use the IN key word"

If you care to post an example of how you were trying to use Multiple Expressions in an IN we can point you to alternatives (I'd just be guessing at what you are trying if I didn't ask!)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources