Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Updating Multiiple value Expand / Collapse
Author
Message
Posted Monday, July 30, 2012 11:35 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 07, 2014 11:09 PM
Points: 220, Visits: 536
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
Post #1337674
Posted Tuesday, July 31, 2012 12:50 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:59 AM
Points: 2,663, Visits: 5,595
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




Post #1337690
Posted Tuesday, July 31, 2012 1:29 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 07, 2014 11:09 PM
Points: 220, Visits: 536
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.
Post #1337710
Posted Tuesday, July 31, 2012 1:33 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 3:47 PM
Points: 4,828, Visits: 11,177
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1337711
Posted Tuesday, July 31, 2012 1:41 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, March 19, 2013 7:24 AM
Points: 467, Visits: 1,681
Maybe the OP wants to use something like the MERGE statement http://msdn.microsoft.com/en-us/library/bb510625(v=sql.100).aspx

Dave
Post #1337713
Posted Tuesday, July 31, 2012 1:41 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:59 AM
Points: 2,663, Visits: 5,595
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



Post #1337715
Posted Tuesday, July 31, 2012 1:49 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 3:47 PM
Points: 4,828, Visits: 11,177
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1337717
Posted Tuesday, July 31, 2012 2:37 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 07, 2014 11:09 PM
Points: 220, Visits: 536
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
Post #1337731
Posted Tuesday, July 31, 2012 2:40 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 3:47 PM
Points: 4,828, Visits: 11,177
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1337733
Posted Tuesday, July 31, 2012 2:52 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 27, 2014 5:14 AM
Points: 177, Visits: 965
you should post proper ddl statement,sample data and expected results,so that it will be easy to unserstand and give u some solution
Post #1337736
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse