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

x
?
Solved

sql update query help

Posted on 2014-03-26
3
Medium Priority
?
273 Views
Last Modified: 2014-03-26
I am in need of help writing an update query.

I have a single table:  [companycitymaster]  
This table contains columns [state], [citycode] and [splc].

I need to update the state = 'QC' rows with the [splc] value where
state = 'PQ' BUT only if the state = 'QC' rows are blank and only where the [citycode] is equal.  

I need to do this update before i delete all the pq's.  

Thanks

See data below.

data
0
Comment
Question by:hougie40
3 Comments
 
LVL 36

Expert Comment

by:ste5an
ID: 39956550
E.g.

UPDATE  O
SET     SPLC = ( SELECT I.SPLC
                 FROM   CompanyCityMaster I
                 WHERE  I.CityCode = O.CityCode
                        AND I.[State] = 'PQ'
               )
FROM    CompanyCityMaster O
WHERE   O.[State] = 'QC'
        AND LEN(RTRIM(COALESCE(O.SPLC, ''))) = 0;

Open in new window

0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 39956726
UPDATE ccm_qc
SET splc = ccm_pq.splc
FROM (
    SELECT state, citycode, splc
    FROM companycitymaster
    WHERE
        state = 'QC' AND
        splc = ''
) AS ccm_qc
INNER JOIN (
    SELECT state, citycode, splc
    FROM companycitymaster
    WHERE
        state = 'PQ' AND
        splc > ''
) AS ccm_pq ON
    ccm_pq.citycode = ccm_qc.citycode
0
 

Author Closing Comment

by:hougie40
ID: 39956791
perfect.  I prefer the inner join over the where clause join.

Thanks scott.
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Screencast - Getting to Know the Pipeline

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