Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Server - update statement

Posted on 2012-03-29
6
Medium Priority
?
336 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
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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

886 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