Update with JOIN but only on Top 1 ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2000)

Update with JOIN but only on Top 1 ?

Author

Topic

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2006-09-19 : 06:13:00

I'm trying to update a table via a join with the current query:

UPDATE zarageddebt_CURR INNER JOIN baddebt_CURR ON baddebt_CURR.DocNo = zarageddebt_CURR.DocNo SET zarageddebt_CURR.[Bad Debt] = baddebt_CURR.[Bad Debt];

Unfortunately, I have just discovered that the relationship is not always 1 to 1 (although it is in 99% of cases). In those where, it isn't and the destination table has, say, 3 records with the same DocNo, I would like to update only the top record.

Can this be done in the join? I don't think this will work:
UPDATE zarageddebt_CURR INNER JOIN baddebt_CURR ON TOP 1 baddebt_CURR.DocNo = zarageddebt_CURR.DocNo SET zarageddebt_CURR.[Bad Debt] = baddebt_CURR.[Bad Debt];

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-19 : 06:25:06

Top record, according to what? Time of insertion? Max [BAD DEBT]?

Try this

UPDATE		y
SET		y.[Bad Debt] = x.[Bad Debt]
FROM		zarageddebt_CURR y
INNER JOIN	(
			SELECT	DISTINCT DocNo,
				[Bad Debt]
			FROM	baddebt_CURR
		) x ON x.DocNo = y.DocNo

Peter Larsson
Helsingborg, Sweden

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2006-09-19 : 06:57:47

Hi
I think that will work. However, I need someway of identifying a unique record.
At the moment, there is no primary key or unique key in the 1st table. In most cases the DocNo is the unique reference although every so often it is repeated 2 or more times.
In the cases where it is repeated, I only want to update 1 of the records rather than all of them.

Any ideas? Could I add a primary key somehow and build that into it ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-19 : 07:05:04

I understand that. But which of the duplicated records do you want?
If there are two duplicated records for DocNo 12345, which [Bad Debt] do you want?

Peter Larsson
Helsingborg, Sweden

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2006-09-19 : 07:17:53

Sorry. There is only every 1 bad debt record.
But there may be 2 or 3 records in the 1st table with the same DocNo.
So, I want to update only the top record in the 1st table with the bad debt data. Unfortunately, I can't find out what the top record is in the 1st table at the moment without adding some sort of primary key...I think.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-19 : 07:28:07

Ok. How do you distinguish the 1st table zarageddebt_CURR between the duplicate records?

Peter Larsson
Helsingborg, Sweden

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2006-09-19 : 08:21:48

There is no way to distinguish them at present that could always be used.
Sometimes they can be dsitinguished by other references like dates, etc., sometime not.
If I added a primary key, I guess I could distinguish them by what record number they are? Would that be an idea?
If so, how would I build that into the join to only update the top record number of that particular docno?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-19 : 08:24:46

Do you have some sample data? Table layouts? Expected result/outcome of the provided sample data?
If you don't want to update all duplicates in first table, why not remove the duplicates?

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-19 : 09:03:53

quote:
Originally posted by qwertyjjj

There is no way to distinguish them at present that could always be used.
If so, how would I build that into the join to only update the top record number of that particular docno?

Can any record qualify as "top record"? How do you qualify a record as "TOP"?
What then happens when you run the update query again? Then there is the possibility that other rows are getting updated.

Peter Larsson
Helsingborg, Sweden

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2006-09-19 : 10:56:57

Well, ideally I'd leave it in 2 tables except Access is being odd with the joins putting brackets everywhere.
So, I was trying to get all the data normalised into 1 table by updating it with the data from baddebt_CURR

As an example:
The join structure here is a little different to what I'm used to in SQL Server...as mentioned Access adds brackets round everything.

When I run this, it's lightning fast:
SELECT IIf(Len([Company Code])=2,"00" & [Company Code],[Company Code]) AS Expr1, ((Sum([1To90])+Sum([Not Due])+Sum([91To120]))/1000) AS Months0To3, ((Sum([121To150])+Sum([151To180]))/1000) AS Months4To5, (Sum([181To365])/1000) AS Months6To12, (Sum([MoreThan366])/1000) AS [Months12+], ((Sum([1To90])+Sum([Not Due])+Sum([91To120]))+(Sum([121To150])+Sum([151To180]))+Sum([181To365])+Sum([MoreThan366]))/1000 AS TotalDebt
FROM StructureCompanyCodes
LEFT JOIN zarageddebt_CURR ON StructureCompanyCodes.[Company Code] = zarageddebt_CURR.CoCd)
GROUP BY IIf(Len([Company Code])=2,"00" & [Company Code],[Company Code])

When I run this, it takes at least 20 seconds to bring back data and the baddebt_CURR table isn't that large. From the brackets, it looks like it's returning a resultset from the first join and then joining tht again to the baddebt_CURR table:
SELECT IIf(Len([Company Code])=2,"00" & [Company Code],[Company Code]) AS Expr1, ((Sum([1To90])+Sum([Not Due])+Sum([91To120]))/1000) AS Months0To3, ((Sum([121To150])+Sum([151To180]))/1000) AS Months4To5, (Sum([181To365])/1000) AS Months6To12, (Sum([MoreThan366])/1000) AS [Months12+], ((Sum([1To90])+Sum([Not Due])+Sum([91To120]))+(Sum([121To150])+Sum([151To180]))+Sum([181To365])+Sum([MoreThan366]))/1000 AS TotalDebt, Sum(baddebt_CURR.[Bad Debt]) AS [SumOfBad Debt]
FROM (StructureCompanyCodes LEFT JOIN zarageddebt_CURR ON StructureCompanyCodes.[Company Code] = zarageddebt_CURR.CoCd)
LEFT JOIN baddebt_CURR ON StructureCompanyCodes.[Company Code] = baddebt_CURR.CoCd
GROUP BY IIf(Len([Company Code])=2,"00" & [Company Code],[Company Code])

All I want to do is join from the first table onto both tables and group the data. Any ideas if the join is wrong?

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2006-09-20 : 06:11:30

Say I add a primary key.
I can then define what i top by the earliest entry into the DB.

So:
1 000A1
2 000A1
3 000A2

In this case, I'd only want to update ID 1 despite there being 2 "000A1" entries.

Like I said, If the query above ran quickly then I'd leave it in 2 tables so not sure what is causing the delay on that query.
In the absence of being able to correct that, I have to normalise the data ?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-09-20 : 09:55:14

If this is an Access question, please post it in the Access forum. This is a SQL Server website.

- Jeff

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-20 : 10:11:23

Yes, if you add an IDENTITY (autonumber) column to the table, it will fairly easy to update the "first" entry, the entry with lowest identity (autonumber) value.

Peter Larsson
Helsingborg, Sweden

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2006-09-20 : 11:03:28

Something like this?

UPDATE y
SET y.[Bad Debt] = x.[Bad Debt]
FROM zarageddebt_CURR y
INNER JOIN (
SELECT DISTINCT DocNo, MAX(ID)
[Bad Debt]
FROM baddebt_CURR
GROUP BY DocNo
) x ON x.DocNo = y.DocNo

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources