Update syntax.. - 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

Update syntax..

Author

Topic

Firefly17
Yak Posting Veteran

78 Posts

Posted - 2005-09-07 : 02:45:19

Hi (again)

I know that it's very simple question but since I know that many gurus are here, I want to ask abt it.

Ssql = "Update Holder set PID='temp_PID',CMSN=sMSN,CSN=sMSN1 where HID= " & temp_HID

when i run that sql from VB, it gives error as INVALID COLUMN name(sMSN/sMSN1).., it is for both sMSN and sMSN1 . But both of these sMSN and sMSN1 are numeric in the table. so I think I don't need for quotes.

pls tell me what is the correct syntax for it?

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-09-07 : 03:00:52

you're using dynamic sql, try to print the text you've just constructed and see if the values add up,

in this case, the new values for cmsn and csn are column names also?
what you need is a an update with a subquery or join like this

quote:

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'

--------------------
keeping it simple...

Kristen
Test

22859 Posts

Posted - 2005-09-07 : 03:04:57

You could run this to check that everything is spelled correctly, and to check the datatype in the database:


SELECT	MyName, COLUMN_NAME, DATA_TYPE
FROM	
(
	SELECT	'PID' AS MyName
	UNION ALL SELECT 'CMSN'
	UNION ALL SELECT 'sMSN'
	UNION ALL SELECT 'CSN'
	UNION ALL SELECT 'sMSN1'
	UNION ALL SELECT 'HID'
) X
	LEFT OUTER JOIN INFORMATION_SCHEMA.COLUMNS
		ON TABLE_NAME = 'Holder'
		AND COLUMN_NAME = MyName

Kristen

Firefly17
Yak Posting Veteran

78 Posts

Posted - 2005-09-07 : 03:55:50

Do i need join? here I'm using only one table.. I've tested as Kristen told me and the spellings and all are correct.

ssql = "Update Holder set PID='temp_PID',CMSN='sMSN',CSN=sMSN1 where HID= " & temp_HID

here only CSN is numeric in the table. The rest are varchar in MSSQL. sMSN1 is Long in vb. if i put quote (' '), it says that can't convert varchar to numeric, If I don't put the quote , it says invalid column name sMSN1.

but if the variable is string, do we need quotes? ( that is another issue :( ).. right now is sMSN1 problem..

any idea??

tks alot for prompt replies

Kristen
Test

22859 Posts

Posted - 2005-09-07 : 03:58:48

"only CSN is numeric in the table. The rest are varchar"

In that case you might need:

ssql = "Update Holder set PID='temp_PID',CMSN='sMSN',CSN=CAST(sMSN1 AS CorrectNumericDataType) where HID= " & temp_HID

You might want to try my earlier suggestion to double check your column names

Kristen

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-09-07 : 23:08:33

you need a from clause since your source is also a destination

if you put quotes around the values after the '=', then you're asking sql to place those string values ('sMSN' and 'sMSN1') into those columns not whatever value sMSN/sMSN1 holds

--------------------
keeping it simple...

Kristen
Test

22859 Posts

Posted - 2005-09-08 : 06:02:58

"quotes around column names"

Blimey, my eagle-eye missed that (although it wasn't in the original post)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources