Updating Multiiple value

  • Suppose i have 2 table as Emp and Emp2.

    select empid from Emp where deptid=2

    The output of the above query is

    empid

    1

    2

    3

    5

    7

    9

    10

    16

    17

    Now,Insert all the above records in Emp2 from Emp where deptid=2

    which should look something like this

    update Emp2 set empid= empid from Emp where deptid=2

    So my question is : What would be the exact query for achieving the above

  • Assuming none of the rows you want to insert into emp2 are already in that table then:

    insert into emp2(empid)

    select empid

    from emp

    where deptid = 2

    Should put you on the right lines.

    Mike

  • I tried your query but it is not working for me. This query does not overwrite the existing value in emp2 but it adds additional records. I want something which would overwrite the records.

  • shahsn11 (7/31/2012)


    I want that the record in another table i.e in our case emp2 should be overwritten. In my case there can be some records in emp2 which would already would be having some record for deptno:2

    Overwritten with what?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Maybe the OP wants to use something like the MERGE statement http://msdn.microsoft.com/en-us/library/bb510625(v=sql.100).aspx

    Dave

  • In your original question you did say the records had to be INSERTED in to the new table, which is what my statement did.

    Can you post full DDL of both tables, and some sample data that shows exactly what you are trying to do.

    Do you want to INSERT new rows into emp2, UPDATE existing rows, or a combination of the two?

    Mike

  • Mike John (7/31/2012)


    In your original question you did say the records had to be INSERTED in to the new table, which is what my statement did.

    Can you post full DDL of both tables, and some sample data that shows exactly what you are trying to do.

    Do you want to INSERT new rows into emp2, UPDATE existing rows, or a combination of the two?

    Mike

    Precisely put. In the OP's favour, I have noticed that those who are new to SQL Server do not readily distinguish between the meanings of the words 'Insert' and 'Update'...

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Hi,

    I might not be able to put my question in proper way so let me put the same question other way around.

    -------- //some logic

    -------- //some logic

    where empid = (select empid from Emp where deptid=2)

    Now in the above query the empid returns more than one empid. Hence the above syntax or way of implementation is wrong. I would like to know what would you do when you encounter a situation like this

  • I'm afraid that what you are asking for is not clear enough for me to offer a solution.

    Please see the link in my signature for details of how to post DDL and sample data in such a way as to make this clear. If you take the time to do this, a working & tested solution will be usually be provided within a matter of minutes.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • you should post proper ddl statement,sample data and expected results,so that it will be easy to unserstand and give u some solution

  • dilipd006 (7/31/2012)


    you should post proper ddl statement,sample data and expected results,so that it will be easy to unserstand and give u some solution

    Are you trying to boost your points by rewriting my posts? 😀

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • shahsn11 (7/31/2012)


    Hi,

    I might not be able to put my question in proper way so let me put the same question other way around.

    -------- //some logic

    -------- //some logic

    where empid = (select empid from Emp where deptid=2)

    Now in the above query the empid returns more than one empid. Hence the above syntax or way of implementation is wrong. I would like to know what would you do when you encounter a situation like this

    First you should use "in" not "=" if you have multiple records returned

    where empid in (select empid from Emp where deptid=2)

    Second: post the exact DDL , and as far as i have understood ..you may be looking something like MERGE as Dave mentioned...check this url..

    http://technet.microsoft.com/en-us/library/bb510625.aspx

    ~ demonfox
    ___________________________________________________________________
    Wondering what I would do next , when I am done with this one :ermm:

  • I would again try to make to understand what i am trying to achieve.Since i am a newbie , the first thing they teach to me was C.

    I was trying to implement the same logic as that of C over here. First i would present a very simple C logic followed by what i want.

    for(int i=0;i<=5;i++)

    {

    -----------

    -----------//some logic

    }

    In the above code i am able to perform the logic for each value of i.

    In SQL Server over here 'i' is nothing but 'empid'. i want to perform a set of logical step(Update) for every value empid in employee table.

  • SQL is very unlike c. It is fundamentally a set based language, so very rare that you need loops and so on.

    I think you need a merge statement to solve this, and if you can post the DDL of both tables one of us will almost certainly give you the full statement you need.

    It is hard for anyine to help when they do not know exactky what the tables look like.

    Mike

  • it also appears like you are missing a primary key, thus allowing multiple rows with the same empid to be inserted.

    As suggested, ddl, data, and expected results would be helpful.

    Leonard

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply