Updating Multiple Columns [w/out typing all out]

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

Updating Multiple Columns [w/out typing all out]

Author

Topic

mathmath
Starting Member

13 Posts

Posted - 2009-06-25 : 04:36:16

Hi all,
I am trying to update Table[land] with the following data (simplified):

Scenarios:

Num_1 = [A-Z], Num_1_Index = [1-9]
Num_2 = [A-Z], Num_2_Index = [1-9]
.....
Num_26 = [A-Z], Num_26_Index = [1-9]
---------------------------------------------
Update land
set Num_1_Index = case
when Num_1 like 'A' then '1'
when Num_1 like 'B' then '2'
......
when Num_1 like 'Z' then '26'
else Num_1_Index
end
go

Update land
set Num_2_Index = case
when Num_2 like 'A' then '1'
when Num_2 like 'B' then '2'
......
when Num_2 like 'Z' then '26'
else Num_2_Index
end
go
.....
.....

Update land
set Num_26_Index = case
when Num_26 like 'A' then '1'
when Num_26 like 'B' then '2'
......
when Num_26 like 'Z' then '26'
else Num_26_Index
end
go

Objectives
Anything after "then" is fixed standardization. Anything after like is general data.
I want to update all the columns w/ the same standardizations. w/out replicating the query 26 times.

Thank you very much for the time.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-25 : 04:53:15

This reduces the number of queries to one only.

UPDATE	Land
SET	Num_1_Index =	CASE
				WHEN Num_1 LIKE '[A-Z]' THEN ASCII(UPPER(Num_1)) - 64
				ELSE Num_1_Index
			END,
	Num_2_Index =	CASE
				WHEN Num_2 LIKE '[A-Z]' THEN ASCII(UPPER(Num_2)) - 64
				ELSE Num_2_Index
			END

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

mathmath
Starting Member

13 Posts

Posted - 2009-06-26 : 03:09:13

quote:

Originally posted by Peso

This reduces the number of queries to one only.

UPDATE	Land
SET	Num_1_Index =	CASE
				WHEN Num_1 LIKE '[A-Z]' THEN ASCII(UPPER(Num_1)) - 64
				ELSE Num_1_Index
			END,
	Num_2_Index =	CASE
				WHEN Num_2 LIKE '[A-Z]' THEN ASCII(UPPER(Num_2)) - 64
				ELSE Num_2_Index
			END

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

Thanks Peso.

But my original data is more complicated than that, as follow:

Update land
set Num_#_Index = case
when Num_# like 'FRUITS' then 'F1'
when Num_# like 'VEGETABLE' then 'V2'
when Num_# like 'CANDY' then 'C3'
when Num_# like 'WOOD' then 'W5'
...........
else Num_#_Index
end
go

*where there's no pattern w/in ''.
How would you do this?

Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-26 : 03:15:26

[code]DECLARE @Translate TABLE
(
A VARCHAR(20),
B VARCHAR(2)
)

INSERT @Translate
SELECT 'FRUITS', 'F1' UNION ALL
SELECT 'VEGETABLE', 'V2' UNION ALL
SELECT 'CANDY', 'C3' UNION ALL
SELECT 'WOOD', 'W5'

UPDATE l
SET l.Num_1_Index = COALESCE(t.B, l.Num_1_Index),
l.Num_2_Index = COALESCE(t.B, l.Num_2_Index)
FROM Land AS l
LEFT JOIN @Translate AS t ON t.A IN (Num_1_Index, Num_2_Index, ... )[/code]

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

mathmath
Starting Member

13 Posts

Posted - 2009-06-26 : 03:39:55

quote:

Originally posted by Peso

DECLARE	@Translate TABLE
	(
		A VARCHAR(20),
		B VARCHAR(2)
	)

INSERT	@Translate
SELECT	'FRUITS', 'F1' UNION ALL
SELECT	'VEGETABLE', 'V2' UNION ALL
SELECT	'CANDY', 'C3' UNION ALL
SELECT	'WOOD', 'W5'


UPDATE		l
SET		l.Num_1_Index =	COALESCE(t.B, l.Num_1_Index),
		l.Num_2_Index =	COALESCE(t.B, l.Num_2_Index)
FROM		Land AS l
LEFT JOIN	@Translate AS t ON t.A IN (Num_1_Index, Num_2_Index, ... )

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

Thanks again Peso..
Here are some of the messages:

* incorrect syntax near 1 ( it doesnt recognize "update 1" i assume, but if i do "update land" then it worked)
* The multi-part identifier "l.Num_1_index" could not be bound.

I am too noob for this.
what should i do...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-26 : 03:50:32

It's not a 1 (one), it's a l (small letter L).

DECLARE	@Translate TABLE
	(
		A VARCHAR(20),
		B VARCHAR(2)
	)

INSERT	@Translate
SELECT	'FRUITS', 'F1' UNION ALL
SELECT	'VEGETABLE', 'V2' UNION ALL
SELECT	'CANDY', 'C3' UNION ALL
SELECT	'WOOD', 'W5'


UPDATE		x
SET		x.Num_1_Index =	COALESCE(t.B, x.Num_1_Index),
		x.Num_2_Index =	COALESCE(t.B, x.Num_2_Index)
FROM		Land AS x
LEFT JOIN	@Translate AS t ON t.A IN (x.Num_1_Index, x.Num_2_Index, ... )

EDIT: Alias name x in the COALESCE function too...

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

mathmath
Starting Member

13 Posts

Posted - 2009-06-26 : 04:21:09

quote:

Originally posted by Peso

It's not a 1 (one), it's a l (small letter L).

DECLARE	@Translate TABLE
	(
		A VARCHAR(20),
		B VARCHAR(2)
	)

INSERT	@Translate
SELECT	'FRUITS', 'F1' UNION ALL
SELECT	'VEGETABLE', 'V2' UNION ALL
SELECT	'CANDY', 'C3' UNION ALL
SELECT	'WOOD', 'W5'


UPDATE		x
SET		x.Num_1_Index =	COALESCE(t.B, l.Num_1_Index),
		x.Num_2_Index =	COALESCE(t.B, l.Num_2_Index)
FROM		Land AS x
LEFT JOIN	@Translate AS t ON t.A IN (x.Num_1_Index, x.Num_2_Index, ... )

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

I should've read carefully, very sorry about that.

Apparantly i cannot use Union All, it says "incorrect syntax near update"

so i removed Union All, but nothing shows up in the Num_1_index....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources