|
I want to update a column of one table A with column of another table B.
for one entry of A, B contains 2 or more entry. Say A is super table of B.
I wrote a query like this
UPDATE SendSet SET oDate = (Select oOrder.oDate from oOrder where oOrder.oID=SendSet.oID)
WHERE Exists (Select oOrder.oDate from oOrder where oOrder.oID=SendSet.oID);
DB is MS-Access.
But it gives error :
Operation must use an updatable query !!
You attempted to run a query that tried to update a field that cannot be updated. For example, you may have created the query in such a way that you tried to update a field on the one side of a one-to-many relationship.
any help ?
|
|
|
|
|
You can use a join in your update query. Please check out how to do a join.
|
|
|
|
|
Without knowing the relationship between table A and table B, I can only suggest that you use something like this:
update SendSet inner join oOrder on SendSet.oID=oOrder.oID set SendSet.oDate=oOrder.oDate;
You need to work out what exactly you need to do this update.
Happy programming!
|
|
|
|
|
Thanks.
it works.
regards 
|
|
|
|
|
I have one table "Farmer" in which i store farmer name,village id,gut id
and when display this information in grid,i want display farmer name,village name (this name is retrieve from village table in which[village ID,Village name],but some farmer have no village id ,but i want show information in grid his name and after vilage name is blank,which query i use?
|
|
|
|
|
What have you tried so far?
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
|
|
|
|
|
i think for this try left outer join so that it get all the farmer even the village is not present for that
|
|
|
|
|
I have downloaded the beta 2 of MS visual Studio 2010.
I'm not very familiar with database projects. I've walked thru the MSVS2008 example for deploying a database to a server. This method entails building directly to a server you have access too.
I'd like to know if there is a user friendly way to script out and install a database using MSVS2010 for a server you know nothing about at the time; ie you are developing a solution that needs to be installed at a client's network?
|
|
|
|
|
I am not sure if this changed with 2010, but this article[^] could be useful to you
|
|
|
|
|
I have 3 tables and need to update the TableC from TableA and TableB
TableA
OldId NewId
TableB
OldId OldName OldAddress
TableC
NewId NewName NewAddress
Please help me with the joins.
Thanks
------------------------------------------------------------
"The only true wisdom is in knowing you know nothing." --Socrates
modified on Tuesday, January 19, 2010 5:30 PM
|
|
|
|
|
Hi
to get the values for TableC you can use this query:
SELECT TableA.NewId, TableB.OldName, TableB.OldAddress FROM TableA INNER JOIN TableB ON TableA.OldId = TableB.OldId;
If you want to insert the data from this query into tableC it must be something like this (not really sure):
INSERT INTO TableC VALUES (SELECT TableA.NewId, TableB.OldName, TableB.OldAddress FROM TableA INNER JOIN TableB ON TableA.OldId = TableB.OldId);
Hope this helps...
Regards
Sebastian
|
|
|
|
|
Hi,
I have a logger written in T-SQL (Stored Procs) writing to a log table.
When there are exceptions a log row is written, but then when the outer transaction rolls back it also rolls back the log entry.
I would like to have the log entry (i.e. row) to be inserted and persisted regardless of what the outer (nested) transactions are doing. The log entry should never roll back
Could someone point me to a solution using T-SQL?
Thanks
Yuval
"The true sign of intelligence is not knowledge but imagination." - Albert Einstein
|
|
|
|
|
Yuval Naveh wrote: I would like to have the log entry (i.e. row) to be inserted and persisted regardless of what the outer (nested) transactions are doing. The log entry should never roll back
Could someone point me to a solution using T-SQL?
You could use a raiserror[^] to write your message to the sys.messages view. You'd have to predefine the errormessages using sp_addmessage though.
Alternatively, if it's from a stored procedure then you might choose to add them to a variable, as opposed to logging them immediately. Next, RETURN that variable when exiting the sproc, and write insert it's contents to the logfile loggingtable.
If you have the option to drop the TSQL-requirement, then go for extended procedures, like xp_cmdshell (executing isql.exe to execute an SQL statement).
I hope someone else comes up with something better
I are Troll 
modified on Tuesday, January 19, 2010 7:31 PM
|
|
|
|
|
Hi Eddy,
Using the xp_cmdshell to come back and insert rows is a possibility - looks a bit cumbersome though.
If I can't figure it out a more elegant way this might be a (temporary) solution.
Thanks,
Yuval
"The true sign of intelligence is not knowledge but imagination." - Albert Einstein
|
|
|
|
|
|
Hi,
I could not find a specific hint that solves the problem.
Thanks
Yuval
"The true sign of intelligence is not knowledge but imagination." - Albert Einstein
|
|
|
|
|
Please guys help me...Can anyone give me some links for
1) INGRES Algorithm
2) System R Algorithm
3) SDD Algorithm
i tried to find out these, but could not get some satisfactory results.
|
|
|
|
|
Muzammil Saeed wrote: Please guys help me...Can anyone give me some links for
I posted some random* links, since you didn't mention whether you wanted explanatory material or reference-implementations.
Muzammil Saeed wrote: 1) INGRES Algorithm
There's the Ingres[^]-database.
Muzammil Saeed wrote: 2) System R Algorithm
An IBM-database[^], never used it personally.
Muzammil Saeed wrote: 3) SDD Algorithm
An example implementation[^].
You're studying databases and the way they work?
I are Troll  *) Not entirely random, but rather arbitrary
|
|
|
|
|
I am student and studying Dist. databse. I want these with some example....
|
|
|
|
|
The only examples that I could find[^] are part of a presentation. It seems that osun.org[^] has some good information too, but I didn't dig in deep enough to judge whether it's what you're looking for.
Have you considered turning your research-results into a CodeProject-article? 
I are Troll
|
|
|
|
|
SQL Server 2008 has the new 3-byte DATE format that I would like to use, being as I don't need the extra overhead of storing TIME.
I used bcp to generate the following format file from my table definition:
"<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="10" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="11"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="Symbol" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="2" NAME="myDate" xsi:type="SQLDATE"/>
</ROW>
</BCPFORMAT>"
xsi:type="SQLDATE" is used to define the transformation for the date in my ascii file to the DATE in the table.
A snippet from the input file is:
AAI 19940101
ACL 19981231
AKA 20091208
BULK INSERT interprets all of these dates to be 1900-01-01, which would be the default value I suppose.
But if I change my input file to:
AAI 1994-01-01
ACL 1998-12-31
AKA 2009-12-08
BULK INSERT inserts the correct date values into my table.
This is peculiar because if I define the table to be SMALLDATETIME instead of DATE and SQLDATETIM4 instead of SQLDATE, then either input file works OK.
It wouldn't be such a big issue for me except that I have thousands of files each with thousands of rows and converting them all before importing is going to be time consuming.
I think this is a bug in SS2008. I tried to report it to Microsoft, but to open a support ticket costs $99
1) Is there a free/simple way to report the bug to Microsoft
2) Is there an updated version of bcp.exe (I can't find one)
3) Is there a workaround that doesn't involve pre-formatting the input
Thanks
Steve
|
|
|
|
|
Gawiz wrote: 1) Is there a free/simple way to report the bug to Microsoft
Microsoft Connect[^]?
Gawiz wrote: 3) Is there a workaround that doesn't involve pre-formatting the input
Converting the results, by importing into the SMALLDATETIME and SQLDATETIM4 types and ALTERing the columntype afterwards.
I are Troll
|
|
|
|
|
I've written a program using C# that is supposed to access a database. When I try to actually open the connection, I get this exception:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
The database access code is contained in a separate assembly. This same assembly is used in another program (written in VB if that matters) that connects to the same database without any problems at all (on the same machine, with the same user logged in, and using the same connection string).
Why am I getting this exception in one program and not in another?
EDIT ===================
Here are the constructors for both apps:
VB: Dim myClass as new MyClass("abc", "def\geh")
C# MyClass myClass = new MyClass("abc", "def\geh");
The problem is that VB automatically escapes the backlslash, but c# requires @"def\geh"...
Stupid programmer tricks...
.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
modified on Tuesday, January 19, 2010 2:32 PM
|
|
|
|
|
Maybe some strings not escaped correctly?
|
|
|
|
|
The connection string is identical (it's created in the common assembly, and that was the very first thing I checked).
EDIT ===========
Why was this 1-voted?
modified on Wednesday, January 20, 2010 5:50 AM
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
