Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2002
    Posts
    8

    Unanswered: Update query with alias in mysql v4.0.13

    Hi,

    I'm trying to write MySQL query using one table and alias to itself. It would look like this :

    UPDATE
    t1, t1 AS a_t1
    SET
    t1.col1 = a_t1.col1
    WHERE
    t1.col2='B' AND a_t1.col2='A' AND t1.col3=a_t1.col3 AND t1.col4='C';

    There's no syntax error, mysql just freezes when I exec that query. why?

    Other multi-table updates, without aliases, work well in v4.0.13 - I practise it. Who knows what's wrong in this case?

  2. #2
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1

    Re: Update query with alias in mysql v4.0.13

    Originally posted by kristoph
    Hi,

    I'm trying to write MySQL query using one table and alias to itself. It would look like this :

    UPDATE
    t1, t1 AS a_t1
    SET
    t1.col1 = a_t1.col1
    WHERE
    t1.col2='B' AND a_t1.col2='A' AND t1.col3=a_t1.col3 AND t1.col4='C';

    There's no syntax error, mysql just freezes when I exec that query. why?

    Other multi-table updates, without aliases, work well in v4.0.13 - I practise it. Who knows what's wrong in this case?
    1-Can you don an EXPLAIN on this query?
    2-How many rows are in t1 ?
    3-Obviously you won't update anything since it looks quite impossible to satisfy:

    t1.col2='B' AND a_t1.col2='A'

  3. #3
    Join Date
    Nov 2002
    Posts
    8
    Hmm.. It's quite hard to explain...
    There are "sections" in t1 table identified by values of col2. Possible values of col2 are 'A', ... to 'F'. There's one more important column - col3. I need to find col3 values from section 'A' in section 'B' and update there col1 to be the same as in section 'A'. Additional condition is col4 - everything must happen where col4 has given value ('C' was only example, it's a date column in fact).
    I know that my example query looks impossible to satisfy - that's why I'm looking for a right way
    There's 36778 records in table. It's gonna grow up soon... rapidly ...
    I make temporary table to get it work but I would like to use alias because I feel it "pure". Maby I'm wrong and creating temp table is the best way?
    If you want I can even ilustrate it on some picture ...

    kris

  4. #4
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1
    Originally posted by kristoph
    Hmm.. It's quite hard to explain...
    There are "sections" in t1 table identified by values of col2. Possible values of col2 are 'A', ... to 'F'. There's one more important column - col3. I need to find col3 values from section 'A' in section 'B' and update there col1 to be the same as in section 'A'. Additional condition is col4 - everything must happen where col4 has given value ('C' was only example, it's a date column in fact).
    I know that my example query looks impossible to satisfy - that's why I'm looking for a right way
    There's 36778 records in table. It's gonna grow up soon... rapidly ...
    I make temporary table to get it work but I would like to use alias because I feel it "pure". Maby I'm wrong and creating temp table is the best way?
    If you want I can even ilustrate it on some picture ...

    kris
    Can you provide the DESCRIBE of the tables involved and some test data to illustrate the results you expect?

  5. #5
    Join Date
    Nov 2002
    Posts
    8
    +---------------+----------+------+-----+---------+----------------+
    | Field | Type | Null | Key | Default | Extra |
    +---------------+----------+------+-----+---------+----------------+
    | id | int(11) | | PRI | NULL | auto_increment |
    | data | date | YES | | NULL | |
    | sx | char(1) | YES | | NULL | |
    | kod_reg | char(3) | YES | | NULL | |
    | kod_num | char(4) | YES | | NULL | |
    | taryfa | char(30) | YES | | NULL | |
    | kod_aktywacji | char(30) | YES | | NULL | |
    | nr_telefonu | char(9) | YES | | NULL | |
    | data_x | date | YES | | NULL | |
    | nr_konta | char(12) | YES | | NULL | |
    | kasa | float | YES | | NULL | |
    | kasa_skor | float | YES | | NULL | |
    | cent_premi | float | YES | | NULL | |
    +---------------+----------+------+-----+---------+----------------+

    There's only one table.
    Real table name is 'prowizje'.
    Let it be like this :

    sx value can be 'A' or 'F' (it's to identify something like event type witch is stored in each record, there's few other types, for example 'P' or 'O' but i don't need to use them in my query)

    1. I want to use only that records witch have data_x = '2003-04-01'
    2. I have to find all nr_telefonu form part of table where sx='F' in part of table where sx='A' (nr_telefonu is not unique).
    3. Then I have to update kasa_skor field from records where sx='A' to have the same value as in section 'F' (equality of nr_telefonu is a condition).

    I hope this explanation has made things clear. If not - I'll still believe that I can make them clearer.

    Greetings from Poland,
    Kris

Posting Permissions

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