Update with Where Clause using Select

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 with Where Clause using Select

Author

Topic

bbowser
Starting Member

43 Posts

Posted - 2010-02-10 : 11:39:10

I'm trying to update my tblAppliance.FundingID field where the tblVoucher.VoucherNum > 9000000 (9million). I seem to be close with some of my update statements but none of them seem to be the right solution so far. I want to update the FundingID field to 2 if the VoucherNum is greater than 9 million. Here is some of the code I've tried so far. What am I doing wrong?

UPDATE [TestRAR].[dbo].[tblAppliances]
SET
[FundingID] = '2'
FROM (SELECT tblAppliances.FundingID, tblVoucher.VoucherNum, tblVoucher.ApplicantID, tblVoucher.ItemDescription
From tblAppliances INNER JOIN
tblVoucher ON tblAppliances.ApplicantID = tblVoucher.ApplicantID
WHERE (tblVoucher.VoucherNum > 9000000)
GROUP BY tblAppliances.FundingID, tblVoucher.VoucherNum, tblVoucher.ApplicantID, tblVoucher.ItemDescription)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-02-10 : 11:45:47

Why you need group by there?


UPDATE ta
SET 
[FundingID] = '2'
from tblAppliances ta INNER JOIN
tblVoucher tv ON ta.ApplicantID = tv.ApplicantID
WHERE tv.VoucherNum > 9000000

Harsh Athalye
http://www.letsgeek.net/

bbowser
Starting Member

43 Posts

Posted - 2010-02-10 : 12:00:22

quote:
Originally posted by harsh_athalye

Why you need group by there?

UPDATE ta
SET
[FundingID] = '2'
from tblAppliances ta INNER JOIN
tblVoucher tv ON ta.ApplicantID = tv.ApplicantID
WHERE tv.VoucherNum > 9000000

Harsh Athalye
http://www.letsgeek.net/

That's the way I got the correct results to show up in a simple select statement. If I don't use the Group by then I get more records than I'm asking for. For instance I have multiple records that have the same applicantID and some of those records may have the VoucherNum that is less than 9 million. So.. this is the only way I could get the Select statement to retrieve the correct records.

bbowser
Starting Member

43 Posts

Posted - 2010-02-10 : 14:39:16

The code below is updating every record that has a match in the tblVoucher table as well as the tblAppliances table so I'm updating 7 records when only 4 have VoucherNum over 9 million. Am I close?

UPDATE [Test].[dbo].[tblAppliances]
SET [FundingID] = '2'
From tblAppliances INNER JOIN
tblVoucher ON tblAppliances.ApplicantID = tblVoucher.ApplicantID
WHERE tblVoucher.VoucherNum >= 9000000

bbowser
Starting Member

43 Posts

Posted - 2010-02-11 : 11:23:57

The following statement doesn't work at all. It simply updates all of the records in the tblAppliances table but the nested Select statement will retrieve the 4 correct records that needs to be updated. Could someone please point me in the right direction?

UPDATE [TestRAR].[dbo].[tblAppliances]
SET
[FundingID] = '1'

From (SELECT Distinct tblVoucher.ApplicantID, tblVoucher.VoucherNum, tblAppliances.FundingID
FROM tblAppliances INNER JOIN
tblVoucher ON tblAppliances.ApplicantID = tblVoucher.ApplicantID
WHERE (tblVoucher.VoucherNum > 9000000))

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-02-11 : 11:44:01

You could try this:

UPDATE 
	A
SET 
	FundingID = '2'
From 
	tblAppliances AS A
INNER JOIN
	tblVoucher 
	ON A.ApplicantID = tblVoucher.ApplicantID
WHERE 
	tblVoucher.VoucherNum > 9000000

But, it would help if you can post DDL, DML and expected results. Check this link for assistance:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

bbowser
Starting Member

43 Posts

Posted - 2010-02-11 : 11:49:38

quote:
Originally posted by Lamprey

You could try this:
UPDATE
A
SET
FundingID = '2'
From
tblAppliances AS A
INNER JOIN
tblVoucher
ON A.ApplicantID = tblVoucher.ApplicantID
WHERE
tblVoucher.VoucherNum > 9000000
But, it would help if you can post DDL, DML and expected results. Check this link for assistance:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Thanks Lamprey for that but it updates 7 records and it should only be updating 4 records. Again this Update statement is updating all the records that have the same ApplicantID as well as a VoucherNum but it's selecting only the records which have a VoucherNum > 9000000. Any other suggestions? I've tried many and none of them seem to be working.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-11 : 11:57:21

Did you notice link Lamprey posted?
If you could post your data in required format as per link somebody will be able to understand your scenario better and propose correct solution. Otherwise we could only provide solutions based on our guess as we cant see your system neither do we know how your data is.

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

bbowser
Starting Member

43 Posts

Posted - 2010-02-12 : 09:38:50

I had overlooked his post so, here is the requested information.

DDL:

Create Table[dbo].[tblAppliances](ApplianceID int Identity (1,1) NOT NULL, ApplicantID int NOT NULL, Brand nvarchar(50) NULL, SerialNumber nvarchar(50) NULL, FundingID int NULL);

Create Table [dbo].[tblVoucher] (VoucherID int Identity(1,1) NOT NULL, ApplicantID int NOT NULL, VoucherDate datetime NULL, ItemDescription nvarchar(50) NULL, VoucherNum nvarchar(50) NOT NULL);

DML:

Insert Into tblAppliances(ApplicantID, Brand, SerialNumber, FundingID)
Values ('2278', 'Sears', '12345', '0')
Insert Into tblAppliances(ApplicantID, Brand, SerialNumber, FundingID)
Values ('2278', 'Sears', '12345665', '0')
Insert Into tblAppliances(ApplicantID, Brand, SerialNumber, FundingID)
Values ('2278', 'Sears', '1234566', '0')
Insert Into tblAppliances(ApplicantID, Brand, SerialNumber, FundingID)
Values ('2279', 'Sears', '12344456', '0')
Insert Into tblAppliances(ApplicantID, Brand, SerialNumber, FundingID)
Values ('2279', 'Sears', '1233345', '0')
Insert Into tblAppliances(ApplicantID, Brand, SerialNumber, FundingID)
Values ('2279', 'Sears', '1234455', '0')
Insert Into tblAppliances(ApplicantID, Brand, SerialNumber, FundingID)
Values ('2280', 'Sears', '1266', '0')
Insert Into tblAppliances(ApplicantID, Brand, SerialNumber, FundingID)
Values ('2280', 'Sears', '12345776', '0')
Insert Into tblAppliances(ApplicantID, Brand, SerialNumber, FundingID)
Values ('2280', 'Sears', '12747474', '0');

Insert Into tblVoucher(ApplicantID, ItemDescription, VoucherNum)
Values ('2278','Freezer', '858564')
Insert Into tblVoucher(ApplicantID, ItemDescription, VoucherNum)
Values ('2278','Refrigerator', '546452')
Insert Into tblVoucher(ApplicantID, ItemDescription, VoucherNum)
Values ('2278','Freezer', '888585')
Insert Into tblVoucher(ApplicantID, ItemDescription, VoucherNum)
Values ('2279','Refrigerator', '887554')
Insert Into tblVoucher(ApplicantID, ItemDescription, VoucherNum)
Values ('2279','Stand Up Washer', '9788435')
Insert Into tblVoucher(ApplicantID,ItemDescription, VoucherNum)
Values ('2279','Freezer', '235644')
Insert Into tblVoucher(ApplicantID, ItemDescription, VoucherNum)
Values ('2280','Stand Up Washer', '9889561')
Insert Into tblVoucher(ApplicantID, ItemDescription, VoucherNum)
Values ('2280','Freezer', '152550')
Insert Into tblVoucher(ApplicantID, ItemDescription, VoucherNum)
Values ('2280','Refrigerator', '9011585')

So.. a record in the tblAppliances table may have a payment Voucher created for it and in this case I need to change the FundingID from 0 to 2 if the corresponding VoucherNum from the tblVoucher table is over 9 million. So I need to update the tblAppliances.FundingID on three records in this case. I hope this helps. Thanks for all the responses so far.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 09:44:59

so you want all records of that Applicant to be updated in tblAppliances if at least he has a single vouchernum that crosses 9 million in tblVoucher?

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

bbowser
Starting Member

43 Posts

Posted - 2010-02-12 : 10:31:41

That is correct Visakh16

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 10:39:52

cool. here's your solution


UPDATE a
SET a.FundingID=2
FROM tblAppliances a
CROSS APPLY (SELECT COUNT(*) AS Cnt
             FROM tblVoucher
             WHERE ApplicantID=a.ApplicantID
             AND VoucherNum*1 > 9000000)v

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

bbowser
Starting Member

43 Posts

Posted - 2010-02-12 : 11:28:38

Visakh16 that solution does not work. When I tried your solution all records in the tblAppliances table were updated to 2.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 11:36:22

quote:
Originally posted by bbowser

Visakh16 that solution does not work. When I tried your solution all records in the tblAppliances table were updated to 2.

Oops i left WHERE clause by mistake

it should be


UPDATE a
SET a.FundingID=2
FROM tblAppliances a
CROSS APPLY (SELECT COUNT(*) AS Cnt
             FROM tblVoucher
             WHERE ApplicantID=a.ApplicantID
             AND VoucherNum*1 > 9000000)v
WHERE v.Cnt>0

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-02-12 : 11:50:09

quote:
Originally posted by bbowser
[br
Thanks Lamprey for that but it updates 7 records and it should only be updating 4 records. Again this Update statement is updating all the records that have the same ApplicantID as well as a VoucherNum but it's selecting only the records which have a VoucherNum > 9000000. Any other suggestions? I've tried many and none of them seem to be working.

Actually, it is updating the corret recrods. Due, to the join some of those records will get updated more than once.

That is why some of the relational/ansi purists say that UPDATE..FROM should be removed from SQL Server now that we have the MERGE statement.

bbowser
Starting Member

43 Posts

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

Well that solution updated 6 records when actually there are only 3 in this case that have a vouchernum > 9000000 and it seemed to have updated the last 6 records in the database. The solution should only be updating 2 records in the tblAppliances table for ApplicantID = 2280 and 1 record in the tblApliances table for ApplicantID = 2279.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 11:57:01

[code]
Create Table #tblAppliances(ApplianceID int Identity (1,1) NOT NULL, ApplicantID int NOT NULL, Brand nvarchar(50) NULL, SerialNumber nvarchar(50) NULL, FundingID int NULL);

Create Table #tblVoucher (VoucherID int Identity(1,1) NOT NULL, ApplicantID int NOT NULL, VoucherDate datetime NULL, ItemDescription nvarchar(50) NULL, VoucherNum nvarchar(50) NOT NULL);

Insert Into #tblAppliances(ApplicantID, Brand, SerialNumber, FundingID)
Values ('2278', 'Sears', '12345', '0')
Insert Into #tblAppliances(ApplicantID, Brand, SerialNumber, FundingID)
Values ('2278', 'Sears', '12345665', '0')
Insert Into #tblAppliances(ApplicantID, Brand, SerialNumber, FundingID)
Values ('2278', 'Sears', '1234566', '0')
Insert Into #tblAppliances(ApplicantID, Brand, SerialNumber, FundingID)
Values ('2279', 'Sears', '12344456', '0')
Insert Into #tblAppliances(ApplicantID, Brand, SerialNumber, FundingID)
Values ('2279', 'Sears', '1233345', '0')
Insert Into #tblAppliances(ApplicantID, Brand, SerialNumber, FundingID)
Values ('2279', 'Sears', '1234455', '0')
Insert Into #tblAppliances(ApplicantID, Brand, SerialNumber, FundingID)
Values ('2280', 'Sears', '1266', '0')
Insert Into #tblAppliances(ApplicantID, Brand, SerialNumber, FundingID)
Values ('2280', 'Sears', '12345776', '0')
Insert Into #tblAppliances(ApplicantID, Brand, SerialNumber, FundingID)
Values ('2280', 'Sears', '12747474', '0');

Insert Into #tblVoucher(ApplicantID, ItemDescription, VoucherNum)
Values ('2278','Freezer', '858564')
Insert Into #tblVoucher(ApplicantID, ItemDescription, VoucherNum)
Values ('2278','Refrigerator', '546452')
Insert Into #tblVoucher(ApplicantID, ItemDescription, VoucherNum)
Values ('2278','Freezer', '888585')
Insert Into #tblVoucher(ApplicantID, ItemDescription, VoucherNum)
Values ('2279','Refrigerator', '887554')
Insert Into #tblVoucher(ApplicantID, ItemDescription, VoucherNum)
Values ('2279','Stand Up Washer', '9788435')
Insert Into #tblVoucher(ApplicantID,ItemDescription, VoucherNum)
Values ('2279','Freezer', '235644')
Insert Into #tblVoucher(ApplicantID, ItemDescription, VoucherNum)
Values ('2280','Stand Up Washer', '9889561')
Insert Into #tblVoucher(ApplicantID, ItemDescription, VoucherNum)
Values ('2280','Freezer', '152550')
Insert Into #tblVoucher(ApplicantID, ItemDescription, VoucherNum)
Values ('2280','Refrigerator', '9011585')

select * from #tblAppliances

select * from #tblVoucher

UPDATE a
SET a.FundingID=2
FROM #tblAppliances a
CROSS APPLY (SELECT COUNT(*) AS Cnt
FROM #tblVoucher
WHERE ApplicantID=a.ApplicantID
AND VoucherNum*1 > 9000000)v
WHERE v.Cnt >0

select * from #tblAppliances

drop table #tblAppliances
drop table #tblVoucher

output
---------------------------------------
before updation
tblAppliances
ApplianceID ApplicantID Brand SerialNumber FundingID
1 2278 Sears 12345 0
2 2278 Sears 12345665 0
3 2278 Sears 1234566 0
4 2279 Sears 12344456 0
5 2279 Sears 1233345 0
6 2279 Sears 1234455 0
7 2280 Sears 1266 0
8 2280 Sears 12345776 0
9 2280 Sears 12747474 0
tblVoucher
VoucherID ApplicantID VoucherDate ItemDescription VoucherNum
1 2278 NULL Freezer 858564
2 2278 NULL Refrigerator 546452
3 2278 NULL Freezer 888585
4 2279 NULL Refrigerator 887554
5 2279 NULL Stand Up Washer 9788435
6 2279 NULL Freezer 235644
7 2280 NULL Stand Up Washer 9889561
8 2280 NULL Freezer 152550
9 2280 NULL Refrigerator 9011585

after updation
tblAppliances
ApplianceID ApplicantID Brand SerialNumber FundingID
1 2278 Sears 12345 0
2 2278 Sears 12345665 0
3 2278 Sears 1234566 0
4 2279 Sears 12344456 2
5 2279 Sears 1233345 2
6 2279 Sears 1234455 2
7 2280 Sears 1266 2
8 2280 Sears 12345776 2
9 2280 Sears 12747474 2

[/code]

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-02-12 : 12:17:23

quote:
Originally posted by bbowser

Well that solution updated 6 records when actually there are only 3 in this case that have a vouchernum > 9000000 and it seemed to have updated the last 6 records in the database. The solution should only be updating 2 records in the tblAppliances table for ApplicantID = 2280 and 1 record in the tblApliances table for ApplicantID = 2279.

Ok, so what is the other requirement that restricts the update to only those two rows?

bbowser
Starting Member

43 Posts

Posted - 2010-02-12 : 12:38:06

Exactly Visakh16 if you look at the output you have the last 6 records updated when the output I'm looking for would have ApplianceID #5, #7 and #9 updated only.
Expect Results:
ApplianceID ApplicantID Brand SerialNumber FundingID
1 2278 Sears 12345 0
2 2278 Sears 12345665 0
3 2278 Sears 1234566 0
4 2279 Sears 12344456 0
5 2279 Sears 1233345 2
6 2279 Sears 1234455 0
7 2280 Sears 1266 2
8 2280 Sears 12345776 0
9 2280 Sears 12747474 2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 12:41:45

quote:
Originally posted by bbowser

Exactly Visakh16 if you look at the output you have the last 6 records updated when the output I'm looking for would have ApplianceID #5, #7 and #9 updated only.
Expect Results:
ApplianceID ApplicantID Brand SerialNumber FundingID
1 2278 Sears 12345 0
2 2278 Sears 12345665 0
3 2278 Sears 1234566 0
4 2279 Sears 12344456 0
5 2279 Sears 1233345 2
6 2279 Sears 1234455 0
7 2280 Sears 1266 2
8 2280 Sears 12345776 0
9 2280 Sears 12747474 2

this is not what you stated earlier

see your reply to what i asked on 02/12/2010 : 09:44:59

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

bbowser
Starting Member

43 Posts

Posted - 2010-02-15 : 10:01:28

Then I misunderstood your question Visakh16.
I worked through my problem although I never really found a solution.
Thanks for the help though.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources