At 08:23 PM 7/20/2008, Perrin Harkins wrote:
On Sun, Jul 20, 2008 at 12:12 AM, mos [EMAIL PROTECTED] wrote:
Is there a way to get Insert ... select ... On Duplicate Update to
update
the row with the duplicate key?
That's what it does.
Why can't it do this?
What makes you think it can't?
On Mon, Jul 21, 2008 at 11:44 AM, mos [EMAIL PROTECTED] wrote:
I can't specify all of the columns in a Set statement in the
OnDuplicate clause because I don't know what the column names are and there
could be 100 columns.
Write code to do it. There is no way around specifying the
At 11:00 AM 7/21/2008, Perrin Harkins wrote:
On Mon, Jul 21, 2008 at 11:44 AM, mos [EMAIL PROTECTED] wrote:
I can't specify all of the columns in a Set statement in the
OnDuplicate clause because I don't know what the column names are and there
could be 100 columns.
Write code to do
So just use REPLACE instead of INSERT...
http://dev.mysql.com/doc/refman/5.0/en/replace.html
On Mon, Jul 21, 2008 at 11:44 AM, mos [EMAIL PROTECTED] wrote:
At 08:23 PM 7/20/2008, Perrin Harkins wrote:
On Sun, Jul 20, 2008 at 12:12 AM, mos [EMAIL PROTECTED] wrote:
Is there a way to get
At 12:16 PM 7/21/2008, you wrote:
So just use REPLACE instead of INSERT...
Sure, but a Replace will delete the existing row and insert the new one
which means also maintaining the indexes. This will take much longer than
just updating the existing row. Now if there were only a couple of
On Mon, Jul 21, 2008 at 2:43 PM, mos [EMAIL PROTECTED] wrote:
I thought if MySQL found a duplicate key on the insert, it would
automatically update the existing row that it found with the results from
table1 if I left out the column expressions in the update clause. But
apparently it doesn't
On Sun, Jul 20, 2008 at 12:12 AM, mos [EMAIL PROTECTED] wrote:
Is there a way to get Insert ... select ... On Duplicate Update to update
the row with the duplicate key?
That's what it does.
Why can't it do this?
What makes you think it can't?
- Perrin
--
MySQL General Mailing List
For
Is there a way to get Insert ... select ... On Duplicate Update to
update the row with the duplicate key? Otherwise I'll have to use Replace
which is inefficient because it deletes the old duplicated row and then
inserts the new row with the same key. I'd much rather have it update the
Folks,
Thanks for the 'help'. Oy.
I figured it out from some help on the Lasso discussion list.
All I had to do was properly address ALL the tables I wanted to touch.
So this:
UPDATE tbe_gallery
SET tbe_gsa.gsa_paperprice = tbe_gallery.gallery_gsaprice_paper
WHERE tbe_gallery.gallery_id =
http://dev.mysql.com/doc/refman/5.0/en/update.html
See multiple table syntax. I have had issues with the syntax (IMHO),
and is not available on 3.23 (I am a poor soul that still has to deal
3.23).
If you have all the data and you can not figure out the syntax you can
alternatively use INSERT...
Hi,
I'm wondering if the following can be done
UPDATE tbe_gallery
SET tbe_gsa.gsa_paperprice = tbe_gallery.gallery_gsaprice_paper
WHERE tbe_gallery.gallery_id = tbe_images.img_rel_id AND
tbe_images.img_orig_filename = tbe_gsa.gsa_id
Let me explain:
I have 3 tables and only 1 of them
On Wednesday 25 April 2007 23:14, you wrote:
try this:
update table1, table2
set table1.value = table2.value
where table1.id = table2.id
Thanks for the replies... It was late evening when I tried to figure out how
to do this.
Today I found the answer myself, which is exactly as described
Please, I nedd help!!
I have two tabels:
table1:
id
value
table2:
id
value
Both tables has a lot of records with identical IDs. I need to update the
table1.value with the table2.value where the id are identical.
But I cannot find any UPDATE query that can do
On Wed, April 25, 2007 23:10, Jørn Dahl-Stamnes wrote:
Please, I nedd help!!
I have two tabels:
table1:
id
value
table2:
id
value
Both tables has a lot of records with identical IDs. I need to update the
table1.value with the table2.value where the id are
[EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Saturday, January 06, 2007 4:10 AM
Subject: On Duplicate Key Update question
I use On Duplicate Key Update a lot and I usually use it like this,
Insert Into tablename (myID, Qty)
Values (1034,15),
(6,13),
(5001,1),
(145,20) On Duplicate Key
I use On Duplicate Key Update a lot and I usually use it like this,
Insert Into tablename (myID, Qty)
Values (1034,15),
(6,13),
(5001,1),
(145,20) On Duplicate Key Update Qty=Values(Qty);
This works very well but now I'd like to do something a little
different. I'd like to have a query like this
Sorry for the premature question. I think I figured it out.
On Duplicate Key Update Qty=Qty+Values(Qty);
I haven't tested it yet but it makes sense that it'll work.
Ed Reed [EMAIL PROTECTED] 1/5/07 2:40 PM
I use On Duplicate Key Update a lot and I usually use it like this,
Insert Into
Ed Reed wrote:
I use On Duplicate Key Update a lot and I usually use it like this,
Insert Into tablename (myID, Qty)
Values (1034,15),
(6,13),
(5001,1),
(145,20) On Duplicate Key Update Qty=Values(Qty);
This works very well but now I'd like to do something a little
different. I'd like to have
I have a field representing the chances the user has to get a
password right, which is initially 3. I would like, if possible in a
single query, to be able to decrement it if it's still 0 and return
the value. Something like this:
UPDATE table
SET chances = IF(chances 0, chances - 1, 0)
Chris, I'm not aware of a way to use ordinary SQL (insert, update)
for this, but the use of a stored procedure would work for you. I've
not done it with MySQL (never had a need) but did things like this
extensively with Sybase.
In rough terms:
CREATE PROCEDURE sp_chances(IN xxx INT) RETURNS
At 8:49 -0500 9/8/06, Dan Buettner wrote:
Chris, I'm not aware of a way to use ordinary SQL (insert, update)
for this, but the use of a stored procedure would work for you. I've
not done it with MySQL (never had a need) but did things like this
extensively with Sybase.
In rough terms:
CREATE
Agreed, in your case it may be 6 of one, half a dozen of the other.
If you were calling a stored procedure to autheticate someone, as in
CALL autheticate(username, password)
then you could conceivably later alter your entire authentication
database model without ever having to touch your
If I have 4 Fields (FIELD1, FIELD2, FIELD3 FIELD4)
I can do this easily;
UPDATE TABLE_NAME SET FIELD4 = FIELD1;
But -- how do I do it so that FIELD4 = FIELD1 FIELD2 ??? I can't seem
to find any examples online. Maybe it's just too early in the morning -
I'm drawing a blank! ;)
Shawn,
Perhaps :
UPDATE TABLE_NAME SET FIELD4 = concat(FIELD1,FIELD2);
-Original Message-
From: Cummings, Shawn (GNAPs) [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 03, 2006 9:33 AM
To: Mysql General (E-mail)
Subject: UPDATE question
If I have 4 Fields (FIELD1, FIELD2, FIELD3
Cummings, Shawn (GNAPs) schrieb:
If I have 4 Fields (FIELD1, FIELD2, FIELD3 FIELD4)
I can do this easily;
UPDATE TABLE_NAME SET FIELD4 = FIELD1;
But -- how do I do it so that FIELD4 = FIELD1 FIELD2 ??? I can't seem
to find any examples online. Maybe it's just too early in the
On 5/3/06, Barry [EMAIL PROTECTED] wrote:
Cummings, Shawn (GNAPs) schrieb:
If I have 4 Fields (FIELD1, FIELD2, FIELD3 FIELD4)
I can do this easily;
UPDATE TABLE_NAME SET FIELD4 = FIELD1;
But -- how do I do it so that FIELD4 = FIELD1 FIELD2 ??? I can't seem
to find any examples
Cummings, Shawn (GNAPs) wrote:
If I have 4 Fields (FIELD1, FIELD2, FIELD3 FIELD4)
I can do this easily;
UPDATE TABLE_NAME SET FIELD4 = FIELD1;
But -- how do I do it so that FIELD4 = FIELD1 FIELD2 ??? I can't
seem to find any examples online. Maybe it's just too early in the
morning
.
-Original Message-
From: 2wsxdr5 [mailto:[EMAIL PROTECTED]
Sent: Friday, January 06, 2006 8:09 PM
To: mysql@lists.mysql.com
Subject: [SPAM] - concat string and update question - Found word(s)
remove list in the Text body
I have a table of people and their phone numbers, some have the area
I know this is possible but I'm not real sure of the command to use. I
have 2 tables that are pretty much identical except for one column. What I
want to do is moved data from one table column to the other table column
based on a matching id number that is also a column in both tables called
id.
Here's my table definition:
CREATE TABLE `files` (
`id` int(11) NOT NULL auto_increment,
`checksum` char(32) NOT NULL default '',
`size` bigint(20) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `checksum` (`checksum`(8))
This table is used to store information on files. When I delete a file,
At 18:41 -0400 2/28/04, Juan E Suris wrote:
Here's my table definition:
CREATE TABLE `files` (
`id` int(11) NOT NULL auto_increment,
`checksum` char(32) NOT NULL default '',
`size` bigint(20) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `checksum` (`checksum`(8))
This table is used to store
I'm not quite sure why I haven't run across this in the past, but now that I
have I am stumped. I am needing to update a table based on criteria found in
it and one other table, but I am uncertain how to proceed. If I had
subselects I would run the query as follows, I believe:
UPDATE
At 5:14 PM -0500 9/18/03, [EMAIL PROTECTED] wrote:
I'm not quite sure why I haven't run across this in the past, but now that I
have I am stumped. I am needing to update a table based on criteria found in
it and one other table, but I am uncertain how to proceed. If I had
subselects I would run
character.
I should look for a better MySQL book, the ones I have don't cover that
info. Any suggestions for one that does?
--
Chip
HTH,
Tore.
- Original Message -
From: chip wiegand [EMAIL PROTECTED]
To: mysql [EMAIL PROTECTED]
Sent: Sunday, March 02, 2003 1:46 AM
Subject: update
wiegand [EMAIL PROTECTED]
To: Tore Bostrup [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Sunday, March 02, 2003 4:25 PM
Subject: Re: update question
On Sun, 2 Mar 2003 02:06:40 -0500
Tore Bostrup [EMAIL PROTECTED] wrote:
I assume you are storing your dates in a char/varchar column
PROTECTED]
To: Tore Bostrup [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Sunday, March 02, 2003 4:25 PM
Subject: Re: update question
On Sun, 2 Mar 2003 02:06:40 -0500
Tore Bostrup [EMAIL PROTECTED] wrote:
I assume you are storing your dates in a char/varchar column - not
a good
I need to make a change to a field in a table and don't know how to
write the correct sql statement. I made an error and now have a date
field with the year repeated twice - 01-01-03-03 - there are
aproximately 100 rows like this, and maybe 20 or so that are formatted
properly. How can I remove
mytable
SET mydatestr = LEFT(mydatestr, LENGTH(mydatestr) - 3)
WHERE mydatestr LIKE '__-__-__-__'
HTH,
Tore.
- Original Message -
From: chip wiegand [EMAIL PROTECTED]
To: mysql [EMAIL PROTECTED]
Sent: Sunday, March 02, 2003 1:46 AM
Subject: update question
I need to make a change to a field
hi. i'm new to the list and have only been playing with mySQL for a few
weeks now, i have a question regrading the syntax of an UPDATE statement - i
hope nobody minds me asking.
i want to uopdate a table with the data from another, i've written the
following:
update trackinfo SET
[snip]
update trackinfo SET trackinfo.postcode = newtrackinfo.postcode FROM
trackinfo, newtrackinfo WHERE trackinfo.telephone = newtrackinfo.telephone;
[/snip]
http://www.mysql.com/doc/U/P/UPDATE.html
MySQL does not support sub-queries, such as the one you are attempting here.
(the FROM on..)
There is no from clause in the update syntax. However, I'm not sure how
you would update a tablethe way you are trying to. I'm sure it can be done, and your
SQL looks
correct besides the fromclause. Just take that out and see if what it says.
-Nick
hi. i'm new to the list and
have only been
cheers for the responses. i'll have to find another way, but thanks.
.b
-Original Message-
From: Nick Stuart [mailto:[EMAIL PROTECTED]]
Sent: 20 May 2002 13:46
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: UPDATE question, SQL syntax, etc.
There is no from clause
problem is every time i
delete one question for example delete question 5, i
have to update all the question numbers after question
5 in this question set.(for example if there is 10
question in this set, i delete question 5 i have to
update question numbers for question 6,7 8 9,10 to
5,6,7,8,9
Hello, verybody,
I am afraid to send my question again.
i created a table in mysql database system to store
exam questions which has the following column fields:
questionnumber(integer),questionbody(String), answers
(String) and etc.
My problem is if i delete one row(question record) how
can
You shouldn't be storing the questio number at all. You have denormalised
your database and this is why you are experiencing this problem.
Instead, store a unique identifier with each one (an 'id' field,
autonumbering) and then, when you pull back all the questions, order it by
this ID
On Fri, 2002-04-12 at 13:52, Christopher Thompson wrote:
You shouldn't be storing the questio number at all. You have denormalised
your database and this is why you are experiencing this problem.
Maybe I don't understand bin's problem, but storing the question #
wouldn't seem to be
UPDATE returns the number of rows that were actually changed.
How is the returned number of rows formatted for SQLFetch/SQLGet?
Thanks
Jeff Tanner
Seattle, WA
-
Before posting, please check:
http://www.mysql.com/manual.php
How can i do an update a column in a
table width the fields of another table
i would like to do the folowing query:
UPDATE enc_oper set estado = "N",
quant_prod = 1 * opera.quantidade whe
re encomenda = 20010004580 and posicao =
1 and opera.codigo = enc_prod.opera;
But i dont
For example, I have a table
value position
3001
1002
5003
2004
I have to update position ordered by value, and table must look like this
value position
3003
1001
5004
2002
Is it possible
Hi.
On Thu, Jan 18, 2001 at 05:39:25PM -0500, [EMAIL PROTECTED] wrote:
How can I do the following:
update location set location.city_id = location_city.city_id where
location.city = location_city.name;
I want to update one field in a table with values from another table. Is
How can I do the following:
update location set location.city_id = location_city.city_id where
location.city = location_city.name;
I want to update one field in a table with values from another table. Is
this possible within MySQL or do I need to write some code to do this.
Any help would be
Hi.
On Thu, Jan 18, 2001 at 05:39:25PM -0500, [EMAIL PROTECTED] wrote:
How can I do the following:
update location set location.city_id = location_city.city_id where
location.city = location_city.name;
I want to update one field in a table with values from another table. Is
this
52 matches
Mail list logo
