Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Server - update statement

Posted on 2012-03-29
6
Medium Priority
?
333 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 

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 2000 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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

670 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