Professional Web Applications Themes

how to swap two fields bertween two rows - MySQL

I have a table in which a field (ordine) is used for the orderer output of data. This is not an index, since data are subdivided in different categories and therefore the value is not unique for the whole table. Sometimes I need to change the display order and I was used to use a query like this one: UPDATE globalmoneta as mon1,globalmoneta as mon2 SET mon1.ordine="2",mon2.ordine="3" WHERE mon1.ordine="3" AND mon2.ordine="2" AND mon1.parentid="W-CE3" AND mon2.parentid="W-CE3"; probably not very efficient, but acceptable since this operation is not often performed. Unfortunately, after a server upgrade this query is no more working. I ...

  1. #1

    Default how to swap two fields bertween two rows

    I have a table in which a field (ordine) is used for the orderer
    output of data.

    This is not an index, since data are subdivided in different
    categories and therefore the value is not unique for the whole table.

    Sometimes I need to change the display order and I was used to use a
    query like this one:

    UPDATE globalmoneta as mon1,globalmoneta as mon2 SET
    mon1.ordine="2",mon2.ordine="3" WHERE mon1.ordine="3" AND
    mon2.ordine="2" AND mon1.parentid="W-CE3" AND mon2.parentid="W-CE3";

    probably not very efficient, but acceptable since this operation is
    not often performed.

    Unfortunately, after a server upgrade this query is no more working. I
    suspect this is due to a different mysql version but I can not check
    it.

    Any idea on how to obtain the same task with a different query?

    Thanks,
    Massimo

    incuso Guest

  2. #2

    Default how to swap two fields bertween two rows

    I have a table in which a field (ordine) is used for the orderer
    output of data.

    This is not an index, since data are subdivided in different
    categories and therefore the value is not unique for the whole table.

    Sometimes I need to change the display order and I was used to use a
    query like this one:

    UPDATE globalmoneta as mon1,globalmoneta as mon2 SET
    mon1.ordine="2",mon2.ordine="3" WHERE mon1.ordine="3" AND
    mon2.ordine="2" AND mon1.parentid="W-CE3" AND mon2.parentid="W-CE3";

    probably not very efficient, but acceptable since this operation is
    not often performed.

    Unfortunately, after a server upgrade this query is no more working. I
    suspect this is due to a different mysql version but I can not check
    it.

    Any idea on how to obtain the same task with a different query?

    Thanks,
    Massimo

    incuso Guest

  3. #3

    Default Re: how to swap two fields bertween two rows

    incuso wrote: 

    Try bracketing the tables this:
    UPDATE (globalmoneta as mon1,globalmoneta as mon2) SET
    mon1.ordine="2",mon2.ordine="3" WHERE mon1.ordine="3" AND
    mon2.ordine="2" AND mon1.parentid="W-CE3" AND mon2.parentid="W-CE3";


    Paul Guest

  4. #4

    Default Re: how to swap two fields bertween two rows

    On 4 Feb, 16:49, "Paul Lautman" <com> wrote: 

    Same result...

    The query doeas not give any error. Simply only mon1.ordine is
    changed...

    incuso Guest

Similar Threads

  1. Replies: 7
    Last Post: September 2nd, 05:44 AM
  2. Replies: 3
    Last Post: September 20th, 12:38 PM
  3. Generating a total based upon multiple fields frommultiple rows
    By RayBan12 in forum Macromedia ColdFusion
    Replies: 3
    Last Post: August 9th, 06:40 PM
  4. Swap image also swap link
    By Timothy Robertson in forum Dreamweaver AppDev
    Replies: 2
    Last Post: May 26th, 05:29 PM
  5. Select rows where other related rows don't exist
    By Dmand in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 2nd, 06:23 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139