#1
  1. I love your chinese eyes :*
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Jan 2006
    Location
    Her heart... she claims!
    Posts
    1,703
    Rep Power
    1054

    Update one col from another col based on primary key


    Hi

    I have a table which stores referral URLs in column 'ref' and primary key of the table is 'clid' I have added another column which will store the name of the search engine referral by the name 'SearchEngine'. Now what I want to do is to avoid PHP processing and write a mysql query as follow,

    Code:
    UPDATE clicks 
    SET SearchEngine=(
    SELECT (
    CASE WHEN (LOCATE('.google.',ref)!=0) THEN 'Google' 
    WHEN (LOCATE('.yahoo.',ref)!=0) THEN 'Yahoo' 
    WHEN (LOCATE('.msn.',ref)!=0) THEN 'Microsoft' 
    WHEN (LOCATE('.microsoft.',ref)!=0) THEN 'Microsoft') 
    WHEN (LOCATE('.live.',ref)!=0) THEN 'Microsoft') 
    ELSE 'Others'  END
    ),
    @tmpo:=clid 
    FROM clicks WHERE ref!=''
    ) 
    WHERE (clid=@tmpo);
    but it gives me following error,


    Code:
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') WHEN (LOCATE('.live.',ref)!=0) THEN 'Microsoft') ELSE 'Others'  END),clid:=@tm' at line 1
    Basically i want to parse referral url, decide the search engine name and then store its name in the new column based on primary key so that correct values are stored in correct record.

    Any help is much appreciated.
    Last edited by jojoba; March 30th, 2009 at 09:16 AM.
  2. #2
  3. I love your chinese eyes :*
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Jan 2006
    Location
    Her heart... she claims!
    Posts
    1,703
    Rep Power
    1054
    I had some syntax probs in my query, I fixed it as below

    Code:
    UPDATE clicks SET SearchEngine=
    (
    SELECT 
    (
    CASE WHEN (LOCATE('.google.',ref)!=0) THEN 'Google' 
    WHEN (LOCATE('.yahoo.',ref)!=0) THEN 'Yahoo' 
    WHEN (LOCATE('.msn.',ref)!=0) THEN 'Microsoft' 
    WHEN (LOCATE('.microsoft.',ref)!=0) THEN 'Microsoft' 
    WHEN (LOCATE('.live.',ref)!=0) THEN 'Microsoft' 
    ELSE 'Others' 
     END
    ),
    @tmpo:=clid 
    FROM clicks 
    WHERE ref!='') 
    WHERE (clid=@tmpo);
    and now getting the following error for which i was trying to create temporary variable,

    Code:
    ERROR 1093 (HY000): You can't specify target table 'clicks' for update in FROM clause
    Last edited by jojoba; March 30th, 2009 at 09:20 AM.
  4. #3
  5. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2006
    Location
    Hindustan
    Posts
    1,121
    Rep Power
    0
    Try this:

    Not Tested.

    Code:
    UPDATE clicks  SET SearchEngine=( SELECT ( CASE WHEN (LOCATE('.google.',ref)!=0) THEN 'Google'  WHEN (LOCATE('.yahoo.',ref)!=0) THEN 'Yahoo'  WHEN (LOCATE('.msn.',ref)!=0) THEN 'Microsoft'  WHEN (LOCATE('.microsoft.',ref)!=0) THEN 'Microsoft'  WHEN (LOCATE('.live.',ref)!=0) THEN 'Microsoft' ELSE 'Others'  END ), @tmpo:=clid  FROM (
             select * from clicks
          ) as x
     WHERE ref!='' )  WHERE (clid=@tmpo)

    Comments on this post

    • jojoba agrees : thank you
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,795
    Rep Power
    4332
    simplify, simplify, simplify
    Code:
    UPDATE clicks 
       SET SearchEngine =
           CASE WHEN LOCATE('.google.',ref)    <> 0 THEN 'Google' 
                WHEN LOCATE('.yahoo.',ref)     <> 0 THEN 'Yahoo' 
                WHEN LOCATE('.msn.',ref)       <> 0 THEN 'Microsoft' 
                WHEN LOCATE('.microsoft.',ref) <> 0 THEN 'Microsoft' 
                WHEN LOCATE('.live.',ref)      <> 0 THEN 'Microsoft' 
                ELSE 'Others'  END
     WHERE LENGTH(ref) > 0

    Comments on this post

    • jojoba agrees : thanks, and I am buying your book :)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. I love your chinese eyes :*
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Jan 2006
    Location
    Her heart... she claims!
    Posts
    1,703
    Rep Power
    1054
    I didn't know I could do it without referencing clid (PK) for each record.

IMN logo majestic logo threadwatch logo seochat tools logo