CODEWITHSUNDEEP
sqlsql-server
maztt
maztt

Reputation: 12294

sql subquery update with 2 conditions

i have two tables

post 

int_PostId , dtm_Publishdate               createddate
1             2013-02-07 21:12:03.247
2             2013-03-07 21:12:03.247
3             2013-03-07 21:12:03.247

and postmeta

int_postmetaid, metakey ,     metavalue ,     int_PostId , int createddate
   1              chk           en                1             2013-02-07  
   2              chk           ps                1             2013-02-07  
   3              chk           cs                1             2013-02-07  
   4             publishdate     2013-03-07       1             2013-02-07
   5              chk           en                2             2013-02-07  
   6              chk           ps                2             2013-02-07  
   7              chk           cs                2             2013-02-07    
   8             publishdate     2013-03-07       2             2013-02-07
   9              chk           en                3             2013-02-07  
   10              chk           ps               3             2013-02-07  
   11              chk           cs               3             2013-02-07

i want to update post's dtm_Publishdate column with these condition. i want to do it in one query

1) if the publishdate metakey exist in the postmeta table update post's *dtm_Publishdate* with it's metavalue

2) if it doesn's exist then update the post's dtm_Publishdate with it's createddate

i am really stuck with the condition scenario. can someonehelp?

Upvotes: 0

Views: 95

Answers (2)

Beth
Beth

Reputation: 9607

if there's only ever one publishdate row in postmeta, you could try something like this:

UPDATE post 
SET dtm_publishdate =
    CASE WHEN pd.metavalue IS NOT NULL
        THEN metavalue
        ELSE createddate
    END
FROM
    post p LEFT OUTER JOIN
    (SELECT
        int_postID, metavalue
    FROM 
        post p INNER JOIN
        postmeta m ON
        p.int_postID = m.int_postID
    WHERE
        metakey = 'publishdate') pd ON
    p.int_postid = pd.int_postID

Upvotes: 0

Esoteric Screen Name
Esoteric Screen Name

Reputation: 6112

As long as you're on SQL Server 2008 or later, use the MERGE statement.

MERGE INTO post as Target
USING (SELECT int_PostId,metavalue 
    FROM postmeta 
    WHERE metakey='publishdate') 
    as Source (int_PostId,metavalue)
ON Source.int_PostId = Target.int_PostId
WHEN MATCHED THEN 
    UPDATE SET Target.dtm_Publishdate = Source.metavalue
WHEN NOT MATCHED THEN 
    UPDATE SET Target.dtm_Publishdate = Target.createddate

You may need to cast the metavalue column to a DateTime type in the WHEN MATCHED clause.

Upvotes: 1

Related Questions