.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

help with query - update from 2 tables

Posted By:      Posted Date: September 15, 2010    Points: 0   Category :Sql Server
hi i have 2 tables     table1: num,X,Y     table2: num,X,Y i need to update X and Y in table1 where table1.num = table2.num how to do it ? i need it in Oracle query (i think that in sql server it will work too) thank's in advance 

View Complete Post

More Related Resource Links

How do I create a query that joins two tables and returns a single entry with the most recent update


Say I have the following two tables (Purchase & Customer). 

CustomerID StoreName PurchaseDate
1 Asheville 9/14/2010 15:49

MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.

Event Hanlder to update other SQL tables



I'm writing a small app to allow viewing and editing of a single SQL table (the _Assets table).

I have a form-view that allows the data to be viewed (ItemTemplate) and edited (EditItemTemplate).

Everything works well. All the SQL editing is done in the Mark-Up using a simple SqlDataSource, asp:Parameters and data-binding, (ie. there is no VB code behind).

However, I need to write an event-handler which updates other tables when the EditItemTemplate INSERT button is used.

This event handler needs to update OTHER SQL tables.

The idea is to create a HISTORY of changes (Updates) made to my _Assets table.

It would look something like this:

IF the value of the _Assets.Comments field is changed by the formview, then:

Open SQL connection to _CommentsHistory table

Update _CommentsHistory

SET CountrySerial = asp:Parameter Name "CountrySerial" from SqlDataSource ID="FindAsset"

SET OldComment = [the original Comment] asp:Parameter Name "Comments" from SqlDataSource ID="FindAsset"

SET NewComment = [the New Comment inserted in the formview] TextBox ID="CommentsTextBox" from FormView ID="FormView1"

SET ChangeDate = { fn (now)}

Set ChangedBy =

SqlDataAdapter.Update related tables



I would like to get 2 related tables from SQL Server to DataSet, insert new rows and save them back to SQL Db.

Let's say i have 2 related tables as shown below...

Table1 Table2
test1ID (Primary Key) test2ID (Primary Key)
data test1ID


In Sql i have Diagram with relationship and Update Rule is set to Cascade

I am trying to get them to dataset, insert "master" and "child" row and save them back to db.

I tried different approaches (none of them workes) and this is one of them:

Dim ds As New DataSet()
        Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("NastanitveConnectionString").ConnectionString)
        Dim sql As String = "SELECT * from Table1;Select * from Table2;"
        Dim d

How to update Sql Server related tables from Dataset(SchemaFill)

Hi!I would like to update new records that get inserted in DataSet to Sql Server Database.Below is a working example of how to do this if you use "DataAdapter.Fill(ds)". But i don't want to fill data, becouse i don't need data, i only need shema (I will use this dataset to insert new data to Sql), so i would like to use "DataAdapter.FillSchema(ds)"Please help me out at least with this one... i'm stuck... can't continue untill i crack this nut.<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default2.aspx.vb" Inherits="Default2" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title></title> </head> <body> <form id="form1" runat="server"> <div> <table> <tr> <td> <asp:GridView ID="GridView1" runat="server" ShowHeaderWhenEmpty="True"> <Columns> <asp:CommandField ShowSelectButton="True" /> </Columns>

Running a single SQL update query on multiple foxpro databases

Hi, Env : SSIS 2008 My requirement is to run a update SQL statement on multiple foxpro databases. The fox database paths are available in a SQL server table.  I've created a for each loop control and assigned the database path to a variable named dbpath. Within the loop, I added an "execute sql task" control. Now what properties are to be set to get the requirement done. Appreciate any help. Regards. BH  Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful. BH

Query DB and update Check Box List on JS/jQuery Function

Hey guys.  I'm not sure if this is the right forum to post this in but I've come to a roadblock in my app and I'm not sure which way I should go.  Currently, I have a slider control and two text boxes used to set the Start and End times of a section of a video.  Let's say the video is of some Music Awards.  In my database, I've tagged the start and end time of each celebrities on-screen appearance.  When the user adjusts their start and end times on the page, I want to query the database table and pull back all celebrities within that range.  The problem is I don't know how to do this in JS.Can I use the Check Box List and bind the values to it?  Do I need to render a table of Check Boxes in JavaScript?  Do I call the Server-Side code via Ajax and then partially render the page?Any help, comments or suggestions would be great.

Select and update in same query

Hi all, I need to update the selected rows from a select statement and update it using an update query. For that the query i am using is UPDATE SERVERACCOUNTSTRAN..TRAN_JOURNALDET SET CONTRATRANSACTIONHEADCODE=T.CODE FROM ##TRAN TR INNER JOIN SERVERSSRETAIL..TRANSACTIONHEAD T ON T.SHORTNAME=TR.CREDT_ACCT WHERE T.COMPANYCODE=1 AND CONTRATRANSACTIONHEADCODE=0 Now the problem is all the values of contratransactionheadcode field is updated to 18. But the real values are different for each row. only the first row value is 18 which is updated to every other row. Thanks in advance.Sujith Kumar www.sujithkumar.tk

Update related tables

Hi All, Forgive me if this is the wrong place to post this question. I looked for a forum on SQL statements but couldn't find one. I have a FormView with data from two tables in the SqlDataSource.  The second table is linked to the first by a FK/PK. There is an example from the Northwind SQL database, e.g. if the Products table is the main table but I want to display the Category Description in the FormView and edit it, how do I write the Update and Insert statements.? I have tried using the query builder in VS 2008  but it doesn't seem to work. Any help greatly appreciated. Regards,Penn

Can anybody Identify whats the error in this update query ??

I am getting error as Invalid Column name 'JAVA' when i am havin a value JAVA in txtExamName text box.In my database ExamName field is having a data type nvarchar(25)."UPDATE Exam SET ExamName=' " + txtExamName.Text + " ',SubjectId=" + SubId + ",StartDate=' " + txtDate.Text + " ',StartTime=' " + txtHour.Text + ":" + txtMinutes.Text + " ',EndTime=' " + txtHour0.Text + ":" + txtMinutes0.Text + " ',ExamDescription=' " + txtDescri.Text + " ',TotalNoOfQuestion=" + txtQue.Text + ",MarkofCorrectAns=" + txtPoint.Text + ",MarkofWrongAns=" + txtExamName.Text + " WHERE ExamId=" + Convert.ToInt32(Session["ExamId"])

How index will effect in update query

Hi All, I have one update query it has lot of joins with other table ,If we add index in the secondary table with this join condition will it improve the performance? eg: UPDATE FunctionalAcknHeaders  SET cStatus = 'E'  FROM FunctionalAcknHeaders fh   LEFT JOIN OrderHeaders oh ON cast(oh.nOrderHeaders_id as varchar(10)) = 'abc'  WHERE oh.nOrderHeaders_id is null   AND fh.cStatus = 'R'   if i create index on OrderHeaders  table nOrderHeaders_id, will it improve the performance ? Thanks, Roshan

Why does'nt an UPDATE query finish

I have been asked the following question in one of SQL interviews: "An application issues an UPDATE statement to a table. But the UPDATE query never ends i.e it is going on for ever. What might be the reason?" I gave various reasons: --> May be the table is blocked by some other query. --> May be the query is updating a huge chunk of data. --> May be appropriate indexes are not created on the table. --> May be the indexes are defragmented. --> May be the statistics on the table need to be updated. --> May be the server is too busy to process the request. But the interviewer said all the above conditions are satisfied i.e. the query is updating only 100K rows and the table has appropriate rebuilt indexes with statistics updated and the server is not very busy. There is no blocking as well. What else could be the reason?   Thanks, Chaitanya

Update query help Dense_Rank .....

Hi,  I need help updating MinAmounttBilled and Dense_Rank_ fields in the table @MinNums_TestData below. MinAmountBilled - For each customer this field should be populated with the smallest value in the AmountBilled column. Dense_Rank_ - This field groups all same customer numbers, need to know how i can update this filed as well. Actuall table i will be updating has 10 million records. Here si the Sample data :- -- Create Sample Table Declare @MinNums_TestData TABLE ( CustomerNumber [varchar](20) , Dense_Rank_ [varchar](20) , AmountBilled [varchar](20) , MinAmounttBilled [varchar](20) ) -- Test data INSERT INTO @MinNums_TestData ( CustomerNumber, Dense_Rank_,AmountBilled,MinAmounttBilled) SELECT '100', '', '15243' ,''UNION ALL SELECT '100', '', '1243' ,''UNION ALL SELECT '100', '', '34243' ,''UNION ALL SELECT '100', '', '43' ,''UNION ALL SELECT '200', '', '56' ,''UNION ALL SELECT '200', '', '65' ,''UNION ALL SELECT '200', '', '34243' ,''UNION ALL SELECT '9', '', '6' ,''UNION ALL SELECT '9', '', '5' ,''UNION ALL SELECT '20', '', '34243' ,''UNION ALL SELECT '200', '', '56' ,''UNION ALL SELECT '3', '', '2' ,''UNION ALL SELECT '1', '', '3' ,'' -- Table to group all customers Declare @MinNums_TestData_2 TABLE ( CustomerNum

Insert/Update data to multiple tables on a single button click


Hi experts,

I need to insert/update data to 3 or more tables on a single button click.What is best method for achieving this?I am using mysql as my DB.

Thanks for your valuable reply in advance.

update query having minimum of expresion



I have this 3 tables:

CREATE TABLE [dbo].[TC_103_M](

	[TIP_REC] [int] NULL,

	[ID_CMP] [int] NOT NULL,

	[ID_FILE] [int] NULL,

	[CUT_OFF] [datetime] NOT NULL,

	[FIN_DTST] [datetime] NULL,

	[FIN_DTVA] [datetime] NULL,

	[FIN_DTSC] [datetime] NULL,

	[FIN_DIV] [varchar](3) NULL,

	[FIN_TIPTAS] [varchar](2) NULL,

	[FIN_PREAMM] [varchar](1) NULL,

	[FIN_RIMB] [varchar](2) NULL,

	[FIN_DTIA] [datetime] NULL,

	[FIN_DTCA] [datetime] NULL,

	[FIN_FRCAP] [varchar](8) NULL,

	[FIN_AMERO] [float] NULL,

	[FIN_AMNOM] [float] NULL,

	[FIN_DEBRES] [float] NULL,

	[FIN_AMINS] [float] NULL,

	[FIN_EAD] [float] 

query all tables in the database with slightly different query


Declare @Table_Name varchar(50)

FROM sys.Tables

OPEN table_cursor

FETCH NEXT FROM table_cursor
INTO @Table_Name

While @@FETCH_STATUS = 0
  SELECT * FROM @Table_Name

 FETCH NEXT FROM table_cursor
  INTO @Table_Name

CLOSE table_cursor
DEALLOCATE table_cursor

The above code gives error, maybe I am mistaken about the way cursors work.


But how can I acheive the same pirpose


I want to query all tables in the database with the same query in which the where condition will slightly vary.

The above gives this error


Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@Table_Name".
Msg 1087, Level 15, State 2, Line 13
Must declare the table variable "@Table_Name".
Msg 137, Level 15, State 2, Line 16
Must declare the scalar variable "@Table_Name".



Update query from select statement


Cana you I need to do an update from a select statement...i'm really close here but have not quite figured out the nuance...

the full select statement provides correct results and i've joined the outer table with the inside table....can you make recommendations?

BEGIN TRAN --rollback

UPDATE geprs_price..T_ITEM_PRC_CHG 


		FROM geprs_price..T_ITEM_PRC_CHG gep
		and gep.prc_eff_dt = dm.prc_eff_dt
		LEFT JOIN construct..T_AD_USERS con
		ON dm.CONFIRM_BY  = con.UID
		LEFT JOIN construct..T_AD_USERS crt
		ON dm.CREATE_BY  = crt.UID

		AND dm.PRC_TYP_ID in (39)



ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend