Re: [mysql] record swapping

#1

"Pawe³ Ga³ecki" <pmg3NOSPAM (AT) NOSPAM (DOT) op.pl> wrote

Quote:

How can I swap two records in a table without using a temporary record to
store the content of one of them?
Let's say I have:

Index|some string value
-----+-----------------------
1| sample string
2|another sample string

and I want to have

Index|some string value
-----+-----------------------
1|another sample string
2| sample string

Any ideas?

Update Table
set Index = 3 - Index
where Index in ( 1, 2 )
;

#2

"Bob Badour" <bbadour (AT) golden (DOT) net> writes:

Quote:

Update Table
set Index = 3 - Index
where Index in ( 1, 2 )

Hmm...you noticed [mysql] in the subject line and knew you wouldn't have
to worry about ON UPDATE CASCADE. Is this cheating? :-)

#3

"Bruce Lewis" <brlspam (AT) yahoo (DOT) com> wrote

Quote:

"Bob Badour" <bbadour (AT) golden (DOT) net> writes:

Update Table
set Index = 3 - Index
where Index in ( 1, 2 )

Hmm...you noticed [mysql] in the subject line and knew you wouldn't have
to worry about ON UPDATE CASCADE. Is this cheating? :-)

What does ON UPDATE CASCADE have to do with a single table update statement?

Unless one applies the closed world assumption, anything is possible.

#4

This works in Standard SQL:

UPDATE MyTable
SET a = b, b = a;

SQL is a set-oriented so the assignments are done all at once.

#5

"Bob Badour" <bbadour (AT) golden (DOT) net> writes:

Quote:

What does ON UPDATE CASCADE have to do with a single table update statement?

If another table references this single table with ON UPDATE CASCADE,
swapping primary key values will have different semantics than swapping
other values. I think it's the semantics of swapping other values that
the original poster wanted.

Quote:

unless one applies the closed world assumption, anything is possible.

Yes, but in an an open world where anything is possible, it's still best
to follow the principle of least surprise.

#6

Bruce,

"Bruce Lewis" <brlspam (AT) yahoo (DOT) com> kirjoitti viestissä
news:nm94qtmeufc.fsf (AT) biohazard-cafe (DOT) mit.edu...

Quote:

"Bob Badour" <bbadour (AT) golden (DOT) net> writes:

Update Table
set Index = 3 - Index
where Index in ( 1, 2 )

Hmm...you noticed [mysql] in the subject line and knew you wouldn't have
to worry about ON UPDATE CASCADE. Is this cheating? :-)

InnoDB tables in MySQL support ON UPDATE CASCADE.

Best regards,

Heikki
Innobase Oy
http://www.innodb.com
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: [mysql] record swapping

comp.databases comp.databases

Re: [mysql] record swapping - 02-19-2004 , 10:16 AM

Re: [mysql] record swapping - 02-19-2004 , 01:33 PM

Re: [mysql] record swapping - 02-19-2004 , 01:39 PM

Re: [mysql] record swapping - 02-19-2004 , 08:06 PM

Re: [mysql] record swapping - 02-20-2004 , 08:24 AM

Re: [mysql] record swapping - 02-20-2004 , 08:54 AM