Switch Values in a column - 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

Switch Values in a column

Author

Topic

MagicCity77
Starting Member

19 Posts

Posted - 2008-10-01 : 11:09:28

I have a table:

TruckID TruckModel
1 Ford
2 TBN
3 Chevy
4 Toyota

How do I do a search for TBN and if the TruckID is not 1 then Switch the Value from that Record to TBN like the following:

TruckID TruckModel
1 TBN
2 Ford
3 Chevy
4 Toyota

I thought I could something like this

@TruckModel NVarchar(50) = 'TBN

Update tb_Truck
Set TruckModel
Where TruckModel = @TruckModel

But I'm not actually switching just replacing.

TBN needs to always be id = 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-01 : 11:26:48

[code]DECLARE @TruckID int

SELECT @TruckID=TruckID
FROM table
WHERE TruckModel='TBN'

UPDATE Table
SET @TruckModel=TruckModel,
TruckModel='TBN'
WHERE TruckID=1
AND TruckModel<>'TBN'

UPDATE Table
SET t.TruckModel=@TruckModel
WHERE t.TruckID=@TruckID
AND @TruckID<>1[/code]

MagicCity77
Starting Member

19 Posts

Posted - 2008-10-01 : 11:41:30

Thanks I'm going to try and run the query now.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-01 : 11:49:45

[code]DECLARE @Sample TABLE
(
ID INT PRIMARY KEY,
Model VARCHAR(200)
)

INSERT @Sample
SELECT 1, 'Ford' UNION ALL
SELECT 2, 'TBN' UNION ALL
SELECT 3, 'Chevy' UNION ALL
SELECT 4, 'Toyota'

DECLARE @ID INT

SELECT @ID = ID
FROM @Sample
WHERE Model = 'TBN'

SELECT *
FROM @Sample

UPDATE @Sample
SET ID = 1 + @ID - ID
WHERE ID IN (1, @ID)

SELECT *
FROM @Sample[/code]

E 12°55'05.63"
N 56°04'39.26"

MagicCity77
Starting Member

19 Posts

Posted - 2008-10-01 : 11:58:19

quote:

Originally posted by visakh16

DECLARE @TruckID int

SELECT @TruckID=TruckID
FROM table
WHERE TruckModel='TBN'

UPDATE Table
SET @TruckModel=TruckModel,
TruckModel='TBN'   
WHERE TruckID=1
AND TruckModel<>'TBN'

UPDATE Table
SET t.TruckModel=@TruckModel  
WHERE t.TruckID=@TruckID
AND @TruckID<>1

I received a few syntax errors bt here is what I placed in the query window.

DECLARE @TruckID int,
DECLARE @TruckModelnvarchar(50)

SELECT @TruckID=TruckID
FROM tb_Truck
WHERE TruckModel='TBN'

UPDATE tb_Truck
SET @TruckModel=TruckModel,
TruckModel='TBN'   
WHERE TruckID=1
AND TruckModel<>'TBN'

UPDATE tb_Truck
SET t.TruckModel=@TruckModel
WHERE t.TruckID=@TruckID
AND @TruckID<>1

The syntax error is:

Incorrect syntax near the keyword ' DECLARE'. Line 2

What is wrong with this????

raky
Aged Yak Warrior

767 Posts

Posted - 2008-10-01 : 12:04:02

hi,

make few changes as below

DECLARE @TruckID int,
DECLARE @TruckModel nvarchar(50)

SELECT @TruckID=TruckID
FROM tb_Truck
WHERE TruckModel='TBN'

UPDATE tb_Truck
SET @TruckModel=TruckModel,
TruckModel='TBN'
WHERE TruckID=1
AND TruckModel<>'TBN'

UPDATE t
SET t.TruckModel=@TruckModel
FROM tb_Truck t
WHERE t.TruckID=@TruckID
AND @TruckID<>1

Leave one space between @TruckModel and nvarchar(50)

MagicCity77
Starting Member

19 Posts

Posted - 2008-10-01 : 12:04:02

I'm going to take a look at union to understand what it is doing then try your code. Where you have @Sample would I change that to the actual table name?

quote:

Originally posted by Peso

DECLARE	@Sample TABLE
	(
		ID INT PRIMARY KEY,
		Model VARCHAR(200)
	)

INSERT	@Sample
SELECT	1, 'Ford' UNION ALL
SELECT	2, 'TBN' UNION ALL
SELECT	3, 'Chevy' UNION ALL
SELECT	4, 'Toyota'

DECLARE	@ID INT

SELECT	@ID = ID
FROM	@Sample
WHERE	Model = 'TBN'

SELECT	*
FROM	@Sample

UPDATE	@Sample
SET	ID = 1 + @ID - ID
WHERE	ID IN (1, @ID)

SELECT	*
FROM	@Sample

E 12°55'05.63"
N 56°04'39.26"

raky
Aged Yak Warrior

767 Posts

Posted - 2008-10-01 : 12:10:31

quote:
Originally posted by MagicCity77

I'm going to take a look at union to understand what it is doing then try your code. Where you have @Sample would I change that to the actual table name?

quote:
Originally posted by Peso

DECLARE @Sample TABLE
(
ID INT PRIMARY KEY,
Model VARCHAR(200)
)

INSERT @Sample
SELECT 1, 'Ford' UNION ALL
SELECT 2, 'TBN' UNION ALL
SELECT 3, 'Chevy' UNION ALL
SELECT 4, 'Toyota'

DECLARE @ID INT

SELECT @ID = ID
FROM @Sample
WHERE Model = 'TBN'

SELECT *
FROM @Sample

UPDATE @Sample
SET ID = 1 + @ID - ID
WHERE ID IN (1, @ID)

SELECT *
FROM @Sample

E 12°55'05.63"
N 56°04'39.26"

U can use the below which is suggested by Peso

DECLARE @ID INT

SELECT @ID = TruckID
FROM tb_Truck
WHERE TruckModel = 'TBN'

SELECT *
FROM tb_Truck

UPDATE tb_Truck
SET TruckID = 1 + @ID - TruckID
WHERE TruckID IN (1, @ID)

SELECT *
FROM tb_Truck

MagicCity77
Starting Member

19 Posts

Posted - 2008-10-01 : 12:10:57

quote:
Originally posted by raky

hi,

make few changes as below

DECLARE @TruckID int,
DECLARE @TruckModel nvarchar(50)

SELECT @TruckID=TruckID
FROM tb_Truck
WHERE TruckModel='TBN'

UPDATE tb_Truck
SET @TruckModel=TruckModel,
TruckModel='TBN'
WHERE TruckID=1
AND TruckModel<>'TBN'

UPDATE t
SET t.TruckModel=@TruckModel
FROM tb_Truck t
WHERE t.TruckID=@TruckID
AND @TruckID<>1

Leave one space between @TruckModel and nvarchar(50)

Why is it giving me this error:

The syntax error is:

Incorrect syntax near the keyword ' DECLARE'. Line 2

raky
Aged Yak Warrior

767 Posts

Posted - 2008-10-01 : 12:13:16

Have u read my posts

It is giving such type of error because U did not Left atleast one space after variablename and datatype for that Variable

Ok then use this code with slight modification for Vishak's Solution

DECLARE @TruckID int
DECLARE @TruckModel nvarchar(50)

SELECT @TruckID=TruckID
FROM tb_Truck
WHERE TruckModel='TBN'

UPDATE tb_Truck
SET @TruckModel=TruckModel,
TruckModel='TBN'
WHERE TruckID=1
AND TruckModel<>'TBN'

UPDATE t
SET t.TruckModel=@TruckModel
FROM tb_Truck t
WHERE t.TruckID=@TruckID
AND @TruckID<>1

MagicCity77
Starting Member

19 Posts

Posted - 2008-10-01 : 12:16:13

quote:
Originally posted by raky

Have u read my posts

It is giving such type of error because U did not Left atleast one space after variablename and datatype for that Variable

The space issue only occured when I pasted it into the messagebox. There is a space in between the variable name and the datatype in my query window.

raky
Aged Yak Warrior

767 Posts

Posted - 2008-10-01 : 12:23:15

quote:
Originally posted by MagicCity77

quote:
Originally posted by raky

Have u read my posts

It is giving such type of error because U did not Left atleast one space after variablename and datatype for that Variable

The space issue only occured when I pasted it into the messagebox. There is a space in between the variable name and the datatype in my query window.

Remove the Comma at the end of @TruckId Declaration

MagicCity77
Starting Member

19 Posts

Posted - 2008-10-01 : 12:25:58

quote:
Originally posted by raky

quote:
Originally posted by MagicCity77

quote:
Originally posted by raky

Have u read my posts

It is giving such type of error because U did not Left atleast one space after variablename and datatype for that Variable

The space issue only occured when I pasted it into the messagebox. There is a space in between the variable name and the datatype in my query window.

Remove the Comma at the end of @TruckId Declaration

Ok, removed the comma and ran the query.

Gave the message that 1 row was affected how ever the result showed NULL

MagicCity77
Starting Member

19 Posts

Posted - 2008-10-01 : 12:30:41

Never mind, after opening the truck table again the cahnges actually displayed.

Thanks everyone.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources