SQL Server - update statement

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!
JC_LivesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

CluskittCommented:
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
JC_LivesAuthor Commented:
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
CluskittCommented:
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
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

JC_LivesAuthor Commented:
it doesn't get a error... it chooses a record itself to update from.
0
rajeevnandanmishraCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CluskittCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.