473,326 Members | 2,104 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Little Help With Update & Subquery?

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
4 3112
(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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Murali | last post by:
Hi All I was reading thro the posting(s) of Thomas Kyte and his nifty approach to doing updates without the need for unnecessary correlated subqueries. An alternative to correlated subquery...
4
by: Don | last post by:
Hi, I am moving from Sybase to Oracle and I used to be able to do update statement like this in Sybase: UPDATE TABLE1 SET T1.field1 = T2.field2 FROM TABLE1 T1, TABLE2 T2 WHERE T1.field2...
1
by: Clive Foley | last post by:
Hey all, i have a students_table which i want to do a multiple update on. Update student_table set grant = 35000 where course_id = 2 There are lots of students in the student table which...
1
by: mirth | last post by:
I would like to update a decimal column in a temporary table based on a set of Glcodes from another table. I search for a set of codes and then want to sum the value for each row matching the...
2
by: coryjflynn | last post by:
I am try to update the Gender field for all females of a database with about 15,000 records. So how I started was by searching baby girl names on the web and manipulated some of there lists to...
4
by: solidsna2 | last post by:
Hi, I am relatively new to SQL. I am using SQL 2000. I am trying to Update a field base in a criteria in a scond table. UPDATE Tbl1 SET Tbl1.Row2 = '1' WHERE Tbl1.Row1...
22
by: pbd22 | last post by:
hi. I am having probelms with an update statement. every time i run it, "every" row updates, not just the one(s) intended. so, here is what i have. i have tried this with both AND and OR and...
3
by: Manikandan | last post by:
Hi, I have table with three columns as below table name:exp No(int) name(char) refno(int) I have data as below No name refno 1 a 2 b 3 c
3
by: Michel Esber | last post by:
Hi all, DB2 V8 LUW FP 15 There is a table T (ID varchar (24), ABC timestamp). ID is PK. Our application needs to frequently update T with a new value for ABC. update T set ABC=? where ID...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.