delete - 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

General SQL Server Forums

New to SQL Server Programming

delete

Author

Topic

JJins
Yak Posting Veteran

81 Posts

Posted - 2011-11-02 : 09:21:20

I am trying to delete collumn rate and then insert new rates. Here is what I have.

Msg 208, Level 16, State 1, Line 1
Invalid object name 'ratedata.rate'.

delete ratedata.rate
FROM Programs INNER JOIN
RateData ON Programs.ProgramID = RateData.ProgramID
WHERE COVERAGEID = 3 AND POLICYTYPE IN (3,4) AND STATE = 'NC' AND OCCUPANCYTYPEID not in (5,6,7,8)

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-11-02 : 09:26:04

http://www.w3schools.com/sql/sql_update.asp

vikki.seth
Yak Posting Veteran

66 Posts

Posted - 2011-11-02 : 09:26:27

is your table name ratedata? and column name rate?
if yes, the query is incorrect. Are you trying to update that column with 0 or null? Or are you trying to delete entire rows that match the criteria?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-02 : 10:11:08

quote:
Originally posted by JJins

I am trying to delete collumn rate and then insert new rates. Here is what I have.

Msg 208, Level 16, State 1, Line 1
Invalid object name 'ratedata.rate'.

delete ratedata.rate
FROM Programs INNER JOIN
RateData ON Programs.ProgramID = RateData.ProgramID
WHERE COVERAGEID = 3 AND POLICYTYPE IN (3,4) AND STATE = 'NC' AND OCCUPANCYTYPEID not in (5,6,7,8)

you cant delete a column
either you can modify its contents using UPDATE
or you drop it altogether using ALTER TABLE... DROP..

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

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-11-02 : 10:12:26

delete THEN insert? sounds like a textbook UPDATE to me. We need a LOT more information here.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-11-03 : 04:35:48

If you want to only delete contents of rate column then you need to use update statement with inner join rather then delete.

If you want to delete all the rows that match in inner join then you will insert all again with new rate then
I think this should work

delete ratedata
FROM ratedata
INNER JOIN
Programs ON RateData.ProgramID=Programs.ProgramID
WHERE COVERAGEID = 3 AND POLICYTYPE IN (3,4) AND STATE = 'NC' AND OCCUPANCYTYPEID not in (5,6,7,8)

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources