Swap data in columns

Swap data in columns

am 22.09.2010 18:29:30 von nixofortune

--0016e65a07105c4ae70490dba641
Content-Type: text/plain; charset=ISO-8859-1

Hi all.

Sorry for very simple question, just can't figure out the solution.
I need to swap data in column1 with data in column2.


+----+---------+---------+
| id | column1 | column2 |
+----+---------+---------+
| 1 | a | z |
| 2 | b | y |
| 3 | c | x |
| 4 | d | w |
| 5 | e | v |
+----+---------+---------+

Can you achieve this with a simple query?
so for id 1 column1 = 'z' and column2 = 'a' and so on.

Thanks guys,
Igor

--0016e65a07105c4ae70490dba641--

RE: Swap data in columns

am 22.09.2010 18:53:01 von Rolando Edwards

I ran these commands:

use test
DROP TABLE IF EXISTS mydata;
CREATE TABLE mydata (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,column1 CHA=
R(1),column2 CHAR(2));
INSERT INTO mydata (column1,column2) VALUES ('a','z'), ('b','y'), ('c','x')=
, ('d','w'), ('e','v');
SELECT * FROM mydata;
UPDATE mydata A LEFT JOIN mydata B USING (id) SET A.column1=3DB.column2,A.c=
olumn2=3DB.column1;
SELECT * FROM mydata;

I got this output:

lwdba@ (DB test) :: use test
Database changed
lwdba@ (DB test) :: DROP TABLE IF EXISTS mydata;
Query OK, 0 rows affected (0.00 sec)

lwdba@ (DB test) :: CREATE TABLE mydata (id INT NOT NULL AUTO_INCREMENT PRI=
MARY KEY,column1 CHAR(1),column2 CHAR(2));
Query OK, 0 rows affected (0.05 sec)

lwdba@ (DB test) :: INSERT INTO mydata (column1,column2) VALUES ('a','z'), =
('b','y'), ('c','x'), ('d','w'), ('e','v');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0

lwdba@ (DB test) :: SELECT * FROM mydata;
+----+---------+---------+
| id | column1 | column2 |
+----+---------+---------+
| 1 | a | z |
| 2 | b | y |
| 3 | c | x |
| 4 | d | w |
| 5 | e | v |
+----+---------+---------+
5 rows in set (0.00 sec)

lwdba@ (DB test) :: UPDATE mydata A LEFT JOIN mydata B USING (id) SET A.col=
umn1=3DB.column2,A.column2=3DB.column1;
Query OK, 5 rows affected (0.03 sec)
Rows matched: 5 Changed: 5 Warnings: 0

lwdba@ (DB test) :: SELECT * FROM mydata;
+----+---------+---------+
| id | column1 | column2 |
+----+---------+---------+
| 1 | z | a |
| 2 | y | b |
| 3 | x | c |
| 4 | w | d |
| 5 | v | e |
+----+---------+---------+
5 rows in set (0.00 sec)

GIVE IT A TRY !!!

Rolando A. Edwards
MySQL DBA (CMDBA)

155 Avenue of the Americas, Fifth Floor
New York, NY 10013
212-625-5307 (Work)
AIM & Skype : RolandoLogicWorx
redwards@logicworks.net
http://www.linkedin.com/in/rolandoedwards


-----Original Message-----
From: nixofortune [mailto:nixofortune@googlemail.com]=20
Sent: Wednesday, September 22, 2010 12:30 PM
To: mysql@lists.mysql.com
Subject: Swap data in columns

Hi all.

Sorry for very simple question, just can't figure out the solution.
I need to swap data in column1 with data in column2.


+----+---------+---------+
| id | column1 | column2 |
+----+---------+---------+
| 1 | a | z |
| 2 | b | y |
| 3 | c | x |
| 4 | d | w |
| 5 | e | v |
+----+---------+---------+

Can you achieve this with a simple query?
so for id 1 column1 =3D 'z' and column2 =3D 'a' and so on.

Thanks guys,
Igor

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Swap data in columns

am 22.09.2010 20:06:21 von Johnny Withers

--001485f7c4dabf62c50490dd00ba
Content-Type: text/plain; charset=ISO-8859-1

Couldn't you just rename the columns?

JW


On Wed, Sep 22, 2010 at 11:53 AM, Rolando Edwards
wrote:

> I ran these commands:
>
> use test
> DROP TABLE IF EXISTS mydata;
> CREATE TABLE mydata (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,column1
> CHAR(1),column2 CHAR(2));
> INSERT INTO mydata (column1,column2) VALUES ('a','z'), ('b','y'),
> ('c','x'), ('d','w'), ('e','v');
> SELECT * FROM mydata;
> UPDATE mydata A LEFT JOIN mydata B USING (id) SET
> A.column1=B.column2,A.column2=B.column1;
> SELECT * FROM mydata;
>
> I got this output:
>
> lwdba@ (DB test) :: use test
> Database changed
> lwdba@ (DB test) :: DROP TABLE IF EXISTS mydata;
> Query OK, 0 rows affected (0.00 sec)
>
> lwdba@ (DB test) :: CREATE TABLE mydata (id INT NOT NULL AUTO_INCREMENT
> PRIMARY KEY,column1 CHAR(1),column2 CHAR(2));
> Query OK, 0 rows affected (0.05 sec)
>
> lwdba@ (DB test) :: INSERT INTO mydata (column1,column2) VALUES ('a','z'),
> ('b','y'), ('c','x'), ('d','w'), ('e','v');
> Query OK, 5 rows affected (0.00 sec)
> Records: 5 Duplicates: 0 Warnings: 0
>
> lwdba@ (DB test) :: SELECT * FROM mydata;
> +----+---------+---------+
> | id | column1 | column2 |
> +----+---------+---------+
> | 1 | a | z |
> | 2 | b | y |
> | 3 | c | x |
> | 4 | d | w |
> | 5 | e | v |
> +----+---------+---------+
> 5 rows in set (0.00 sec)
>
> lwdba@ (DB test) :: UPDATE mydata A LEFT JOIN mydata B USING (id) SET
> A.column1=B.column2,A.column2=B.column1;
> Query OK, 5 rows affected (0.03 sec)
> Rows matched: 5 Changed: 5 Warnings: 0
>
> lwdba@ (DB test) :: SELECT * FROM mydata;
> +----+---------+---------+
> | id | column1 | column2 |
> +----+---------+---------+
> | 1 | z | a |
> | 2 | y | b |
> | 3 | x | c |
> | 4 | w | d |
> | 5 | v | e |
> +----+---------+---------+
> 5 rows in set (0.00 sec)
>
> GIVE IT A TRY !!!
>
> Rolando A. Edwards
> MySQL DBA (CMDBA)
>
> 155 Avenue of the Americas, Fifth Floor
> New York, NY 10013
> 212-625-5307 (Work)
> AIM & Skype : RolandoLogicWorx
> redwards@logicworks.net
> http://www.linkedin.com/in/rolandoedwards
>
>
> -----Original Message-----
> From: nixofortune [mailto:nixofortune@googlemail.com]
> Sent: Wednesday, September 22, 2010 12:30 PM
> To: mysql@lists.mysql.com
> Subject: Swap data in columns
>
> Hi all.
>
> Sorry for very simple question, just can't figure out the solution.
> I need to swap data in column1 with data in column2.
>
>
> +----+---------+---------+
> | id | column1 | column2 |
> +----+---------+---------+
> | 1 | a | z |
> | 2 | b | y |
> | 3 | c | x |
> | 4 | d | w |
> | 5 | e | v |
> +----+---------+---------+
>
> Can you achieve this with a simple query?
> so for id 1 column1 = 'z' and column2 = 'a' and so on.
>
> Thanks guys,
> Igor
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=johnny@pixelated.net
>
>


--
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net

--001485f7c4dabf62c50490dd00ba--

Re: Swap data in columns

am 22.09.2010 21:27:41 von Steven Staples

What about:
select `id`, `column1` as 'column2', `column2` as 'column1';

Steve



On Wed, 2010-09-22 at 13:06 -0500, Johnny Withers wrote:
> Couldn't you just rename the columns?
>
> JW
>
>
> On Wed, Sep 22, 2010 at 11:53 AM, Rolando Edwards
> wrote:
>
> > I ran these commands:
> >
> > use test
> > DROP TABLE IF EXISTS mydata;
> > CREATE TABLE mydata (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,column1
> > CHAR(1),column2 CHAR(2));
> > INSERT INTO mydata (column1,column2) VALUES ('a','z'), ('b','y'),
> > ('c','x'), ('d','w'), ('e','v');
> > SELECT * FROM mydata;
> > UPDATE mydata A LEFT JOIN mydata B USING (id) SET
> > A.column1=B.column2,A.column2=B.column1;
> > SELECT * FROM mydata;
> >
> > I got this output:
> >
> > lwdba@ (DB test) :: use test
> > Database changed
> > lwdba@ (DB test) :: DROP TABLE IF EXISTS mydata;
> > Query OK, 0 rows affected (0.00 sec)
> >
> > lwdba@ (DB test) :: CREATE TABLE mydata (id INT NOT NULL AUTO_INCREMENT
> > PRIMARY KEY,column1 CHAR(1),column2 CHAR(2));
> > Query OK, 0 rows affected (0.05 sec)
> >
> > lwdba@ (DB test) :: INSERT INTO mydata (column1,column2) VALUES ('a','z'),
> > ('b','y'), ('c','x'), ('d','w'), ('e','v');
> > Query OK, 5 rows affected (0.00 sec)
> > Records: 5 Duplicates: 0 Warnings: 0
> >
> > lwdba@ (DB test) :: SELECT * FROM mydata;
> > +----+---------+---------+
> > | id | column1 | column2 |
> > +----+---------+---------+
> > | 1 | a | z |
> > | 2 | b | y |
> > | 3 | c | x |
> > | 4 | d | w |
> > | 5 | e | v |
> > +----+---------+---------+
> > 5 rows in set (0.00 sec)
> >
> > lwdba@ (DB test) :: UPDATE mydata A LEFT JOIN mydata B USING (id) SET
> > A.column1=B.column2,A.column2=B.column1;
> > Query OK, 5 rows affected (0.03 sec)
> > Rows matched: 5 Changed: 5 Warnings: 0
> >
> > lwdba@ (DB test) :: SELECT * FROM mydata;
> > +----+---------+---------+
> > | id | column1 | column2 |
> > +----+---------+---------+
> > | 1 | z | a |
> > | 2 | y | b |
> > | 3 | x | c |
> > | 4 | w | d |
> > | 5 | v | e |
> > +----+---------+---------+
> > 5 rows in set (0.00 sec)
> >
> > GIVE IT A TRY !!!
> >
> > Rolando A. Edwards
> > MySQL DBA (CMDBA)
> >
> > 155 Avenue of the Americas, Fifth Floor
> > New York, NY 10013
> > 212-625-5307 (Work)
> > AIM & Skype : RolandoLogicWorx
> > redwards@logicworks.net
> > http://www.linkedin.com/in/rolandoedwards
> >
> >
> > -----Original Message-----
> > From: nixofortune [mailto:nixofortune@googlemail.com]
> > Sent: Wednesday, September 22, 2010 12:30 PM
> > To: mysql@lists.mysql.com
> > Subject: Swap data in columns
> >
> > Hi all.
> >
> > Sorry for very simple question, just can't figure out the solution.
> > I need to swap data in column1 with data in column2.
> >
> >
> > +----+---------+---------+
> > | id | column1 | column2 |
> > +----+---------+---------+
> > | 1 | a | z |
> > | 2 | b | y |
> > | 3 | c | x |
> > | 4 | d | w |
> > | 5 | e | v |
> > +----+---------+---------+
> >
> > Can you achieve this with a simple query?
> > so for id 1 column1 = 'z' and column2 = 'a' and so on.
> >
> > Thanks guys,
> > Igor
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe: http://lists.mysql.com/mysql?unsub=johnny@pixelated.net
> >
> >
>
>



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Swap data in columns

am 22.09.2010 22:10:32 von nixofortune

--001485f6cc32d81ccf0490debc3d
Content-Type: text/plain; charset=ISO-8859-1

Hi Rolando,
This is perfect solution I was looking for.
Why do you use left join here? It looks like inner join works fine as well.
Thanks.



>
>
>
>
>
> Rolando Edwards wrote:
>
> I ran these commands:
>
> use test
> DROP TABLE IF EXISTS mydata;
> CREATE TABLE mydata (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,column1 CHAR(1),column2 CHAR(2));
> INSERT INTO mydata (column1,column2) VALUES ('a','z'), ('b','y'), ('c','x'), ('d','w'), ('e','v');
> SELECT * FROM mydata;
> UPDATE mydata A LEFT JOIN mydata B USING (id) SET A.column1=B.column2,A.column2=B.column1;
> SELECT * FROM mydata;
>
> I got this output:
>
> lwdba@ (DB test) :: use test
> Database changed
> lwdba@ (DB test) :: DROP TABLE IF EXISTS mydata;
> Query OK, 0 rows affected (0.00 sec)
>
> lwdba@ (DB test) :: CREATE TABLE mydata (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,column1 CHAR(1),column2 CHAR(2));
> Query OK, 0 rows affected (0.05 sec)
>
> lwdba@ (DB test) :: INSERT INTO mydata (column1,column2) VALUES ('a','z'), ('b','y'), ('c','x'), ('d','w'), ('e','v');
> Query OK, 5 rows affected (0.00 sec)
> Records: 5 Duplicates: 0 Warnings: 0
>
> lwdba@ (DB test) :: SELECT * FROM mydata;
> +----+---------+---------+
> | id | column1 | column2 |
> +----+---------+---------+
> | 1 | a | z |
> | 2 | b | y |
> | 3 | c | x |
> | 4 | d | w |
> | 5 | e | v |
> +----+---------+---------+
> 5 rows in set (0.00 sec)
>
> lwdba@ (DB test) :: UPDATE mydata A LEFT JOIN mydata B USING (id) SET A.column1=B.column2,A.column2=B.column1;
> Query OK, 5 rows affected (0.03 sec)
> Rows matched: 5 Changed: 5 Warnings: 0
>
> lwdba@ (DB test) :: SELECT * FROM mydata;
> +----+---------+---------+
> | id | column1 | column2 |
> +----+---------+---------+
> | 1 | z | a |
> | 2 | y | b |
> | 3 | x | c |
> | 4 | w | d |
> | 5 | v | e |
> +----+---------+---------+
> 5 rows in set (0.00 sec)
>
> GIVE IT A TRY !!!
>
> Rolando A. Edwards
> MySQL DBA (CMDBA)
>
> 155 Avenue of the Americas, Fifth Floor
> New York, NY 10013
> 212-625-5307 (Work)
> AIM & Skype : RolandoLogicWorxredwards@logicworks.nethttp://www.linkedin.c om/in/rolandoedwards
>
>
> -----Original Message-----
> From: nixofortune [mailto:nixofortune@googlemail.com ]
> Sent: Wednesday, September 22, 2010 12:30 PM
> To: mysql@lists.mysql.com
> Subject: Swap data in columns
>
> Hi all.
>
> Sorry for very simple question, just can't figure out the solution.
> I need to swap data in column1 with data in column2.
>
>
> +----+---------+---------+
> | id | column1 | column2 |
> +----+---------+---------+
> | 1 | a | z |
> | 2 | b | y |
> | 3 | c | x |
> | 4 | d | w |
> | 5 | e | v |
> +----+---------+---------+
>
> Can you achieve this with a simple query?
> so for id 1 column1 = 'z' and column2 = 'a' and so on.
>
> Thanks guys,
> Igor
>
>
>
>

--001485f6cc32d81ccf0490debc3d--

Re: Swap data in columns

am 22.09.2010 22:13:33 von nixofortune

Hi Rolando,
This is perfect solution I was looking for.
Why do you use left join here? It looks like inner join works fine as well.
Thanks.


Rolando Edwards wrote:
> I ran these commands:
>
> use test
> DROP TABLE IF EXISTS mydata;
> CREATE TABLE mydata (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,column1 CHAR(1),column2 CHAR(2));
> INSERT INTO mydata (column1,column2) VALUES ('a','z'), ('b','y'), ('c','x'), ('d','w'), ('e','v');
> SELECT * FROM mydata;
> UPDATE mydata A LEFT JOIN mydata B USING (id) SET A.column1=B.column2,A.column2=B.column1;
> SELECT * FROM mydata;
>
> I got this output:
>
> lwdba@ (DB test) :: use test
> Database changed
> lwdba@ (DB test) :: DROP TABLE IF EXISTS mydata;
> Query OK, 0 rows affected (0.00 sec)
>
> lwdba@ (DB test) :: CREATE TABLE mydata (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,column1 CHAR(1),column2 CHAR(2));
> Query OK, 0 rows affected (0.05 sec)
>
> lwdba@ (DB test) :: INSERT INTO mydata (column1,column2) VALUES ('a','z'), ('b','y'), ('c','x'), ('d','w'), ('e','v');
> Query OK, 5 rows affected (0.00 sec)
> Records: 5 Duplicates: 0 Warnings: 0
>
> lwdba@ (DB test) :: SELECT * FROM mydata;
> +----+---------+---------+
> | id | column1 | column2 |
> +----+---------+---------+
> | 1 | a | z |
> | 2 | b | y |
> | 3 | c | x |
> | 4 | d | w |
> | 5 | e | v |
> +----+---------+---------+
> 5 rows in set (0.00 sec)
>
> lwdba@ (DB test) :: UPDATE mydata A LEFT JOIN mydata B USING (id) SET A.column1=B.column2,A.column2=B.column1;
> Query OK, 5 rows affected (0.03 sec)
> Rows matched: 5 Changed: 5 Warnings: 0
>
> lwdba@ (DB test) :: SELECT * FROM mydata;
> +----+---------+---------+
> | id | column1 | column2 |
> +----+---------+---------+
> | 1 | z | a |
> | 2 | y | b |
> | 3 | x | c |
> | 4 | w | d |
> | 5 | v | e |
> +----+---------+---------+
> 5 rows in set (0.00 sec)
>
> GIVE IT A TRY !!!
>
> Rolando A. Edwards
> MySQL DBA (CMDBA)
>
> 155 Avenue of the Americas, Fifth Floor
> New York, NY 10013
> 212-625-5307 (Work)
> AIM & Skype : RolandoLogicWorx
> redwards@logicworks.net
> http://www.linkedin.com/in/rolandoedwards
>
>
> -----Original Message-----
> From: nixofortune [mailto:nixofortune@googlemail.com]
> Sent: Wednesday, September 22, 2010 12:30 PM
> To: mysql@lists.mysql.com
> Subject: Swap data in columns
>
> Hi all.
>
> Sorry for very simple question, just can't figure out the solution.
> I need to swap data in column1 with data in column2.
>
>
> +----+---------+---------+
> | id | column1 | column2 |
> +----+---------+---------+
> | 1 | a | z |
> | 2 | b | y |
> | 3 | c | x |
> | 4 | d | w |
> | 5 | e | v |
> +----+---------+---------+
>
> Can you achieve this with a simple query?
> so for id 1 column1 = 'z' and column2 = 'a' and so on.
>
> Thanks guys,
> Igor
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Swap data in columns

am 22.09.2010 22:23:16 von Johnny Withers

--001485f7d53067134a0490deead7
Content-Type: text/plain; charset=ISO-8859-1

This is even better!

JW


On Wed, Sep 22, 2010 at 2:27 PM, Steve Staples wrote:

> What about:
> select `id`, `column1` as 'column2', `column2` as 'column1';
>
> Steve
>
>
>
> On Wed, 2010-09-22 at 13:06 -0500, Johnny Withers wrote:
> > Couldn't you just rename the columns?
> >
> > JW
> >
> >
> > On Wed, Sep 22, 2010 at 11:53 AM, Rolando Edwards
> > wrote:
> >
> > > I ran these commands:
> > >
> > > use test
> > > DROP TABLE IF EXISTS mydata;
> > > CREATE TABLE mydata (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,column1
> > > CHAR(1),column2 CHAR(2));
> > > INSERT INTO mydata (column1,column2) VALUES ('a','z'), ('b','y'),
> > > ('c','x'), ('d','w'), ('e','v');
> > > SELECT * FROM mydata;
> > > UPDATE mydata A LEFT JOIN mydata B USING (id) SET
> > > A.column1=B.column2,A.column2=B.column1;
> > > SELECT * FROM mydata;
> > >
> > > I got this output:
> > >
> > > lwdba@ (DB test) :: use test
> > > Database changed
> > > lwdba@ (DB test) :: DROP TABLE IF EXISTS mydata;
> > > Query OK, 0 rows affected (0.00 sec)
> > >
> > > lwdba@ (DB test) :: CREATE TABLE mydata (id INT NOT NULL
> AUTO_INCREMENT
> > > PRIMARY KEY,column1 CHAR(1),column2 CHAR(2));
> > > Query OK, 0 rows affected (0.05 sec)
> > >
> > > lwdba@ (DB test) :: INSERT INTO mydata (column1,column2) VALUES
> ('a','z'),
> > > ('b','y'), ('c','x'), ('d','w'), ('e','v');
> > > Query OK, 5 rows affected (0.00 sec)
> > > Records: 5 Duplicates: 0 Warnings: 0
> > >
> > > lwdba@ (DB test) :: SELECT * FROM mydata;
> > > +----+---------+---------+
> > > | id | column1 | column2 |
> > > +----+---------+---------+
> > > | 1 | a | z |
> > > | 2 | b | y |
> > > | 3 | c | x |
> > > | 4 | d | w |
> > > | 5 | e | v |
> > > +----+---------+---------+
> > > 5 rows in set (0.00 sec)
> > >
> > > lwdba@ (DB test) :: UPDATE mydata A LEFT JOIN mydata B USING (id) SET
> > > A.column1=B.column2,A.column2=B.column1;
> > > Query OK, 5 rows affected (0.03 sec)
> > > Rows matched: 5 Changed: 5 Warnings: 0
> > >
> > > lwdba@ (DB test) :: SELECT * FROM mydata;
> > > +----+---------+---------+
> > > | id | column1 | column2 |
> > > +----+---------+---------+
> > > | 1 | z | a |
> > > | 2 | y | b |
> > > | 3 | x | c |
> > > | 4 | w | d |
> > > | 5 | v | e |
> > > +----+---------+---------+
> > > 5 rows in set (0.00 sec)
> > >
> > > GIVE IT A TRY !!!
> > >
> > > Rolando A. Edwards
> > > MySQL DBA (CMDBA)
> > >
> > > 155 Avenue of the Americas, Fifth Floor
> > > New York, NY 10013
> > > 212-625-5307 (Work)
> > > AIM & Skype : RolandoLogicWorx
> > > redwards@logicworks.net
> > > http://www.linkedin.com/in/rolandoedwards
> > >
> > >
> > > -----Original Message-----
> > > From: nixofortune [mailto:nixofortune@googlemail.com]
> > > Sent: Wednesday, September 22, 2010 12:30 PM
> > > To: mysql@lists.mysql.com
> > > Subject: Swap data in columns
> > >
> > > Hi all.
> > >
> > > Sorry for very simple question, just can't figure out the solution.
> > > I need to swap data in column1 with data in column2.
> > >
> > >
> > > +----+---------+---------+
> > > | id | column1 | column2 |
> > > +----+---------+---------+
> > > | 1 | a | z |
> > > | 2 | b | y |
> > > | 3 | c | x |
> > > | 4 | d | w |
> > > | 5 | e | v |
> > > +----+---------+---------+
> > >
> > > Can you achieve this with a simple query?
> > > so for id 1 column1 = 'z' and column2 = 'a' and so on.
> > >
> > > Thanks guys,
> > > Igor
> > >
> > > --
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe:
> http://lists.mysql.com/mysql?unsub=johnny@pixelated.net
> > >
> > >
> >
> >
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=johnny@pixelated.net
>
>


--
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net

--001485f7d53067134a0490deead7--

RE: Swap data in columns

am 22.09.2010 22:54:03 von Rolando Edwards

Oh yea, INNER JOIN is cleaner to use

Rolando A. Edwards
MySQL DBA (CMDBA)

155 Avenue of the Americas, Fifth Floor
New York, NY 10013
212-625-5307 (Work)
201-660-3221 (Cell)
AIM & Skype : RolandoLogicWorx
redwards@logicworks.net
http://www.linkedin.com/in/rolandoedwards


-----Original Message-----
From: Egor Shevtsov [mailto:nixofortune@googlemail.com]=20
Sent: Wednesday, September 22, 2010 4:14 PM
To: MySQL mailing list
Subject: Re: Swap data in columns

Hi Rolando,
This is perfect solution I was looking for.
Why do you use left join here? It looks like inner join works fine as well.
Thanks.


Rolando Edwards wrote:
> I ran these commands:
>
> use test
> DROP TABLE IF EXISTS mydata;
> CREATE TABLE mydata (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,column1 C=
HAR(1),column2 CHAR(2));
> INSERT INTO mydata (column1,column2) VALUES ('a','z'), ('b','y'), ('c','x=
'), ('d','w'), ('e','v');
> SELECT * FROM mydata;
> UPDATE mydata A LEFT JOIN mydata B USING (id) SET A.column1=3DB.column2,A=
..column2=3DB.column1;
> SELECT * FROM mydata;
>
> I got this output:
>
> lwdba@ (DB test) :: use test
> Database changed
> lwdba@ (DB test) :: DROP TABLE IF EXISTS mydata;
> Query OK, 0 rows affected (0.00 sec)
>
> lwdba@ (DB test) :: CREATE TABLE mydata (id INT NOT NULL AUTO_INCREMENT P=
RIMARY KEY,column1 CHAR(1),column2 CHAR(2));
> Query OK, 0 rows affected (0.05 sec)
>
> lwdba@ (DB test) :: INSERT INTO mydata (column1,column2) VALUES ('a','z')=
, ('b','y'), ('c','x'), ('d','w'), ('e','v');
> Query OK, 5 rows affected (0.00 sec)
> Records: 5 Duplicates: 0 Warnings: 0
>
> lwdba@ (DB test) :: SELECT * FROM mydata;
> +----+---------+---------+
> | id | column1 | column2 |
> +----+---------+---------+
> | 1 | a | z |
> | 2 | b | y |
> | 3 | c | x |
> | 4 | d | w |
> | 5 | e | v |
> +----+---------+---------+
> 5 rows in set (0.00 sec)
>
> lwdba@ (DB test) :: UPDATE mydata A LEFT JOIN mydata B USING (id) SET A.c=
olumn1=3DB.column2,A.column2=3DB.column1;
> Query OK, 5 rows affected (0.03 sec)
> Rows matched: 5 Changed: 5 Warnings: 0
>
> lwdba@ (DB test) :: SELECT * FROM mydata;
> +----+---------+---------+
> | id | column1 | column2 |
> +----+---------+---------+
> | 1 | z | a |
> | 2 | y | b |
> | 3 | x | c |
> | 4 | w | d |
> | 5 | v | e |
> +----+---------+---------+
> 5 rows in set (0.00 sec)
>
> GIVE IT A TRY !!!
>
> Rolando A. Edwards
> MySQL DBA (CMDBA)
>
> 155 Avenue of the Americas, Fifth Floor
> New York, NY 10013
> 212-625-5307 (Work)
> AIM & Skype : RolandoLogicWorx
> redwards@logicworks.net
> http://www.linkedin.com/in/rolandoedwards
>
>
> -----Original Message-----
> From: nixofortune [mailto:nixofortune@googlemail.com]=20
> Sent: Wednesday, September 22, 2010 12:30 PM
> To: mysql@lists.mysql.com
> Subject: Swap data in columns
>
> Hi all.
>
> Sorry for very simple question, just can't figure out the solution.
> I need to swap data in column1 with data in column2.
>
>
> +----+---------+---------+
> | id | column1 | column2 |
> +----+---------+---------+
> | 1 | a | z |
> | 2 | b | y |
> | 3 | c | x |
> | 4 | d | w |
> | 5 | e | v |
> +----+---------+---------+
>
> Can you achieve this with a simple query?
> so for id 1 column1 =3D 'z' and column2 =3D 'a' and so on.
>
> Thanks guys,
> Igor
>
> =20

--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dredwards@logicworks=
..net


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Swap data in columns

am 23.09.2010 01:03:04 von kfoneill56

update mydata set column1 = column2, column2 = column1
(works in sqlserver, can't try mysql at the moment)
You can select which rows by adding a where clause obviously.
I suppose that the field values are copied to a buffer which is the written
to the table at the end of the update (or row by row?)

----- Original Message -----
From: "nixofortune"
To:
Sent: Wednesday, September 22, 2010 5:29 PM
Subject: Swap data in columns


> Hi all.
>
> Sorry for very simple question, just can't figure out the solution.
> I need to swap data in column1 with data in column2.
>
>
> +----+---------+---------+
> | id | column1 | column2 |
> +----+---------+---------+
> | 1 | a | z |
> | 2 | b | y |
> | 3 | c | x |
> | 4 | d | w |
> | 5 | e | v |
> +----+---------+---------+
>
> Can you achieve this with a simple query?
> so for id 1 column1 = 'z' and column2 = 'a' and so on.
>
> Thanks guys,
> Igor
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Swap data in columns

am 23.09.2010 09:46:21 von Jangita

ALTER TABLE tablename1 CHANGE column1 column2 VARCHAR(64), CHANGE column2
column1 VARCHAR(64)

Assuming the columns are varchar(64)


Jangita | +254 76 918383 | MSN & Y!: jangita@yahoo.com
Skype: jangita | GTalk: jangita.nyagudi@gmail.com




-----Original Message-----
From: nixofortune [mailto:nixofortune@googlemail.com]
Sent: 22 September 2010 6:30 PM
To: mysql@lists.mysql.com
Subject: Swap data in columns

Hi all.

Sorry for very simple question, just can't figure out the solution.
I need to swap data in column1 with data in column2.


+----+---------+---------+
| id | column1 | column2 |
+----+---------+---------+
| 1 | a | z |
| 2 | b | y |
| 3 | c | x |
| 4 | d | w |
| 5 | e | v |
+----+---------+---------+

Can you achieve this with a simple query?
so for id 1 column1 = 'z' and column2 = 'a' and so on.

Thanks guys,
Igor


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Swap data in columns

am 23.09.2010 11:09:06 von nixofortune

--002215b02d1e44a54f0490e99d69
Content-Type: text/plain; charset=ISO-8859-1

Hi Kevin,
It works in mysql but not exactly as I need.
In my case it copied content of column2 into column1.
So, not exactly what I intended to achieve.
Thanks.
Igor


update mydata set column1 = column2, column2 = column1

On Thu, Sep 23, 2010 at 12:03 AM, Kevin (Gmail) wrote:

> update mydata set column1 = column2, column2 = column1
> (works in sqlserver, can't try mysql at the moment)
> You can select which rows by adding a where clause obviously.
> I suppose that the field values are copied to a buffer which is the written
> to the table at the end of the update (or row by row?)
>
> ----- Original Message ----- From: "nixofortune" <
> nixofortune@googlemail.com>
>
> To:
> Sent: Wednesday, September 22, 2010 5:29 PM
>
> Subject: Swap data in columns
>
>
> Hi all.
>>
>> Sorry for very simple question, just can't figure out the solution.
>> I need to swap data in column1 with data in column2.
>>
>>
>> +----+---------+---------+
>> | id | column1 | column2 |
>> +----+---------+---------+
>> | 1 | a | z |
>> | 2 | b | y |
>> | 3 | c | x |
>> | 4 | d | w |
>> | 5 | e | v |
>> +----+---------+---------+
>>
>> Can you achieve this with a simple query?
>> so for id 1 column1 = 'z' and column2 = 'a' and so on.
>>
>> Thanks guys,
>> Igor
>>
>>
>

--002215b02d1e44a54f0490e99d69--