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 =
(SELECT Tbl1.Row1
FROM Tbl2, Tbl1
WHERE Tbl2.Row1 = Tbl1.Row1 AND ({ fn
CURRENT_TIMESTAMP () } >= Tbl2.Row3))
Row 1 is the key between the two table. If I am doing only the select
below, I am getting the right value.
SELECT Tbl1.Row1
FROM Tbl2, Tbl1
WHERE Tbl2.Row1 = Tbl1.Row1 AND ({ fn
CURRENT_TIMESTAMP () } >= Tbl2.Row3)
When I am running the entire querry, I am getting this error:
Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <= , >, >= or when the subquery is used as
an expression.
The statement has been terminated.
What I am trying to do is to update a field in Tbl1 base on a date in
Tbl2. If the date is expire, I want to raise a flag, in Tbl1.
Thank you
Philippe 4 1902
On 27 Feb 2006 12:22:26 -0800, so*******@gmail.com wrote: 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 = (SELECT Tbl1.Row1 FROM Tbl2, Tbl1 WHERE Tbl2.Row1 = Tbl1.Row1 AND ({ fn CURRENT_TIMESTAMP () } >= Tbl2.Row3))
Row 1 is the key between the two table. If I am doing only the select below, I am getting the right value.
SELECT Tbl1.Row1 FROM Tbl2, Tbl1 WHERE Tbl2.Row1 = Tbl1.Row1 AND ({ fn CURRENT_TIMESTAMP () } >= Tbl2.Row3)
When I am running the entire querry, I am getting this error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.
What I am trying to do is to update a field in Tbl1 base on a date in Tbl2. If the date is expire, I want to raise a flag, in Tbl1.
Thank you
Philippe
Hi Philippe,
Hard to be sure without CREATE TABLE statements, INSERT statements and
expected output (see www.aspfaq.com/5006), but I guess that you need
something like this:
UPDATE Tbl1
SET Row2 = '1'
WHERE EXISTS
(SELECT *
FROM Tbl2
WHERE Tbl2.Row3 >= CURRENT_TIMESTAMP
AND Tbl2.Row1 = Tbl1.Row1)
--
Hugo Kornelis, SQL Server MVP
(so*******@gmail.com) writes: 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 = (SELECT Tbl1.Row1 FROM Tbl2, Tbl1 WHERE Tbl2.Row1 = Tbl1.Row1 AND ({ fn CURRENT_TIMESTAMP () } >= Tbl2.Row3))
This does not look right. You have Tbl1 once extra in the subquery,
making it entirely uncorrelated with the outer Tbl1. Try chaning the
query to:
UPDATE Tbl1
SET Row2 = '1'
WHERE Tbl1.Row1 = (SELECT Tbl2.Row1
FROM Tbl2
WHERE Tbl2.Row1 = Tbl1.Row1
AND CURRENT_TIMESTAMP >= Tbl2.Row3)
I also changed {fn current_timestamp() } as there is no reason to
call an ODBC function to get the current date.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
Thank you for the reply. Sadly, I am getting the same error with your
solution. If I run only the SELECT subquery alone, I am getting
mutiple result, and the result are what I am expecting. In Tbl2, Row1
can be the same for up to 8 lines. Row1 is the key between the 2
tables. In Tbl1, Row1 is unique.
Ex:
ROW1 ROW2 ROW3
1 xxx zzz
1 aaa bbb
1 ccc ddd
1 eee fff
1 ggg hhh
Maybe this is why it is giving me the error that I have mutiple value.
Like I sayd, I am a new to doing SQL programming. Maybe I am missing
something or I do not approach the problem properly.
Thank you again
Well I figure out the problem. I had a GROUP BY at the end of the
SELECT subquery.
UPDATE Tbl1
SET Row2 = '1'
WHERE Tbl1.Row1 = (SELECT Tbl2.Row1
FROM Tbl2
WHERE Tbl2.Row1 = Tbl1.Row1
AND CURRENT_TIMESTAMP >= Tbl2.Row3 GROUP BY
Tbl2.Row1)
Thank you again for your help. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Mohammed Mazid |
last post by:
Can anyone please help me here? Basically I have modified the source
code and understood it but when I update a record in the db using a
JSP, it gives me an error "The flight you selected does...
|
by: Jack |
last post by:
Hi,
I am trying to test a sql statement in Access which gives me
the error as stated in the heading.
The sql statement is built as a part of asp login verification,
where the userid and password...
|
by: coosa |
last post by:
I have a table:
----------------------------------------------------
CREATE TABLE CATEGORY (
CATEGORY_ID INTEGER IDENTITY(1,1) NOT NULL,
CATEGORY_NAME VARCHAR(40) NOT NULL,...
|
by: Nathan Bloomfield |
last post by:
Hi there,
I am having difficulty with a piece of code which would work wonders
for my application if only the error trapping worked properly.
Basically, it works as follows:
- adds records...
|
by: Zeng |
last post by:
Hello,
I'm wondering if anybody has seen this problem. I basically need to cycle
through ~30000 db rows to update the data, I load up the id of the rows I
need first, put them into ArrayList,...
|
by: amitbadgi |
last post by:
HI i am getting the foll error while conv an asp application to
asp.net
Exception Details: System.Runtime.InteropServices.COMException: Syntax
error in UPDATE statement.
Source Error:
Line...
|
by: Jeremy Ames |
last post by:
I have a datagrid that updates the table using a stored procedure. The stored procedure is confirmed to complete correctly, yet the sql data adapter is returning an error that my application is...
|
by: Steve |
last post by:
ASP error number 13 - Type mismatch with SELECT...FOR UPDATE statement
I got ASP error number 13 when I use the SELECT...FOR UPDATE statement
as below.
However, if I use SELECT statement without...
|
by: gshawn3 |
last post by:
Hi,
I am having a hard time creating a Trigger to update an Oracle
database. I am using a SQL Server 2005 Express database on a Win XP Pro
SP2 desktop, linked to an Oracle 10g database on a...
|
by: vasilip |
last post by:
I am trying to use the XMLUPDATE stored procedure. Installed fine, and
seems to work great if I execute the statement from a sql query using
the controll center.. Doesn't work from java however. I...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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
|
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: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |