Syntax Problem - 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)

Syntax Problem

Author

Topic

SteveL
Starting Member

4 Posts

Posted - 2006-02-24 : 10:44:17

I'm horrible with TRANSACT-SQL syntax, I'mstruggling with a relatively simple project and hoping someone might be able to help me out.
I have a table called "Userconfig". In the table there is a field called "ServerConfigId". The ServerConfigId field will only have a value of either "1" or "2". I want to write a query which basically says (IF the ServerConfigId field in the Userconfig Table is "1", change it to "2". If the ServerConfigId field in the Userconfig Table is "1", change it to "2".
I tried something like this, and I can't seem to get the syntax correct:

UPDATE UserConfig
IF UserConfigId = 3
SET UserConfigId = '1'
ELSE Set UserConfigId = '3'

I've been having a hard time finding any documentation online thatreally gets to the bottom of this. This would be the easiest way to solve my problem. In a perfrect world, I'd love to be able to do this:

1) Determine all the values assigned to the ID field in the ServerConfig table.

2) Determine the value assigned to the ServerConfigId in the UserConfig table.

3) (Since the ServerConfigId field in the Userconfig table contain only values which are assigned to the ID field on the ServerConfig table) Whichever value is assigned to the ServerConfigId field inthe userconfig table, change the value to the other value listed in the ID field of the Serverconfig table.

Since my first option is significantly easier, I can absolutely live with that slution. But if anyone has any suggestoins to make it a littel more elignt, as described in my second scneario that would be GREATLY appreciatd as well. Basically, any advice is appreciated.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-02-24 : 11:16:21

quote:
Originally posted by SteveL

I want to write a query which basically says
(IF the ServerConfigId field in the Userconfig Table is "1", change it to "2".
If the ServerConfigId field in the Userconfig Table is "1", change it to "2".

I do not c any difference between the 2 statements above (Except for the Upper Case 'F' in the top 1.

quote:

UPDATE UserConfig
IF UserConfigId = 3
SET UserConfigId = '1'
ELSE Set UserConfigId = '3'

From where did u find this SQL Syntax ?
Also, in ur first part of Q, u say that UserConfigId would be 1 or 2, and now u r saying "IF UserConfigId = 3" ??

If u tell us what u want with some sample data and what it should be after the update, u may get the answer.

May be u r looking for something like following:

Update UserConfig Set UserConfigId = 1 where UserConfigId = 2

or u want all 2 --> 1 & 1 --> 2 ??

If so U may need to run 3 Queries (for safety)

Update UserConfig Set UserConfigId = 7 where UserConfigId = 1
Update UserConfig Set UserConfigId = 1 where UserConfigId = 2
Update UserConfig Set UserConfigId = 2 where UserConfigId = 7

SteveL
Starting Member

4 Posts

Posted - 2006-02-24 : 14:04:24

I was researching the IF, THEN statements, thinking that my expression would be along the lines of: Set value to 1 if value is 2, else set to 1. The resolution you provided absolutely works. It would have never occured to me to change to a 3rd (arbirtrary value).
Is there a good resource (besides the online books) to reference the key commands and syntax?

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-02-24 : 14:19:37

U have to Learn Basic SQL Syntax apart from using BOL
Visit a site like www.w3schools.com
and improve ur SQL knowledge (in addition to MS SQL server & T-SQL knowledge)

BOL is very good if u have an idea on what u r looking for

SteveL
Starting Member

4 Posts

Posted - 2006-03-23 : 19:20:04

I'm still wrestling with this a little. I used the statement suggested above, and this worked extremely well in a lab, when I attempted to try this on the server it very quickly became apparent that there were some issues. I'm dealing with 2 tables (ServerConfig & UserConfig). The ServerConfig table lists available servers, and has a primary key constraint between the ID, Service Name, and MAchine Name fields. The UserConfig table lists the users, and also has a ServerConfigId field. There is a foreign key constraint between ServerConfigId.UserConfig and Id.ServerConfig. I obviously encoutner an error when I attempt to run this because there is no value of "7" in the ID field of the Serverconfig table.
Since I'm dealing with 2 named instances on 2 different servers, i tried to execute:

If @@SERVERNAME = <hostname>
UPDATE UserConfig SET ServerConfigId = 1
ELSE UPDATE UserConfig SET ServerConfigId = 5

For some reason this appears to work backward. I ran SELECT @@SERVERNAME and both servers are returning the same exact name despite the fact that both have unique host names. I'm trying to get some logic in this that first determines what server is active and changes to the other option.

I've tried
IF (SELECT Id FROM ServerConfig WHERE MachineName = '<hostname>') = (SELECT ServerConfigId FROM UserConfig WHERE ServerConfigId = '1')
UPDATE UserConfig SET ServerConfigId = 5
ELSE UPDATE UserConfig SET ServerConfigId = 1

This appears to only work once, then each subsequent time it fails with the error:

"Subquery returned more than one value. This is not permitted when teh subquery follows =.... or the subquery is used as an expression.

I think I'm fairly close, but I'm just not sure where to go from here. Any help would be greatly appreciated.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-23 : 20:00:47

[code]Declare @ID int
Set @ID = (SELECT Id FROM ServerConfig WHERE MachineName = '<hostname>')

if exists (SELECT ServerConfigId FROM UserConfig WHERE ServerConfigId = 1' and ServerConfigId = @ID )

UPDATE UserConfig SET ServerConfigId = 5
ELSE
UPDATE UserConfig SET ServerConfigId = 1[/code]

SteveL
Starting Member

4 Posts

Posted - 2006-03-24 : 11:00:38

That worked perfectly and also makes perfect sense. I really appreciate your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources