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 07, 2012 5:38 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 7:57 AM
Points: 2,173, Visits: 344
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 07, 2012 5:44 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:42 AM
Points: 12,744, Visits: 31,078
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 07, 2012 6:17 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 7:57 AM
Points: 2,173, Visits: 344
Thanks Lowel,


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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:42 AM
Points: 12,744, Visits: 31,078
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 07, 2012 6:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 17, 2014 9:33 AM
Points: 6,754, Visits: 12,854
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
Posted Sunday, September 09, 2012 6:53 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782
Where is the DDL? Why don't you post something a person could use to help you? Why do you believed in the magical Kabbalah number “id” and name your table for Alcoholic Anonymous?

Seriously, we need DDL to work and you gave us nothing.

CREATE TABLE Policies
(policy_nbr CHAR(5) NOT NULL PRIMARY KEY,
issue_date DATE DEFAULT CURRENT_STAMP NOT NULL);

INSERT INTO Policies
VALUES ('s1234', '2012-04-09'), ('s2345', '2012-05-06');

>> we have another table(BB) with 2 columns <<

Sorry, but you cannot put a call to the CURRENT_TIMESTAMP in a DATE column. A column as to hold a scalar value of the proper data type which conforms to the columns constraints, if any.

>> we insert the records [sic: rows are not records] from BB into Policies table. In Policies table, the values of policy_nbr already exist. I want write the query to replace the old_policy_nbr <<

CREATE TABLE Policy_Nbr_Changes
(old_policy_nbr CHAR(5) NOT NULL PRIMARY KEY,
new_policy_nbr CHAR(5) NOT NULL);

MERGE INTO Policies AS Target
USING Policy_Nbr_Changes AS Source
ON Target.policy_nbr = Source.old_policy_nbr
WHEN MATCHED
THEN UPDATE
SET Target.policy_nbr = Source.new_policy_nb
Target.issue_date = CAST (CURRENT_TIMESTAMP AS DATE);



Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1356532
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse