SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Updating Multiiple value


Updating Multiiple value

Author
Message
Shadab Shah
Shadab Shah
SSC Eights!
SSC Eights! (961 reputation)SSC Eights! (961 reputation)SSC Eights! (961 reputation)SSC Eights! (961 reputation)SSC Eights! (961 reputation)SSC Eights! (961 reputation)SSC Eights! (961 reputation)SSC Eights! (961 reputation)

Group: General Forum Members
Points: 961 Visits: 798
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
Mike John
Mike John
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3396 Visits: 5984
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



Shadab Shah
Shadab Shah
SSC Eights!
SSC Eights! (961 reputation)SSC Eights! (961 reputation)SSC Eights! (961 reputation)SSC Eights! (961 reputation)SSC Eights! (961 reputation)SSC Eights! (961 reputation)SSC Eights! (961 reputation)SSC Eights! (961 reputation)

Group: General Forum Members
Points: 961 Visits: 798
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.
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)

Group: General Forum Members
Points: 53524 Visits: 21207
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?


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Dave Brooking
Dave Brooking
SSChasing Mays
SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)

Group: General Forum Members
Points: 633 Visits: 1681
Maybe the OP wants to use something like the MERGE statement http://msdn.microsoft.com/en-us/library/bb510625(v=sql.100).aspx

Dave
Mike John
Mike John
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3396 Visits: 5984
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



Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)

Group: General Forum Members
Points: 53524 Visits: 21207
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'...


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Shadab Shah
Shadab Shah
SSC Eights!
SSC Eights! (961 reputation)SSC Eights! (961 reputation)SSC Eights! (961 reputation)SSC Eights! (961 reputation)SSC Eights! (961 reputation)SSC Eights! (961 reputation)SSC Eights! (961 reputation)SSC Eights! (961 reputation)

Group: General Forum Members
Points: 961 Visits: 798
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
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)

Group: General Forum Members
Points: 53524 Visits: 21207
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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
SQL006
SQL006
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1115 Visits: 1330
you should post proper ddl statement,sample data and expected results,so that it will be easy to unserstand and give u some solution
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search