Help with updating non unique rows

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)

Help with updating non unique rows

Author

Topic

fuzzyGURU
Starting Member

3 Posts

Posted - 2010-02-12 : 11:34:04

This might just be a complete lack of caffeine on my part but I am not able to get this issue solved.

I have a table that has mismatched data in it. What I need to do is update the [required] field in all rows of the same [group] and [fieldid] with the value of the [required] from the row that has the [language] = 1

My problem has been trying to get all the rows that do not match. I have tried subqueries, group bys, joins, etc. but have been unable to get it to give me an accurate list.

CREATE TABLE [Fields] (
[id] [int] IDENTITY(1,1) NOT NULL,
[group] [nvarchar](5) NOT NULL,
[fieldid] [int] NOT NULL,
[fieldtype] [int] NOT NULL,
[required] [bit] NOT NULL,
[label] [nvarchar](100) NOT NULL,
[language] [int] NOT NULL
)

INSERT INTO [Fields]
SELECT 'aaa01', 1, 1, 1, 'Customer', 1
UNION SELECT 'aaa01', 2, 3, 0, 'Amount', 1
UNION SELECT 'aaa01', 3, 4, 1, 'Rate', 1
UNION SELECT 'aaa01', 4, 1, 0, 'Vendor', 1
UNION SELECT 'aaa01', 5, 2, 0, 'Region', 1
UNION SELECT 'aaa01', 1, 1, 0, 'Client', 2
UNION SELECT 'aaa01', 2, 3, 0, 'Montant', 2
UNION SELECT 'aaa01', 3, 4, 1, 'Taux', 2
UNION SELECT 'aaa01', 4, 1, 0, 'Vendeur', 2
UNION SELECT 'aaa01', 5, 2, 0, 'Région', 2
UNION SELECT 'xyz01', 1, 1, 0, 'Cliente', 3
UNION SELECT 'xyz01', 2, 3, 0, 'Cantidad', 3
UNION SELECT 'xyz01', 3, 4, 1, 'Ritmo', 3
UNION SELECT 'xyz01', 4, 1, 0, 'Distribuidor', 3
UNION SELECT 'xyz01', 5, 2, 1, 'Región', 3
UNION SELECT 'bbb01', 1, 1, 1, 'Kunde', 4
UNION SELECT 'bbb01', 2, 3, 0, 'Betrag', 4
UNION SELECT 'bbb01', 3, 4, 1, 'Rate', 4
UNION SELECT 'bbb01', 4, 1, 0, 'Hersteller', 4
UNION SELECT 'bbb01', 5, 2, 0, 'Region', 4

Any suggestions would be helpful.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 11:48:42

you've language = 1 only for [group]='aaa01'. what about other ones? you dont have to consider them at all?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

fuzzyGURU
Starting Member

3 Posts

Posted - 2010-02-12 : 11:55:59

The data sample I provided is only a subset of what is really there. There are about 6 language entries for each fieldid, and there are varying amounts of fieldids per group. half the data is good, but due to a bug in the frontend code (and the update sql) , it corrupted the values for some language/fieldid entries. An analysis of the data revealed that language = 1 is the correct value to use.

Of course, after I correct this, I then have to add in the missing group/fieldid rows for language entries that are missing (using language = 1 as the source row)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 12:07:26

[code]
CREATE TABLE #Fields (
[id] [int] IDENTITY(1,1) NOT NULL,
[group] [nvarchar](5) NOT NULL,
[fieldid] [int] NOT NULL,
[fieldtype] [int] NOT NULL,
[required] [bit] NOT NULL,
[label] [nvarchar](100) NOT NULL,
[language] [int] NOT NULL
)

INSERT INTO #Fields ([group],[fieldid] ,[fieldtype] ,[required],[label] ,[language] )
SELECT 'aaa01', 1, 1, 1, 'Customer', 1
UNION SELECT 'aaa01', 2, 3, 0, 'Amount', 1
UNION SELECT 'aaa01', 3, 4, 1, 'Rate', 1
UNION SELECT 'aaa01', 4, 1, 0, 'Vendor', 1
UNION SELECT 'aaa01', 5, 2, 0, 'Region', 1
UNION SELECT 'aaa01', 1, 1, 0, 'Client', 2
UNION SELECT 'aaa01', 2, 3, 0, 'Montant', 2
UNION SELECT 'aaa01', 3, 4, 1, 'Taux', 2
UNION SELECT 'aaa01', 4, 1, 0, 'Vendeur', 2
UNION SELECT 'aaa01', 5, 2, 0, 'Région', 2
UNION SELECT 'xyz01', 1, 1, 0, 'Cliente', 3
UNION SELECT 'xyz01', 2, 3, 0, 'Cantidad', 3
UNION SELECT 'xyz01', 3, 4, 1, 'Ritmo', 3
UNION SELECT 'xyz01', 4, 1, 0, 'Distribuidor', 3
UNION SELECT 'xyz01', 5, 2, 1, 'Región', 3
UNION SELECT 'bbb01', 1, 1, 1, 'Kunde', 4
UNION SELECT 'bbb01', 2, 3, 0, 'Betrag', 4
UNION SELECT 'bbb01', 3, 4, 1, 'Rate', 4
UNION SELECT 'bbb01', 4, 1, 0, 'Hersteller', 4
UNION SELECT 'bbb01', 5, 2, 0, 'Region', 4
select 'before update'
select * from #Fields order by [group],[language]

update f
set f.[required] = t.[required]
from #Fields f
join #Fields t
on t.[group]=f.[group]
and t.fieldid=f.fieldid
where t.language =1
and f.language <> 1
and f.[required] <> t.[required]

select 'after update'
select * from #Fields order by [group],[language]

drop table #Fields

before update

id group fieldid fieldtype required label language
----------- ----- ----------- ----------- -------- ---------------------------------------------------------------------------------------------------- -----------
8 aaa01 4 1 0 Vendor 1
9 aaa01 5 2 0 Region 1
2 aaa01 1 1 1 Customer 1
3 aaa01 2 3 0 Amount 1
5 aaa01 3 4 1 Rate 1
6 aaa01 3 4 1 Taux 2
7 aaa01 4 1 0 Vendeur 2
4 aaa01 2 3 0 Montant 2
1 aaa01 1 1 0 Client 2
10 aaa01 5 2 0 Région 2
11 bbb01 1 1 1 Kunde 4
12 bbb01 2 3 0 Betrag 4
13 bbb01 3 4 1 Rate 4
14 bbb01 4 1 0 Hersteller 4
15 bbb01 5 2 0 Region 4
16 xyz01 1 1 0 Cliente 3
17 xyz01 2 3 0 Cantidad 3
18 xyz01 3 4 1 Ritmo 3
19 xyz01 4 1 0 Distribuidor 3
20 xyz01 5 2 1 Región 3

------------
after update

id group fieldid fieldtype required label language
----------- ----- ----------- ----------- -------- ---------------------------------------------------------------------------------------------------- -----------
8 aaa01 4 1 0 Vendor 1
9 aaa01 5 2 0 Region 1
2 aaa01 1 1 1 Customer 1
3 aaa01 2 3 0 Amount 1
5 aaa01 3 4 1 Rate 1
6 aaa01 3 4 1 Taux 2
7 aaa01 4 1 0 Vendeur 2
4 aaa01 2 3 0 Montant 2
1 aaa01 1 1 1 Client 2
10 aaa01 5 2 0 Région 2
11 bbb01 1 1 1 Kunde 4
12 bbb01 2 3 0 Betrag 4
13 bbb01 3 4 1 Rate 4
14 bbb01 4 1 0 Hersteller 4
15 bbb01 5 2 0 Region 4
16 xyz01 1 1 0 Cliente 3
17 xyz01 2 3 0 Cantidad 3
18 xyz01 3 4 1 Ritmo 3
19 xyz01 4 1 0 Distribuidor 3
20 xyz01 5 2 1 Región 3

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

fuzzyGURU
Starting Member

3 Posts

Posted - 2010-02-12 : 12:27:16

yup.. total lack of caffeine..

visakh, it looks like that worked. Thank you very much
now to add the missing rows... the joy of inherited code...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 12:29:06

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources