By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,226 Members | 1,435 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,226 IT Pros & Developers. It's quick & easy.

Little Help With Update & Subquery?

P: n/a
Can anyone help me get this update query right?

The SELECT subquery does select the rows I want to update when taken on
its own.

However when I add the UPDATE piece it finds no rows to update. What am
I missing here...

===== begin SQL
UPDATE Claims SET Payment = 'Not Required'
WHERE Payment IN
(
SELECT Payment From Claims Left Join People
ON People.ID = Claims.PeopleID
WHERE People.Foo = People.Bar
AND Canceled = False
AND IsNull (Payment)
);
===== end SQL

Thanks for all help!
--
Smartin
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
(SELECT Payment From Claims
Left Join People ON People.ID = Claims.PeopleID
WHERE
(People.Foo = People.Bar)
AND
(Canceled = False)
AND
(Payment Is Null))

Nov 13 '05 #2

P: n/a
On Tue, 08 Nov 2005 18:41:41 -0500, Smartin <sm********@yahoo.com>
wrote:

Probably because you're using the Claims table twice. Try this:
UPDATE Claims C SET C.Payment = 'Not Required'
WHERE C.Payment IN
(
SELECT Payment From Claims Left Join People
ON People.ID = Claims.PeopleID
WHERE People.Foo = People.Bar
AND Canceled = False
AND IsNull (Payment)
);

Of course we're assuming Payment is a text field wide enough to accept
the text 'Not Required'.

-Tom.

Can anyone help me get this update query right?

The SELECT subquery does select the rows I want to update when taken on
its own.

However when I add the UPDATE piece it finds no rows to update. What am
I missing here...

===== begin SQL
UPDATE Claims SET Payment = 'Not Required'
WHERE Payment IN
(
SELECT Payment From Claims Left Join People
ON People.ID = Claims.PeopleID
WHERE People.Foo = People.Bar
AND Canceled = False
AND IsNull (Payment)
);
===== end SQL

Thanks for all help!


Nov 13 '05 #3

P: n/a
Lauren Quantrell wrote:
(SELECT Payment From Claims
Left Join People ON People.ID = Claims.PeopleID
WHERE
(People.Foo = People.Bar)
AND
(Canceled = False)
AND
(Payment Is Null))


Thanks for the suggestiom, but same result ):

--
Smartin
Nov 13 '05 #4

P: n/a
Tom van Stiphout wrote:
On Tue, 08 Nov 2005 18:41:41 -0500, Smartin <sm********@yahoo.com>
wrote:

Probably because you're using the Claims table twice. Try this:
UPDATE Claims C SET C.Payment = 'Not Required'
WHERE C.Payment IN
(
SELECT Payment From Claims Left Join People
ON People.ID = Claims.PeopleID
WHERE People.Foo = People.Bar
AND Canceled = False
AND IsNull (Payment)
);

Of course we're assuming Payment is a text field wide enough to accept
the text 'Not Required'.

-Tom.


Thanks for that too, but same result using alias ):
--
Smartin
Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.