Eazy one - 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

SQL Server Development (2000)

Eazy one

Author

Topic

Saeed
Starting Member

39 Posts

Posted - 2003-06-19 : 01:31:07

I have a Table defined as :
r1 , r2 , r3 , ,l1,l2,l3

there are some records that have duplicates r1,r2,r3,r4 but unique l1,l2,l3,l4
I d like to set the value for r2 for these kinda records to ZERO except for one record that keeps its original value.
Is there anyway to do this in a few steps?

ex:
12, 139494 ,388383 , A,B,C
12, 139494 ,388383 , A,X,E
12, 139494 ,388383 , B,Q,E
13, 22222 , 4848 , A,B,C

should become
12, 139494 ,388383 , A,B,C
0, 0 ,0, A,X,E
0, 0 ,0, B,Q,E
13, 22222 , 4848 , A,B,C

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-06-19 : 01:48:47


CREATE TABLE TerribleTable(r1 int, r2 int, r3 int, l1 char(1), l2 char(1), l3 char(1))
GO
INSERT TerribleTable
SELECT 12, 139494 ,388383 , 'A','B','C' 
UNION ALL
SELECT 12, 139494 ,388383 , 'A','X','E' 
UNION ALL
SELECT 12, 139494 ,388383 , 'B','Q','E' 
UNION ALL
SELECT 13, 22222 , 4848 , 'A','B','C' 
UNION ALL
SELECT 13, 22222 , 4848 , 'D','B','C' 
UNION ALL
SELECT 13, 22222 , 4848 , 'C','B','C' 
UNION ALL
SELECT 13, 22222 , 4848 , 'e','B','C' 
go
SELECT * FROM TerribleTable
GO
UPDATE d SET r1 = 0, r2 = 0, r3=0
FROM TerribleTable  D
INNER JOIN TerribleTable X ON x.r1 =d.r1 AND x.r2 =d.r2 AND x.r3 =d.r3
WHERE d.l1+d.l2+d.l3 > x.l1+x.l2+x.l3
GO
SELECT * FROM TerribleTable

Change the greater-than sign to a Less-than pick which one stays

DavidM

"SQL-3 is an abomination.."

Saeed
Starting Member

39 Posts

Posted - 2003-06-19 : 20:00:21

Good solution thanks
except if the varchar is long it craps out:
the conversion of the varchar value '21208055369' overflowed an int column. Maximum integer value exceeded.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-06-19 : 20:04:05

I don't see any varchar's anywhere .... where do you have them and why are you trying to convert them to an int?

- Jeff

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-06-19 : 20:14:20

If your values are going to be outside of the range that an INT supports, then you will need to use BIGINT if your system is SQL2k. If it is 7.0 or earlier, then you'll need to use FLOAT instead as BIGINT is new to 2000.

Tara

nr
SQLTeam MVY

12543 Posts

Posted - 2003-06-19 : 20:20:03

Suspect you have the problem with WHERE d.l1+d.l2+d.l3 > x.l1+x.l2+x.l3
These should be A,B,C from your example but maybe one is really an int. If so convert all to a char (not varchar).

or use somethng like

update TerribleTable
set r1 = 0, r2 = 0, r3=0
from TerribleTable t1
where not exists
(select * from (select top 1 t2.* from TerribleTable t2 where t1.r1 = t2.r1 and t1.r2 = t2.r2 and t1.r3 = t2.r3) t3
where t1.l1 = t3.l1 and t1.l2 = t3.l2 and t1.l3 = t3.l3
)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources