Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2003
    Posts
    69

    Unanswered: Simple MySql question: replace value in field by value in other field for all records

    Hi, I have this Opencart DB with products.

    TABLE: 'oc_product_description'
    FIELDS
    product_id
    language_id
    name
    description
    ....

    So basically same product appears twice in the database once with language_id=0 and other with language_id=1. Now, i have a tool to import products but this only works for 1 language so description is inserted in language 0 but remains NULL in language=1;

    Now I want to copy all descriptions from language=0 and paste it to language=1 (maybe if language 1 is NULL) Otherwise i have to copy it manually for all the products.

    Something like:

    for product_id(i=0; i<NrOfLines; i++)
    {
    copy value from product_id(i) AND language_id=0 TO product_id(i) AND language_id=1;
    }


    OR

    LOOP
    {
    product_id(i)&language_id=1 REPLACE product_id(i)&language_id=0;
    i++
    }

    Can you guys help me with the correct syntax please?

    EDIT:

    added Screenshot of mysql
    also it's language 1 and 2 instead of 0 and 1
    Attached Thumbnails Attached Thumbnails Screenshot 2013-10-28 16.30.11 copy.png  
    Last edited by west; 10-28-13 at 12:44.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about this?

    Code:
    UPDATE oc_product_description t
     SET   (name , description)
         = (SELECT name , description
             FROM  oc_product_description s
             WHERE s.product_id  = t.product_id
               AND s.language_id = 0
           )
     WHERE language_id = 1
    ;

  3. #3
    Join Date
    Sep 2003
    Posts
    69
    Hi, thank you very much for your help.

    Before trying it I want to make sure I understand what it does to limit the risk of screwing up my DB

    I am a little confused about the 't' and the 's'. Are those like variables?

    does T stands for all id's with language 0 and s for all id's with language=1?

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I found an issue similar to yours in
    MySQL :: MySQL 5.6 Reference Manual :: 13.2.11 UPDATE Syntax
    under the title
    Posted by Rafi B. on April 26 2006 6:44pm

    Referencing the issue, MySQL syntax might be like
    Code:
    UPDATE oc_product_description AS target
         , oc_product_description AS source
     SET   target.name        = source.name
         , target.description = source.description
     WHERE target.language_id = 1
       AND source.language_id = 0
       AND source.product_id  = target.product_id
    ;


    By the way,
    MySQL's syntax were sometimes not compliant to SQL standard.

    For example, the sample code got syntax errors on Mimer SQL Developers - Mimer SQL-2003 Validator
    Mimer SQL-2003 Validator

    Check your SQL against the SQL-2003 standard using the Mimer SQL Validator 2003.

    The SQL-2003 Validator!

    Enter your SQL statement(s) in the box below and simply click the "Test SQL" button:

    Code:
    UPDATE oc_product_description AS target
         , oc_product_description AS source
     SET   target.name        = source.name
         , target.description = source.description
     WHERE target.language_id = 1
       AND source.language_id = 0
       AND source.product_id  = target.product_id
    ;

    See Validator Examples

    Code:
    Result:
    
    UPDATE oc_product_description AS target 
         , oc_product_description AS source 
         ^                        ^-
    syntax error: ,
      correction: SET <identifier> = <identifier> ,
    syntax error: AS source
      correction: = source
    
     SET   target.name        = source.name 
     ^--
    syntax error: SET
      correction: (
    
         , target.description = source.description 
     WHERE target.language_id = 1 
     ^----
    syntax error: WHERE
      correction: ) WHERE
    
       AND source.language_id = 0 
       AND source.product_id  = target.product_id 
    ;

  5. #5
    Join Date
    Sep 2003
    Posts
    69
    Code:
    UPDATE oc_product_description AS target
         , oc_product_description AS source
     SET   target.name        = source.name
         , target.description = source.description
     WHERE target.language_id = 2
       AND source.language_id = 1
       AND source.product_id  = target.product_id
    ;



    Thanks a lot! This worked!

    I added an extra criteria so it only overrides the name for the fields where there is no description (name=NULL)

    Code:
    UPDATE oc_product_description AS target
         , oc_product_description AS source
     SET   target.name        = source.name
         , target.description = source.description
     WHERE target.language_id = 2
       AND source.language_id = 1
       AND source.product_id  = target.product_id
       AND target.name = NULL
    ;
    Last edited by west; 10-28-13 at 13:38.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by west View Post
    I will now try to add an extra criteria so it only overrides for the fields where there is no description (description=NULL)
    When comparing against NULL values you cannot use the = operator. You must use IS NULL:
    Code:
    WHERE description IS NULL
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  7. #7
    Join Date
    Sep 2003
    Posts
    69
    oh thanks, changed it! I really appreciate your help.

    Also a little strange he accepted = NULL

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    There is a good(I think) article about NULL and equal predicate in here
    MySQL :: MySQL 5.6 Reference Manual :: 12.3.2 Comparison Functions and Operators

    like
    <=>
    NULL-safe equal. This operator performs an equality comparison like the = operator,
    but returns 1 rather than NULL if both operands are NULL, and 0 rather than NULL if one operand is NULL.

    Code:
    mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
            -> 1, 1, 0
    mysql> SELECT 1 = 1, NULL = NULL, 1 = NULL;
            -> 1, NULL, NULL
    So,
    "accepted = NULL" must be a correct syntax,
    but it might return a different result than you expected.

  9. #9
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by tonkuma View Post
    [...]
    By the way,
    MySQL's syntax were sometimes not compliant to SQL standard.

    For example, the sample code got syntax errors on Mimer SQL Developers - Mimer SQL-2003 Validator

    There are some limitations on how you can update a table in mysql:

    See: MySQL :: MySQL 5.6 Reference Manual :: 13.2.11 UPDATE Syntax

    "Currently, you cannot update a table and select from the same table in a subquery."

    I guess this is why this special construction with update of a join is invented. There are also other peculiar constructions on the same page:

    UPDATE t SET id = id + 1 ORDER BY id DESC;

    Which has to be used if id is unique. Example:

    create table t (x int not null primary key) engine=innodb
    insert into t (x) values (1),(2);
    update t set x = x+1;
    ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
    update t set x = x+1 order by x desc;
    Query OK, 2 rows affected (0.01 sec)
    Rows matched: 2 Changed: 2 Warnings: 0

    All and all there are quite a few gotchas for anyone coming from another dbms.
    --
    Lennart

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •