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

Replace

Author

Topic

jfm
Posting Yak Master

145 Posts

Posted - 2013-05-02 : 11:27:56

Hi there,

I have the following tables:

Table_1 as a
I need to replace some values in col_5 when col_value = '*'

Table_2 as b
I have the values to replace when table_1 = '*' and a.Id_col = b.Id_col

I'm using this query:

update S
set s.PP = b.PP
from S as s
inner join pp_wr as b
on s.CN = b.CN
and s.tt = b.tt
and s.I = b.I
and s.I_D = b.I_D
where s.PP = '*'

The matter is that SQL is not recognising col_PP ...

Any idea?

Thanks

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-02 : 12:21:20

Does the table have a column named col_PP (or PP as it is in your query)? Also, is your real table name "S"? You can find what columns it has by doing a "select top 0 * from S" or using this query

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'S'

jfm
Posting Yak Master

145 Posts

Posted - 2013-05-02 : 12:48:36

Hi James,

Yes, the column name is PP but is just typed Col_PP to specify its a column. It is the real name of the column.

I don't understand: You can find what columns it has by doing (...) you mean what rows it has by doing (...) ?

thank you

quote:
Originally posted by James K

Does the table have a column named col_PP (or PP as it is in your query)? Also, is your real table name "S"? You can find what columns it has by doing a "select top 0 * from S" or using this query
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'S'

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-02 : 12:59:20

quote:
I don't understand: You can find what columns it has by doing (...) you mean what rows it has by doing (...) ?

No, I meant what columns. WHen you said "SQL is not recognizing col_PP", I took that to mean that you got an error message to the effect that col_PP could not be found. If that is the case, I was suggesting that you should look up if there indeed is a column by that name in the table. If that is not what the error message sent, can you post the exact text of the error message?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources