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

Help Needed Expand / Collapse
Author
Message
Posted Friday, September 7, 2012 5:38 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 10, 2015 4:34 AM
Points: 2,280, Visits: 365
Hi,

we are having one table (AA) with 3 columns

Id,Polcinumber,date
1,s1234,04/09/2012
2,s2345,05/06/2012


we are having another table(BB) having 2 cloumns

Id,Policynumber,date
1,s71234, currentdate


we are inserting the records from BB to AA table.in AA table the values of Polcynumbers are already existing

i want write the query to replace the oldpolicynumbers

can any one help on this
Post #1355917
Posted Friday, September 7, 2012 5:44 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 3:19 PM
Points: 13,616, Visits: 34,764
assuming the ID is the join criteria between the two tables, this will do it:
UPDATE AA
SET AA.Polcinumber = BB.Policynumber
FROM BB
WHERE AA.ID = BB.ID
AND AA.Polcinumber <> BB.Policynumber



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1355922
Posted Friday, September 7, 2012 6:17 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 10, 2015 4:34 AM
Points: 2,280, Visits: 365
Thanks Lowel,


how to insert the currentdate into AA table with new policy number
Post #1355938
Posted Friday, September 7, 2012 6:41 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 3:19 PM
Points: 13,616, Visits: 34,764
Div Goud (9/7/2012)
Thanks Lowel,


how to insert the currentdate into AA table with new policy number


just expand the number of columns being updated;
...
SET AA.Polcinumber = BB.Policynumber,
Column2 = GetDate(), --a function fromt eh server
Column3 = 'Approved', --a static value
Column4 = BB.SomeOtherColumn -- a value in the other table



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1355943
Posted Friday, September 7, 2012 6:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:40 AM
Points: 7,329, Visits: 15,462
DELETE aa
FROM aa
INNER JOIN bb
ON bb.ID = aa.ID

SET IDENTITY_INSERT aa ON

INSERT INTO aa (Id,Policynumber,date)
SELECT Id,Policynumber,date
FROM bb

SET IDENTITY_INSERT aa OFF



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1355944
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse