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_TIMESTA MP () } >= 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_TIMESTA MP () } >= 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 1917
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_TIMEST AMP () } >= 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_TIMEST AMP () } >= 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_TIMESTA MP
AND Tbl2.Row1 = Tbl1.Row1)
--
Hugo Kornelis, SQL Server MVP
(so*******@gmai l.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_TIMESTA MP () } >= 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_TIMESTA MP >= Tbl2.Row3)
I also changed {fn current_timesta mp() } as there is no reason to
call an ODBC function to get the current date.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.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_TIMESTA MP >= 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 exist."
Althought there is not selection going on, instead it is entered, I
need it to match the flight in the db using the flightNo. I want all
the attributes to be changed apart from the flightNo itself as it is
it's associated details I need...
|
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 are input in login screen.
The password in the database is a number field.
I am writing the dynamic sql statement as follows below. I believe
I am going wrong in the password section of the code. I
appreciate any help. Thanks. Regards.
|
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,
PARENT_CATEGORY_ID INTEGER,
CATEGORY_ICON IMAGE,
DEPTH INTEGER,
|
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 from rsSource into rsDest
- if it finds a key violation then it deletes the current record from
rsDest and adds the new record from rsSource. This works perfectly -
but only for the first found duplicate record, it brings up the error
|
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, close the connection, then process
through one record at a time, so there is no nested transaction. It
normally take 1 hour or more, after about 45 min, the aspx page gives up
with a server unavailable msg, but the server still goes on in the...
| |
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 112: MM_editCmd.ActiveConnection = MM_editConnection
Line 113: MM_editCmd.CommandText = MM_editQuery
Line 114: MM_editCmd.Execute
|
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 catching.
Everything within my stored procedure is contained within a transaction. When utilizing the sql profiler, we can see that the transaction successfully begins and commits. The data in the sql table is updated at this point.
My data...
|
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 FOR UPDATE, it is fine and
no error.
I also tried Set objRs = objConn.Execute("SELECT * FROM EMP UPDATE OF
EMPNO"), but it still couldn't help.
any ideas? I tried to search in the web but couldn't find similar
|
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 remote Windows 2003
server. Both machines are on the same domain and very close physically
(<1ms ping).
I have set up the Oracle linked server in SQLEXPRESS, added the
login/pw information, and I can execute select and update queries
|
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 get a 07004
error that says I must in clude a USING or INTO clause in my dynamic
query.
I inserted the USING="SQL" clause (as the documentation stated
somewhere) into the XML query but that didn't do anything.
Can anyone help me with this?...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |