.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

Select and update in same query

Posted By:      Posted Date: September 09, 2010    Points: 0   Category :Sql Server
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

View Complete Post

More Related Resource Links

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)



SELECT and UPDATE query problems



I have a web form that should display advertisements depending of the number of impressions (number stored in a database) and I also need to update the number of impressions when page is loaded. The advertisements are displayed on a web page, but the problem is I don't know where to insert the SqlCommand for updating number of impressions.

Labels are used for displaying the number of impressions.  

        String connectionString = WebConfigurationManager.ConnectionStrings["CMS"].ToString();
        using (SqlConnection connection = new SqlConnection(connectionString))

            using (SqlCommand commandReklama = new SqlCommand("SELECT * FROM Reklama", connection))
                SqlDataReader readerReklama = commandReklama.ExecuteReader();
                while (readerReklama.Read())
                    string redirectUrl = readerReklama.GetString(1);
                    string url = readerReklama.GetString(2);
                    int brojPrikazivanja = readerReklama.GetInt32(3);
                    bool show = readerReklama.GetBoolean(4);                  
                    string naziv = readerReklama.GetString(5);
                    int location = readerReklama.GetInt32(6);

                    if (

UPDATE query based on the SELECT query



Can you please advise how can i achive following UPDATE query based on the SELECT query. You can see my comments in Green

UPDATE [Table_POforecast]
   SET [ForecastAmount] = [ForecastAmount]* Coeff --- Coeff value to be from SELECT Statement
 WHERE [PO_No] = [PO_No] --- PO_No to match from SELECT statement

--- SELECT Statement
SELECT     PO_No, (NewBalance / TotalForecastAmount) AS Coeff
FROM       View_Forecast
WHERE     (OutstandingForecast < 0)



On New Item form, select item on choice dropdown based on query result.


I posted this on Experts Exchange awhile back with no answer, so here's hoping someone here has an idea :)

So I've mostly figured this problem out, but I need some help in getting over the hump... note that I'm using JavaScript to do this (SPServices) on SharePoint 07'.

I'm on the New Items page of a list. Based on a user's selection in a "Teacher" dropdown, I'm doing the following:

1.) Get the value of the Teacher field and several other fields (School and Visit Date)
2.) Run a CAML query based on those fields to find the last list item entered for that Teacher

So here's where I'm stuck...

I have 10 choice fields, all with choices 0, 4, 7, and 10. Depending on what they were set to in the last entered item (which I queried), I want to set the selected item on each of those dropdowns to equal that. So "Score 1" was set to "4" on the la

select query


Hi All.

I have 2 table.


ID  | Person_Name

1   |  abc

2   |   def

3   |   ghq


ID  | Product  | ExportPersonID  | ImportedPersonID |  OwnerID

1    |   a         |   1                      |   2                           |  3

2    |  B          |    2                     |   3                           |  1

I want above two table output

ID    Product      |  ExpotedPerson|    ImportedPerson  |  Owner

1          a       &n

condition Select query

Hi All.Greetings.I have 1 table.Table 1ID | Name  | Sex       |  DateofBirth1   | abc    | Female  | 19802   |abc2   | Female  |20083   |abc3   |Male       |19854   |abc4   |Male      | 2009I want to see if age is greater then 3 year it will adult girl or adult boy. and If age is less then 3 year then it will be child girl or child boy.I have Output like thenName | Sex         |abc    | Adult girl |abc2  | child girl  |abc4  | child boy | etc.....How to write condition query to get my result.Thanks in Advance.

Copy a column with update and select

Hi, I am using SQL Compact 3.5 I am trying to copy the values of a column called "CODIGO" from one table to another table. I mean, I want to copy the column "CODIGO"  from table  "DATOS_ACADEMICOS" to table "ALUMNOS". I use this code: UPDATE ALUMNOS  SET CODIGO=(SELECT DATOS_ACADEMICOS.CODIGO FROM DATOS_ACADEMICOS WHERE DATOS_ACADEMICOS.ALUMNO_ID=ALUMNOS.ID)   But I get an error in the SELECT statment: Major error 0x80040E14, Minor error 25501 [ Token line number = 2,Token line offset = 13,Token in error = SELECT ]   I also tried this code with the same error: UPDATE ALUMNOS  SET CODIGO = (SELECT DATOS_ACADEMICOS.CODIGO             FROM DATOS_ACADEMICOS             WHERE DATOS_ACADEMICOS.ALUMNO_ID=ALUMNOS.ID) WHERE EXISTS (SELECT 1 FROM DATOS_ACADEMICOS WHERE DATOS_ACADEMICOS.ALUMNO_ID=ALUMNOS.ID)   How can I copy from one column to another in a SQL sentence? Thanks    

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


Hi Everone,    Can any one help me out in creating a dynamic select statement, so far my SP look like belowALTER PROCEDURE [dbo].[CheckAccess]@TABLENAME VARCHAR(100),@JNID INT=0ASBEGIN    SET NOCOUNT ON;    DECLARE @SQ VARCHAR(500)    DECLARE @ID INT =0     SET @SQ= ' SET @ID = SELECT TOP 1 ID FROM ' +  @TABLENAME + ' WHERE PID ='+ @JNID       PRINT @SQ    EXEC(@SQ)       IF (@ID>0)      BEGIN        RETURN 1      END    ELSE      BEGIN        RETURN 0      ENDENDGOlet me know where i am going wrong

ODBC Select if Statement in MAS90 query

Hi, I am connecting to a MAS90 database via DSN and am having trouble with the query string for ODBC.  I can collect most all fields if I SELECT them directly, however, I want to create a result column based on which cost is higher. As far as I know, this select statement in to and ODBC connection needs to comply with MSSQL I have tried SELECT IM1_InventoryMasterfile.ItemNumber, IFF(IM1_InventoryMasterfile.LastCost > IM1_InventoryMasterfile.AveCost,IM1_InventoryMasterfile.LastCost,IM1_InventoryMasterfile.AveCost) as 'Cost', IM1_InventoryMasterfile.TotalQtyOnHand FROM IM1_InventoryMasterfile IM1_InventoryMasterfile And SELECT IM1_InventoryMasterfile.ItemNumber, CASE WHEN IM1_InventoryMasterfile.LastCost > IM1_InventoryMasterfile.AveCost THEN IM1_InventoryMasterfile.LastCost ELSE IM1_InventoryMasterfile.AveCost END as 'Cost', IM1_InventoryMasterfile.TotalQtyOnHand FROM IM1_InventoryMasterfile IM1_InventoryMasterfile Neither are working?  What am I missing here? Can I do this sort of if then statement in MSSQL?   Thanks for your help 

Write select query with a paremetrized where clause, and populate database with the retrieved column

I want to be able to retrieve column values using the dropdownlist values as parameters then populate the tbPO table with the retreived values. Where and how do I do it. ImportsSystem.Data.OleDb ImportsSystem ImportsSystem.IO Imports ?directcostDataSetTableAdapters PartialClass po_header  Inherits System.Web.UI.Page  Dim cn As OleDbConnection  Dim cmd As OleDbCommand  Dim dr As OleDbDataReader  Dim icount As Integer  Dim str As String  Dim vendor_id As Object   ?  Public Sub btn_click(ByVal sender As Object, ByVal e As System.EventArgs) Handles button1.ClickTry   context.Items.Add(     cmd.CommandType = System.Data.   cmd.CommandText = Dim context As HttpContext = HttpContext.Current"Company_Name", DropDownList3.Text)Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Users\Owner\Documents\Visual Studio 2010\WebSites\WebSiteJun25\App_Data\directcost.mdb;")Dim cmd As New OleDbCommand()CommandType.Text"insert into tbPO(ponumber, suppliername, ShipTo, basictype, DateNeeded, Purpose,ShippingInstr, ProjId) values(?,?,?,?,?,?,?,?)" cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.Add

Tricky SELECT query from a Single Table

Hi I have a 2 rows of data in a table as mentioned below   Table1             ElementID Month Year Planned Cost UnplannedCost PlannedExpense UnPlannedExpense 4 9 2010 NULL 40 NULL 20 4 9 2010 400 NULL 200 NULL  I need a SELECT query to get the output in a single row as ElementID Month Year Planned Cost UnplannedCost PlannedExpense UnPlannedExpense 4 9 2010 400 40 200 20 Could anybody help me in writing a query for this? Thanks

configure both select and Update statements in ADO.NET

 i would like to retrieve a value with select and perform a calculation and then save using update in the same batch code?  

How to Create an MDX Query Parameter to Select 30 Values from a Dimension?

Hi, I'm using SSRS 2005 to report on an SSAS cube that contains a Procedure dimension.  I don't need to use members of this dimension in my report, but rather need to select records (patients) where their chart has one or more of the codes.  I've researched this today and cannot locate the best approach.  Thus far, I've attempted to create an MDX query parameter as part of my dataset.  However, I don't know whether this is the correct approach, and how to structure the syntax so that only records with one or more procedures are included in the report?  If so, what is the proper MDX syntax for setting my Procedure code equal to the query parameter? Thanks, Sid

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.

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"])

Error in select query

Hi, I am tring to use the following command but getting the following error message --Query Select * from CTOR_DBPF\Amord_wrk\dbo.rate2010 Server:   CTOR_DBPF Database: Amord_wrk Table:    rate2010 --Error message Line 1: Incorrect syntax near '\'.
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