My Insert statement looks like

INSERT INTO tableName Column1Name, Column2Name VALUES ('c1', 'c2');

Shouldn't the UPDATE statement look very similar?

UPDATE TableName Column1Name, Column2Name VALUES ('c1', 'c2') WHERE
something=something;

It appears that the update has a different structure or I've messed up
somewhere else...

UPDATE TableName Column1Name=`c1`, Column2Name=`c2` WHERE
something=something;
Is what the tutorial says, but shouldn't my first UPDATE statement
work as well?

On Wed, 16 Mar 2011 09:32:42 -0700, SpreadTooThin wrote:

No, Update takes a different syntax.

UPDATE TableName SET Column1='c1', Column2='C2' WHERE key=someValue;

See http://dev.mysql.com/doc/refman/5.0/en/update.html

On Mar 16, 4:32*pm, SpreadTooThin <bjobrie... (AT) gmail (DOT) com> wrote:
No. Everything is not the same as everything else.

As it happens, MySQL supports a non-standard INSERT ... ON DUPLICATE
KEY UPDATE ... type statement "REPLACE INTO...". I say "type
statement" because its effect is not always the same as an equivalent
INSERT ... ON DUPLICATE KEY UPDATE ...

It also supports a non standard INSERT syntax using SET (so that
INSERT looks more like an UPDATE statement as opposed to vice versa).

Of course all of this is in the manual so I'm not sure why you asked
here in the first place.

On Mar 16, 11:14*am, Captain Paralytic <paul_laut... (AT) yahoo (DOT) com> wrote:
If a table has two columns that must be unique, how do you know which
of the two columns is the one that is not unique during the UPDATE/
INSERT?

On Mar 16, 1:49*pm, SpreadTooThin <bjobrie... (AT) gmail (DOT) com> wrote:
You are asking the wrong question here... The answer is that the
COMBINATION in this column order is NOT unique. Therefore, if they
should EACH need to be unique, then you need 2 unique indexes. One
for columnA and one for columnB. If the two together must be unique
and you are finding too many cases where they are not unique, then you
may not have chosen this key wisely or may need to add a third column
for absolute uniqueness.

So, let's look at your case. Let's say the columns are
(firstname,lastname)

Data:
Joe, Blow
Jane, Blow
Joe, Blue
Jane, Blue

now you try to insert Joe, Blow again... which firstname OR which
lastname is not unique and causing your problem? You already have 2
firstname "Joe"s and 2 lastname "Blow"s -- which one caused you to
"fail"??? The answer would be "Joe, Blow" because the unique
constraint is firstname+lastname.

Take this to your test server and add a "failed_insert" table that has
no unique constraints and add any row that failed the initial insert
into this table. Then review the data to see where/why you are having
the problem. Only you know your data - or at least you should know
your data...

On Mar 16, 5:49*pm, SpreadTooThin <bjobrie... (AT) gmail (DOT) com> wrote:
You don't need to know. The concept is that if any of your unique keys
match then you will wish to perform an update (or a replace).

SpreadTooThin <bjobrien62 (AT) gmail (DOT) com> wrote:
What do you mean?

1. That the combination of the columns must be unique?
2. Or that each column must be unique?

In any case your INSERT or UPDATE will fail with a "duplicate entry for
key ..." error. And in the second case the message details will tell
you which key (= UNIQUE constraint) was violated.

For the first case your question makes no sense. Changing the value for
either column will resolve the duplicate problem.


XL

On 3/16/2011 9:32 AM, SpreadTooThin wrote:
No, but the REPLACE statement does. Check that out.

John Nagle

On Mar 23, 7:27*am, John Nagle <na... (AT) animats (DOT) com> wrote:
Yes but as I mentioned in my post a week ago, the effects are
different (between REPLACE and UPDATE/INSERT...UPDATE).