Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Server - update statement

Posted on 2012-03-29
6
Medium Priority
?
337 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

581 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