Solved

SQL Server - update statement

Posted on 2012-03-29
6
330 Views
Last Modified: 2012-06-21
Hi experts,

When I make an update statement to multipe fields, but there are potentially two records from which to choose from to get the data, I understand SQL picks a record for you. But does it choose the same record for every field? I'm concerned that field 3 will come from record 1, field 4 will come from record 2, field 5 from record 1, etc.

Thanks!
0
Comment
Question by:JC_Lives
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 18

Expert Comment

by:Cluskitt
ID: 37782251
Not sure I understand what you mean. Can you post your query here?
The easiest way to see how update works is to build first the select statement to return all the lines you want:
SELECT * FROM Table WHERE MyCondition=1
Then change the select fields to match the ones you will get:
SELECT MyNewValue1 AS MyField1, MyNewValue2 AS MyField2, MyField3, MyNewValue4 AS MyField4 FROM Table WHERE MyCondition=1
And then you can update the data when you're happy with the results:
UPDATE Table SET MyField1=MyNewValue1, MyField2=MyNewValue2, MyField4=MyNewValue4 WHERE MyCondition=1
0
 

Author Comment

by:JC_Lives
ID: 37782291
Sure, code is below. I create the staging table with unique combinations of acct, approvaldate, and modtype. But then I have to update the table with the other fields. But the source for the update will sometimes have a match on acct# and approvaldate. Hoping it always chooses consistently which record to use, for all fields. thanks.

-------------------------------------------------------------------------------------------------------------
-- Drop Staging table
-------------------------------------------------------------------------------------------------------------
GO

IF OBJECT_ID('LMCR_AA_Approve_Staging') IS NOT NULL
BEGIN
DROP TABLE dbo.LMCR_AA_Approve_Staging
END
GO
PRINT 'Staging Dropped'  PRINT CURRENT_TIMESTAMP
GO

-------------------------------------------------------------------------------------------------------------
-- Create staging Table
-------------------------------------------------------------------------------------------------------------
CREATE TABLE dbo.LMCR_AA_Approve_Staging
(
      [acct#] [numeric](9, 0) NOT NULL,
      [approvaldate] [datetime] NOT NULL,
      [lettertype] [varchar](50) NULL,
      [aatype] [varchar](100) NULL,
      [sd_type] [varchar](5) NULL,
      [pool_name] [varchar](255) NULL,
      [tmpPoolName] [varchar] (255) NULL,
      [hamp_id] [smallint] NULL,
      [ModType] [varchar](25) NULL
)
PRINT 'Staging table created'  PRINT CURRENT_TIMESTAMP
GO

-------------------------------------------------------------------------------------------------------------
-- Insert data from source into staging
-------------------------------------------------------------------------------------------------------------
INSERT INTO dbo.LMCR_AA_Approve_Staging
      (
            acct#
            ,approvaldate
            ,ModType
      )
SELECT DISTINCT
      A.acct#
      ,MAX(A.approvaldate) as approvaldate
      ,A.ModType
FROM
      WKCMA68508.dlr.dbo.AA_ApprovedToMailLMO A
WHERE
      acct# IS NOT NULL
      AND
            (
                  (ModType = 'HAMP' AND sd_type IN ('sd-10', 'NHRP'))
                  or
                  ModType in ('Non-HAMP', 'DOJ')
            )
      AND ISNULL(pool_name, '') <> 'Test'
      AND ISNULL(status,'') <> 'Cancelled'
GROUP BY A.Acct#, A.ModType

PRINT 'Staging table records inserted'  PRINT CURRENT_TIMESTAMP
GO

UPDATE dbo.LMCR_AA_Approve_Staging
SET
      lettertype = a.lettertype
      ,aatype = a.aatype
      ,sd_type = a.sd_type
      ,pool_name = a.pool_name
      ,tmpPoolName = a.tmpPoolName
      ,hamp_id = a.hamp_id
FROM
(
      SELECT DISTINCT
            A.acct#
            ,A.approvaldate
            ,A.lettertype
            ,A.aatype
            ,A.sd_type
            ,A.pool_name
            ,A.tmpPoolName
            ,A.hamp_id
            ,A.ModType
      FROM
            WKCMA68508.dlr.dbo.AA_ApprovedToMailLMO A
      INNER JOIN
            (
                  SELECT
                        acct#
                        ,MAX(approvalDate) AS 'approvaldate'
                        ,ModType
                  FROM
                        WKCMA68508.dlr.dbo.AA_ApprovedToMailLMO
                  WHERE
                        acct# IS NOT NULL
                        AND
                              (
                                    (ModType = 'HAMP' AND sd_type IN ('sd-10', 'NHRP'))
                                    or
                                    ModType in ('Non-HAMP', 'DOJ')
                              )
                        AND ISNULL(pool_name, '') <> 'Test'
                        AND ISNULL(status,'') <> 'Cancelled'
                  GROUP BY
                        acct#
                        ,ModType
            ) B
      ON
            A.acct# = B.acct#
            AND A.approvaldate = B.approvaldate
) a

PRINT 'Staging table updated'  PRINT CURRENT_TIMESTAMP
GO
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 37782338
Ok, if the subquery returns multiple records, you will get an error. You have to establish a unique value for each field. Now, assuming that the inner query a will always return just one row, all rows in dbo.LMCR_AA_Approve_Staging will have the same value.
0
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

 

Author Comment

by:JC_Lives
ID: 37782346
it doesn't get a error... it chooses a record itself to update from.
0
 
LVL 9

Accepted Solution

by:
rajeevnandanmishra earned 500 total points
ID: 37782348
Hi,
in case of multiple records (in source), the update is going to pick the same row for all fields update. This is sure.
The only thing which is not sure, is, which row it will pick from source.

You can always limit the source rows by using "group by" or "distinct".
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 37782373
True, it would only return an error if you used a subquery in the assignment part. That way it will always update with the first row.
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question