update query neede - 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 2005 Forums

Transact-SQL (2005)

update query neede

Author

Topic

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-08-30 : 06:59:14

[code]
Table: ABC
A B C D E F G H
50586 152476 Dextromethorphan-Guaifenesin 10-100 mg/5 mL Tussin DM 10 mg-100 mg/5 mL Syrup R
89446 433219 Dextromethorphan-Guaifenesin 10-100 mg/5 mL Tussin DM 10 mg-100 mg/5 mL Syrup A
165511 433219 Dextromethorphan-Guaifenesin 10-100 mg/5 mL Tussin DM 10 mg-100 mg/5 mL Syrup A

i want to update Table ABC COLUMN H as 'I' If all columns matches except A

WHAT IS THE UPDATE QUERY IN DOING SO.

SOMEWHERE TRIED LIKE

UPDATE TABLE ABC SET H='I' WHERE A IN (SELECT MIN(A) FROM ABC GROUP BY C HAVING COUNT(*)>1)

I AM HAVING MORE TAHN 1.5 LAKH RECORDS IN MY TABLE.HOW TO UPDATE IF IT MATCHES ABOVE CRITERIA.....

[/code]

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-08-30 : 07:03:08

if 3 or 4 rows are matches over there i want only one row to be as 'A' and rest are all as 'I'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-30 : 07:03:43

What are you trying to do? COUNT(*) < 1 can never occur.

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

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-08-30 : 07:04:05

sorry i.e count(*)>1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-30 : 07:10:20

150 000 records is not much to update.

UPDATE		t1
SET		t1.H = 'I'
FROM		dbo.ABC AS t1
INNER JOIN	(
			SELECT DISTINCT	MIN(A) AS A
			FROM		dbo.ABC
			GROUP BY	C
			HAVING		COUNT(*) > 1
		) AS x ON x.A = t1.A

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

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-08-30 : 07:18:26

Thanks peso any idea for you in doing it in oracle..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-30 : 07:27:37

Yes.
Visit www.dbforums.com and let Oracle experts help you.
This is a Microsoft SQL Server forum.

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

rajasekhar857
Constraint Violating Yak Guru

396 Posts