Re: Update to Percona CVE-2016-6662 Vulnerability Communication

[Reindl Harald]

Am 16.09.2016 um 00:21 schrieb Johan De Meersman:

- Original Message -

From: "Reindl Harald" 
Sent: Friday, 16 September, 2016 00:12:26

frankly - mysqld_safe needs to go away and life is beautiful without for
years here and yes taht worked for mysql too before switch to MariaDB

to say it clear: running *any* code as root for a service binding to a
port above 1024 is idiotic by definition


I agree, and I'm happy to say that the release notes for 8.0 mention that it 
has finally gone away. That doesn't change the fact, however, that there are 
countless installations out there that use it, which is whom I was targeting 
with that mail :-)

Is that service description you pasted the one that comes with the MariaDB 
package, or did you roll it yourself ?


my own one shipped by our internal rpm package

used it more or less identical from 2011 where Fedora switched to 
systemd, some security features got added over time (my side as well as 
implementation in systemd)




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Update to Percona CVE-2016-6662 Vulnerability Communication

[Johan De Meersman]

- Original Message -
> From: "Reindl Harald" 
> Sent: Friday, 16 September, 2016 00:12:26
> 
> frankly - mysqld_safe needs to go away and life is beautiful without for
> years here and yes taht worked for mysql too before switch to MariaDB
> 
> to say it clear: running *any* code as root for a service binding to a
> port above 1024 is idiotic by definition

I agree, and I'm happy to say that the release notes for 8.0 mention that it 
has finally gone away. That doesn't change the fact, however, that there are 
countless installations out there that use it, which is whom I was targeting 
with that mail :-)

Is that service description you pasted the one that comes with the MariaDB 
package, or did you roll it yourself ?


-- 
Unhappiness is discouraged and will be corrected with kitten pictures.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Update to Percona CVE-2016-6662 Vulnerability Communication

[Reindl Harald]

Am 16.09.2016 um 00:05 schrieb Johan De Meersman:

This is probably of interest to many of you, and I've not seen it on the list 
yet.

Kenny Gryp's blog about the vulnerability is at 
https://www.percona.com/blog/2016/09/12/database-affected-cve-2016-6662/ .

For those who use it, there's an ansible playbook to patch the workaround into 
mysqld_safe at https://github.com/meersjo/ansible-mysql-cve-2016-6662 .


frankly - mysqld_safe needs to go away and life is beautiful without for 
years here and yes taht worked for mysql too before switch to MariaDB


to say it clear: running *any* code as root for a service binding to a 
port above 1024 is idiotic by definition

___

[root@srv-rhsoft:~]$ cat /usr/lib/systemd/system/mysqld.service
[Unit]
Description=MariaDB Database

[Service]
Type=simple
User=mysql
Group=mysql
ExecStart=/usr/libexec/mysqld --defaults-file=/etc/my.cnf 
--pid-file=/dev/null

ExecStartPost=/usr/libexec/mysqld-wait-ready $MAINPID
Environment="LANG=en_GB.UTF-8"
Restart=always
RestartSec=1
TimeoutSec=300
LimitNOFILE=infinity
LimitMEMLOCK=infinity
OOMScoreAdjust=-1000
TasksMax=2048

PrivateTmp=yes
PrivateDevices=yes
NoNewPrivileges=yes
CapabilityBoundingSet=~CAP_AUDIT_CONTROL CAP_AUDIT_WRITE 
CAP_DAC_OVERRIDE CAP_NET_ADMIN CAP_NET_BIND_SERVICE CAP_SETGID 
CAP_SETUID CAP_SYS_ADMIN CAP_SYS_BOOT CAP_SYS_MODULE CAP_SYS_PTRACE
SystemCallFilter=~acct modify_ldt add_key adjtimex clock_adjtime 
delete_module fanotify_init finit_module get_mempolicy init_module kcmp 
kexec_load keyctl lookup_dcookie mbind mount open_by_handle_at 
perf_event_open pivot_root process_vm_readv process_vm_writev ptrace 
request_key set_mempolicy swapoff swapon umount2 uselib vmsplice

RestrictAddressFamilies=AF_UNIX AF_LOCAL AF_INET AF_INET6
SystemCallArchitectures=x86-64

ReadOnlyDirectories=/etc
ReadOnlyDirectories=/usr
ReadOnlyDirectories=/var/lib
ReadWriteDirectories=/var/lib/mysql

InaccessibleDirectories=-/boot
InaccessibleDirectories=-/home
InaccessibleDirectories=-/root
InaccessibleDirectories=-/media
InaccessibleDirectories=-/run/user

[Install]
WantedBy=multi-user.target
___

[root@srv-rhsoft:~]$ cat /usr/libexec/mysqld-wait-ready
#!/usr/bin/bash

# Service file passes us the daemon's PID
daemon_pid="$1"

# Wait for the server to come up or for the mysqld process to disappear
ret=0
while /usr/bin/true; do
 RESPONSE=`/usr/bin/mysqladmin --defaults-file=/etc/my.cnf 
--socket=/var/lib/mysql/mysql.sock --user=UNKNOWN_MYSQL_USER ping 2>&1`

 mret=$?
 if [ $mret -eq 0 ]; then
  break
 fi
 # exit codes 1, 11 (EXIT_CANNOT_CONNECT_TO_SERVICE) are expected,
 # anything else suggests a configuration error
 if [ $mret -ne 1 -a $mret -ne 11 ]; then
  ret=1
  break
 fi
 # "Access denied" also means the server is alive
 echo "$RESPONSE" | grep -q "Access denied for user" && break

 # Check process still exists
 if ! /usr/bin/kill -0 $daemon_pid 2>/dev/null; then
  ret=1
  break
 fi
 usleep 10
done

exit $ret

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: update, insert ... gee.. a puzzle

[Hal.sz S.ndor]

2016/03/17 12:47 ... lejeczek:

..  that you experts I hope can crack like a digestive biscuit...

how does one update, or merge or whatever is right technical term for it
- a my.table from my.another table (both are schematically identical, no
foreign keys, one primary key) but..
does it a way so when there is a duplicate only NULLs in my.table get
updated/replaced with proper values from my.another table?

many thanks, specially for actual syntax hints.

Nothing here is easy

My best is INSERT ... SELECT ... ON DUPLICATE KEY UPDATE ...;

INSERT INTO my.table SELECT * FROM my."another table" ON DUPLICATE KEY 
UPDATE /* every single field "f" not in the key thus: */ f = 
IFNULL(my.table.f, my."another table".f);


By the way, there always is only one primary key. Maybe you mean that 
there is only one field to it?


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: update and control flow

[wagnerbianchi.com]

You can do that, but, perhaps the only chance to have it updating a row
based on a condition is developing a Stored Procedure or even having a
BEFORE Trigger associated with the main table. Those ways, you can test the
sent value and decide on what UPDATE you will execute afterwards. Consider
that this is just an opinion since I'm not part of the problem and cannot
analyse all the requirements.

WB

2014-12-09 12:25 GMT-02:00 Martin Mueller martinmuel...@northwestern.edu:

 I'm trying to get my feet wet with 'if' and 'when' uses in mysql. it would
 be very useful for update operations, but I can't get it right.

 If I read the documentation correctly, it should be possible to say
 something like

 UPDATE X

 if WORD like 'a%' SET COMMENT = 'a'
 elseif WORD like 'b%' SET COMMENT = 'b'
 END IF


 But this gives me an error message. What am I doing wrong?

 MM

 Martin Mueller

 Professor emeritus of English and Classics
 Northwestern University





 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql

Re: update and control flow

[Michael Dykman]

​You can use your login inline with nested IF expressions:

insert into foo(id,comment)
 values(17, IF(WORD like 'a%','a',IF(word like 'b%','b',null)));​



On Tue, Dec 9, 2014 at 9:50 AM, wagnerbianchi.com m...@wagnerbianchi.com
wrote:

 You can do that, but, perhaps the only chance to have it updating a row
 based on a condition is developing a Stored Procedure or even having a
 BEFORE Trigger associated with the main table. Those ways, you can test the
 sent value and decide on what UPDATE you will execute afterwards. Consider
 that this is just an opinion since I'm not part of the problem and cannot
 analyse all the requirements.

 WB

 2014-12-09 12:25 GMT-02:00 Martin Mueller martinmuel...@northwestern.edu
 :

  I'm trying to get my feet wet with 'if' and 'when' uses in mysql. it
 would
  be very useful for update operations, but I can't get it right.
 
  If I read the documentation correctly, it should be possible to say
  something like
 
  UPDATE X
 
  if WORD like 'a%' SET COMMENT = 'a'
  elseif WORD like 'b%' SET COMMENT = 'b'
  END IF
 
 
  But this gives me an error message. What am I doing wrong?
 
  MM
 
  Martin Mueller
 
  Professor emeritus of English and Classics
  Northwestern University
 
 
 
 
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:http://lists.mysql.com/mysql
 
 




-- 
 - michael dykman
 - mdyk...@gmail.com

 May the Source be with you.

Re: update and control flow

[shawn l.green]

Hello Martin,

On 12/9/2014 9:25 AM, Martin Mueller wrote:

I'm trying to get my feet wet with 'if' and 'when' uses in mysql. it would
be very useful for update operations, but I can't get it right.

If I read the documentation correctly, it should be possible to say
something like

UPDATE X

if WORD like 'a%' SET COMMENT = 'a'
elseif WORD like 'b%' SET COMMENT = 'b'
END IF


But this gives me an error message. What am I doing wrong?



The correct syntax is to put the function after the = sign. The column 
name must appear by itself on the left side of the equation. You must 
also use the function-format of IF or a CASE..END construction.


UPDATE X
SET COMMENT = IF(WORD like 'a%','a',if(WORD like 'b%', 'b',COMMENT))

UPDATE X
SET COMMENT = CASE
  WHEN WORD like 'a%' then 'a'
  WHEN WORD like 'b%' then 'b'
  ELSE COMMENT
  END

But, as you noted, it may be more efficient to simply run two UPDATE 
statements each with the appropriate WHERE clause to limit the changes 
to just those rows that match your conditions.


UPDATE X
SET COMMENT = 'a'
WHERE WORD like 'a%'

And you can combine both techniques to limit the scope of the UPDATE to 
just the rows to change by matching either pattern.


UPDATE X
SET COMMENT = IF(WORD like 'a%','a','b')
WHERE WORD like 'a%' or WORD like 'b%'

Note: this last format doesn't need the second if() in the 'else' 
portion of the first IF() function because the set of rows to be 
operated on is already limited by the WHERE clause. The rows will match 
one condition or the other but not neither.


Yours,
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: update and control flow

[hsv]

 2014/12/09 14:25 +, Martin Mueller 
I'm trying to get my feet wet with 'if' and 'when' uses in mysql. it would be 
very useful for update operations, but I can't get it right.

If I read the documentation correctly, it should be possible to say
something like 

UPDATE X

if WORD like 'a%' SET COMMENT = 'a'
elseif WORD like 'b%' SET COMMENT = 'b'
END IF


But this gives me an error message. What am I doing wrong?

You have seen Sean Green s good changes. You need to know where what form is 
allowed.
There is an IF statement which is allowed within stored procedures and 
triggers, but not in queries or open code.
There is also a function of the same name that takes three arguments: 
condition, TRUE choice, not-TRUE choice.
There are also CASE statement and CASE operator, whereto WHEN belongs. The 
statement and operator look not quite alike: the statement ends with END CASE, 
the operator with END, and the ELSE NULL allowed for the operator is not 
allowed for the statement.
CASE statement and operator, and IF statement, take THEN between the condition 
and the conclusion.

(If you want to try the statement forms, write a trigger or a stored procedure.)

The statement forms do not apply to your example. The function and operator 
forms that Sean Green used do, and also the conditions WHERE and HAVING, each 
with its own use.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Update Column in table only if variable is Not NULL

[Neil Tompkins]

Shawn


What I need is that if I pass say 10 parameters/variables to a query, I
only want to update the column/field if the value passed is NOT NULL.


On Wed, Oct 30, 2013 at 3:41 AM, Shawn Green shawn.l.gr...@oracle.comwrote:

 Hi,

 On 10/29/2013 9:52 PM, h...@tbbs.net wrote:

 2013/10/29 11:35 -0400, Shawn Green 

 My favorite technique is the COALESCE function for this on a
 column-by-column basis

 SET FieldName1 = Now(), FieldName2 = COALESCE(:MyVariable, FieldName2)
 
 but if MyVariable is NULL, FieldName1 reflects the attempt to change, not
 change.



 The way I understood the second explanation was like this.

 He wants to update a row of data. The FieldName1 field is always updated
 to the current date and time.  If any of the new values (passed in via
 variables) are not NULL for a specific column, replace the value on the row
 with the new value otherwise maintain the current value.

 He may yet mean something completely different than how I read it the
 second time.

 --
 Shawn Green
 MySQL Senior Principal Technical Support Engineer
 Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
 Office: Blountville, TN

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql

Re: Update Column in table only if variable is Not NULL

[Shawn Green]

Hi Neil,

On 10/30/2013 9:55 AM, Neil Tompkins wrote:

Shawn


What I need is that if I pass say 10 parameters/variables to a query, I
only want to update the column/field if the value passed is NOT NULL.


On Wed, Oct 30, 2013 at 3:41 AM, Shawn Green shawn.l.gr...@oracle.comwrote:


Hi,

On 10/29/2013 9:52 PM, h...@tbbs.net wrote:


2013/10/29 11:35 -0400, Shawn Green 



My favorite technique is the COALESCE function for this on a

column-by-column basis

SET FieldName1 = Now(), FieldName2 = COALESCE(:MyVariable, FieldName2)

but if MyVariable is NULL, FieldName1 reflects the attempt to change, not
change.




The way I understood the second explanation was like this.

He wants to update a row of data. The FieldName1 field is always updated
to the current date and time.  If any of the new values (passed in via
variables) are not NULL for a specific column, replace the value on the row
with the new value otherwise maintain the current value.

He may yet mean something completely different than how I read it the
second time.



Then we agree on your intentions.  The COALESCE() construction I 
demonstrated will work just fine for you.


--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Update Column in table only if variable is Not NULL

[Michael Dykman]

Consider:
update table1 set field1 = if( :var,:var,field1), ...

Can be in a procedure but doesn't have to be.
On Oct 28, 2013 5:28 PM, Neil Tompkins neil.tompk...@googlemail.com
wrote:

 Hi Shawn

 Thanks for your reply.  Maybe my example wasn't detailed enough.  Basically
 the snippet of the UPDATE statement I provided shows updating only 1 field.
  However in my live working example, I have about 20 possible fields that
 might need to be updated if the variable passed for each field is NOT
 NULL.

 Therefore, I felt this needs to be done at database level in the stored
 procedure.  How can I accomplish this.

 Thanks
 Neil


 On Mon, Oct 28, 2013 at 6:17 PM, Shawn Green shawn.l.gr...@oracle.com
 wrote:

  Hello Neil,
 
  On 10/28/2013 2:06 PM, Neil Tompkins wrote:
 
  Hi
 
  If I have a update statement like
 
  UPDATE MY_TABLE
  SET FieldName1 = Now(), FieldName2 = :MyVariable
  WHERE FieldName3 = 'Y'
 
  How can I only update the FieldName2 field if the value of MyVariable is
  NOT NULL ?
 
  Thanks
  Neil
 
 
  This needs to be a decision you make at the application level to not
  execute the UPDATE command in the first place. Not every decision needs
 to
  be made by the database. Plus, it will save you the time of a full
 network
  round trip just to get a result from the server that you affected 0 rows
  (parsing, optimizing, executing).
 
  Now, if this was just a typo and your :MyVariable was meant to be
  @MyVariable (a MySQL user variable) then you can put that test in the
 WHERE
  clause of the command
 
  UPDATE MY_TABLE
  SET FieldName1 = Now(), FieldName2 = @MyVariable
  WHERE FieldName3 = 'Y'  AND @MyVariable IS NOT NULL
 
  --
  Shawn Green
  MySQL Senior Principal Technical Support Engineer
  Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
  Office: Blountville, TN
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:http://lists.mysql.com/mysql
 
 

Re: Update Column in table only if variable is Not NULL

[Shawn Green]

Hello Neil,

On 10/28/2013 5:23 PM, Neil Tompkins wrote:

Hi Shawn

Thanks for your reply.  Maybe my example wasn't detailed enough.
  Basically the snippet of the UPDATE statement I provided shows
updating only 1 field.  However in my live working example, I have about
20 possible fields that might need to be updated if the variable
passed for each field is NOT NULL.

Therefore, I felt this needs to be done at database level in the stored
procedure.  How can I accomplish this.

Thanks
Neil


On Mon, Oct 28, 2013 at 6:17 PM, Shawn Green shawn.l.gr...@oracle.com
mailto:shawn.l.gr...@oracle.com wrote:

Hello Neil,

On 10/28/2013 2:06 PM, Neil Tompkins wrote:

Hi

If I have a update statement like

UPDATE MY_TABLE
SET FieldName1 = Now(), FieldName2 = :MyVariable
WHERE FieldName3 = 'Y'

How can I only update the FieldName2 field if the value of
MyVariable is
NOT NULL ?

Thanks
Neil


This needs to be a decision you make at the application level to not
execute the UPDATE command in the first place. Not every decision
needs to be made by the database. Plus, it will save you the time of
a full network round trip just to get a result from the server that
you affected 0 rows (parsing, optimizing, executing).

Now, if this was just a typo and your :MyVariable was meant to be
@MyVariable (a MySQL user variable) then you can put that test in
the WHERE clause of the command

UPDATE MY_TABLE
SET FieldName1 = Now(), FieldName2 = @MyVariable
WHERE FieldName3 = 'Y'  AND @MyVariable IS NOT NULL

--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql



My favorite technique is the COALESCE function for this on a 
column-by-column basis


SET FieldName1 = Now(), FieldName2 = COALESCE(:MyVariable, FieldName2)

--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Update Column in table only if variable is Not NULL

[hsv]

 2013/10/28 21:23 +, Neil Tompkins 
Basically the snippet of the UPDATE statement I provided shows updating only 1 
field.
However in my live working example, I have about 20 possible fields that 
might need to be updated if the variable passed for each field is NOT NULL. 

Well, maybe something as loathsome as this:

UPDATE T SET F1 = NOW(), F2 = IFNULL(@F2, F2), FF3 = IFNULL(@FF3, FF3), FF4 = 
IFNULL(@FF4, FF4), F5 = IFNULL(@F5, F5), 

but if all are NULL F1 will be misleading. I can think only of
NOT (@F2 IS NULL AND @FF3 IS NULL AND @FF4 IS NULL AND @F5 IS NULL )
or making F1 a variable that takes ON UPDATE CURRENT_TIMESTAMP: I have read 
that MySQL checks every UPDATE for actual change, and only then changes such an 
F1 when something actually else changes.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Update Column in table only if variable is Not NULL

[hsv]

 2013/10/29 11:35 -0400, Shawn Green 
My favorite technique is the COALESCE function for this on a column-by-column 
basis

SET FieldName1 = Now(), FieldName2 = COALESCE(:MyVariable, FieldName2) 

but if MyVariable is NULL, FieldName1 reflects the attempt to change, not 
change.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Update Column in table only if variable is Not NULL

[Shawn Green]

Hi,

On 10/29/2013 9:52 PM, h...@tbbs.net wrote:

2013/10/29 11:35 -0400, Shawn Green 

My favorite technique is the COALESCE function for this on a column-by-column 
basis

SET FieldName1 = Now(), FieldName2 = COALESCE(:MyVariable, FieldName2)

but if MyVariable is NULL, FieldName1 reflects the attempt to change, not 
change.




The way I understood the second explanation was like this.

He wants to update a row of data. The FieldName1 field is always updated 
to the current date and time.  If any of the new values (passed in via 
variables) are not NULL for a specific column, replace the value on the 
row with the new value otherwise maintain the current value.


He may yet mean something completely different than how I read it the 
second time.


--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Update Column in table only if variable is Not NULL

[Andy Wallace]

Try:

update my_table
set fieldname1 = Now(), Fieldname2 = :myVariable
where Fieldname3 is not null



On 10/28/13 11:06 AM, Neil Tompkins wrote:

Hi

If I have a update statement like

UPDATE MY_TABLE
SET FieldName1 = Now(), FieldName2 = :MyVariable
WHERE FieldName3 = 'Y'

How can I only update the FieldName2 field if the value of MyVariable is
NOT NULL ?

Thanks
Neil



--
Andy Wallace
iHOUSEweb, Inc.
awall...@ihouseweb.com
(866) 645-7700 ext 219
--
Sometimes it pays to stay in bed on Monday, rather than spending the rest of the 
week debugging Monday's code.
- Christopher Thompson

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Update Column in table only if variable is Not NULL

[Shawn Green]

Hello Neil,

On 10/28/2013 2:06 PM, Neil Tompkins wrote:

Hi

If I have a update statement like

UPDATE MY_TABLE
SET FieldName1 = Now(), FieldName2 = :MyVariable
WHERE FieldName3 = 'Y'

How can I only update the FieldName2 field if the value of MyVariable is
NOT NULL ?

Thanks
Neil



This needs to be a decision you make at the application level to not 
execute the UPDATE command in the first place. Not every decision needs 
to be made by the database. Plus, it will save you the time of a full 
network round trip just to get a result from the server that you 
affected 0 rows (parsing, optimizing, executing).


Now, if this was just a typo and your :MyVariable was meant to be 
@MyVariable (a MySQL user variable) then you can put that test in the 
WHERE clause of the command


UPDATE MY_TABLE
SET FieldName1 = Now(), FieldName2 = @MyVariable
WHERE FieldName3 = 'Y'  AND @MyVariable IS NOT NULL

--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Update Column in table only if variable is Not NULL

[Neil Tompkins]

Hi Shawn

Thanks for your reply.  Maybe my example wasn't detailed enough.  Basically
the snippet of the UPDATE statement I provided shows updating only 1 field.
 However in my live working example, I have about 20 possible fields that
might need to be updated if the variable passed for each field is NOT
NULL.

Therefore, I felt this needs to be done at database level in the stored
procedure.  How can I accomplish this.

Thanks
Neil


On Mon, Oct 28, 2013 at 6:17 PM, Shawn Green shawn.l.gr...@oracle.comwrote:

 Hello Neil,

 On 10/28/2013 2:06 PM, Neil Tompkins wrote:

 Hi

 If I have a update statement like

 UPDATE MY_TABLE
 SET FieldName1 = Now(), FieldName2 = :MyVariable
 WHERE FieldName3 = 'Y'

 How can I only update the FieldName2 field if the value of MyVariable is
 NOT NULL ?

 Thanks
 Neil


 This needs to be a decision you make at the application level to not
 execute the UPDATE command in the first place. Not every decision needs to
 be made by the database. Plus, it will save you the time of a full network
 round trip just to get a result from the server that you affected 0 rows
 (parsing, optimizing, executing).

 Now, if this was just a typo and your :MyVariable was meant to be
 @MyVariable (a MySQL user variable) then you can put that test in the WHERE
 clause of the command

 UPDATE MY_TABLE
 SET FieldName1 = Now(), FieldName2 = @MyVariable
 WHERE FieldName3 = 'Y'  AND @MyVariable IS NOT NULL

 --
 Shawn Green
 MySQL Senior Principal Technical Support Engineer
 Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
 Office: Blountville, TN

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql

Re: Update just some of the fields

[hsv]

 2013/06/17 11:38 +0430, Sayyed Mohammad Emami Razavi 
update test set desc='test10' where id=1; 

_That_ is UPDATE! It is the only means of changing, but neither inserting nor 
deleting, a record.

The other fields are left the same.

MySQL also tracks whether it is an actual change; this is reflected in the 
client message
Rows matched: ?  Changed: ?  Warnings: ?
. The number after Changed: is the number of records where the new value 
really differs from the old.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: update a row only if any column has changed, in a very large table

[Andrés Tello]

Take a look here.
http://dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.html

timestamp field can be autoupdated and autoinitilizated

With both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP, the
column has the current timestamp for its default value and is automatically
updated to the current timestamp.

CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);


And after that, just export data   a defined timestamp.

I think this is the easiest way and more straight forward...


On Sat, Apr 6, 2013 at 9:49 PM, Rajeev Prasad rp.ne...@yahoo.com wrote:

 thx all, the source data is in text file.




 - Original Message -
 From: h...@tbbs.net h...@tbbs.net
 To: mysql list mysql@lists.mysql.com
 Cc:
 Sent: Saturday, April 6, 2013 8:02 PM
 Subject: Re: update a row only if any column has changed, in a very  large
 table

  2013/04/06 13:56 -0700, Rajeev Prasad 
 I have a table with around 2,000,000 records (15 columns). I have to sync
 this from an outside source once every day. not all records are
 changed/removed /new-added everyday. so what is the best way to update only
 those which have changed/added/or deleted?

 i can use update_or_create but that will update (re-write the row) even if
 nothing has changed in the row/record. wont that be an overhead? how can i
 escape that? what would be the fastest and least resources consuming way to
 do this table update?

 I also have another table with 500,000 rows and i wish to implement the
 same solution to that too.

 I earlier posted this on DBIx list, as i thought i could use DBIx tools to
 manage this. but based on response, it seems that MySQL tools would be more
 helpful in doing it in most efficent way. Plz. advice how can i address
 this.

 I also considered to delete and simply recreate the table each day.
 but changes/add and delete are not too many (may be a few hundreds.. max)
 
 Sounds like a case for replication (look it up:
 http://dev.mysql.com/doc/refman/5.5/en/replication.html
 http://dev.mysql.com/doc/refman/5.5/en/replication-formats.html). There
 is statement-replication, and row-replication. The former replicates all
 operations on the database, in the form wherin they were made. False
 changes (changing a field to its former value), too, are recorded. The
 latter records only those changes to a table that are real changes. (In
 MySQL statement-replication is of earlier implementation.)

 After changes are recorded, they are passed from the master --the
 wellspring of the changes-- to the slave --the taker of them.

 These are not tools, as such: replication is something implemented in the
 database-management system. If both your databases are in MySQL you can get
 help here.


 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql

RE: Update and lock question.

[Rick James]

An optimization (at least in InnoDB) is to delay updating the secondary 
index(es).  If you can provide a reproducible test case, it would probably be 
worth filing a bug at bugs.mysql.com

 -Original Message-
 From: Andrés Tello [mailto:mr.crip...@gmail.com]
 Sent: Friday, April 05, 2013 2:56 PM
 To: Urvashi Pathak
 Cc: mysql
 Subject: Re: Update and lock question.
 
 Thanks Urvashi.
 
 Based on your answer, instead of the data I looked into the index, and
 it appears that it was an index issue...
 
 I think I have nailed the wait lock contdition due a updating indexes
 unnecesarely...
 
 
 On Thu, Apr 4, 2013 at 10:52 AM, Urvashi Pathak
 urvashi_pat...@symantec.com
  wrote:
 
 
 
  Hi Andrés,
 
  Select for update makes sure that no other process can change the
 data
  between you selected it for update and then actually changed it and
  commit it.
  If you do not use select for update  then it is possible that some
  other process can change the data  in the mean time between you
  selected and  actually changes it. In this case you not see the
 result
  you actually intend to have.
 
  Innodb will only lock whole table only if there is no where clause in
  the update statement, which I sure you do not have. Innodb follows
 row
  level locking.
 
 
  -Urvi
 
  -Original Message-
  From: Andrés Tello [mailto:mr.crip...@gmail.com]
  Sent: Thursday, April 04, 2013 9:08 AM
  To: mysql
  Subject: Update and lock question.
 
  I'm doing some tests, but have a questions about locking.
 
  In a innodb table, if you issue an select for update lock for a row,
  supposedly, it only locks that row, but if you don't issue a select
  for update, and trow the update... does it locks the hole table?
 
  The update goes over an indexed field, or the effect of locking the
  hole table is due I'm updating an indexed field?
 
  This is because I'm running into dead locks, but I know there is no
  select for update to the row being updated.
 
 
  Thanks.
 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

RE: update a row only if any column has changed, in a very large table

[Jason Trebilcock]

If'n it were my nickel, here is how I would solve the problem (at a somewhat
high level). That is, assuming I had an ETL tool available.

1. Create landing tables for your source data.
2. Load data from the source table(s) to your new landing table(s).
3. Perform lookups from the new landing table to target to identify:
inserts, updates, deletes, do nothings.
4. Write these status back to the landing table. (Want to separate the
inserts from the updates from the deletions.)
5. Load the 'inserts' to your target table.
6. Load the 'updates' to your target table.
7. Perform the 'deletes' on your target table.

And, one other thing that I would do is to log counts and times...so I could
go back over time and evaluate performance.

But then again, I work with ETL tools...so, that is my proverbial hammer.
And given that, everything pretty much looks like a nail.

-Original Message-
From: Rajeev Prasad [mailto:rp.ne...@yahoo.com] 
Sent: Saturday, April 06, 2013 3:57 PM
To: mysql list
Subject: update a row only if any column has changed, in a very large table

hello,

I have a table with around 2,000,000 records (15 columns). I have to sync
this from an outside source once everyday. not all records are
changed/removed /new-added everyday. so what is the best way to update only
those which have changed/added/or deleted?

i can use update_or_create but that will update (re-write the row) even if
nothing has changed in the row/record. wont that be an overhead? how can i
escape that? what would be the fastest and least resources consuming way to
do this table update?

I also have another table with 500,000 rows and i wish to implement the
same solution to that too.

I earlier posted this on DBIx list, as i thought i could use DBIx tools to
manage this. but based on response, it seems that MySQL tools would be more
helpful in doing it in most efficent way. Plz. advice how can i address
this.
 
I also considered to delete and simply recreate the table each day. but
chnages/add and delete are not too many (may be a few hundreds.. max)
 
 
ty.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

RE: Update and lock question.

[Urvashi Pathak]

Hi Andrés,

Select for update makes sure that no other process can change the data between 
you selected it for update and then actually changed it and commit it.
If you do not use select for update  then it is possible that some other 
process can change the data  in the mean time between you selected and  
actually changes it. In this case you not see the result you actually intend to 
have.

Innodb will only lock whole table only if there is no where clause in the 
update statement, which I sure you do not have. Innodb follows row level 
locking.


-Urvi

-Original Message-
From: Andrés Tello [mailto:mr.crip...@gmail.com] 
Sent: Thursday, April 04, 2013 9:08 AM
To: mysql
Subject: Update and lock question.

I'm doing some tests, but have a questions about locking.

In a innodb table, if you issue an select for update lock for a row, 
supposedly, it only locks that row, but if you don't issue a select for update, 
and trow the update... does it locks the hole table?

The update goes over an indexed field, or the effect of locking the hole table 
is due I'm updating an indexed field?

This is because I'm running into dead locks, but I know there is no select for 
update to the row being updated.


Thanks.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: update a row only if any column has changed, in a very large table

[hsv]

 2013/04/06 13:56 -0700, Rajeev Prasad 
I have a table with around 2,000,000 records (15 columns). I have to sync this 
from an outside source once every day. not all records are changed/removed 
/new-added everyday. so what is the best way to update only those which have 
changed/added/or deleted?

i can use update_or_create but that will update (re-write the row) even if 
nothing has changed in the row/record. wont that be an overhead? how can i 
escape that? what would be the fastest and least resources consuming way to do 
this table update?

I also have another table with 500,000 rows and i wish to implement the same 
solution to that too.

I earlier posted this on DBIx list, as i thought i could use DBIx tools to 
manage this. but based on response, it seems that MySQL tools would be more 
helpful in doing it in most efficent way. Plz. advice how can i address this.
 
I also considered to delete and simply recreate the table each day. but 
changes/add and delete are not too many (may be a few hundreds.. max)

Sounds like a case for replication (look it up: 
http://dev.mysql.com/doc/refman/5.5/en/replication.html
http://dev.mysql.com/doc/refman/5.5/en/replication-formats.html). There is 
statement-replication, and row-replication. The former replicates all 
operations on the database, in the form wherin they were made. False changes 
(changing a field to its former value), too, are recorded. The latter records 
only those changes to a table that are real changes. (In MySQL 
statement-replication is of earlier implementation.)

After changes are recorded, they are passed from the master --the wellspring of 
the changes-- to the slave --the taker of them.

These are not tools, as such: replication is something implemented in the 
database-management system. If both your databases are in MySQL you can get 
help here.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: update a row only if any column has changed, in a very large table

[Rajeev Prasad]

thx all, the source data is in text file.




- Original Message -
From: h...@tbbs.net h...@tbbs.net
To: mysql list mysql@lists.mysql.com
Cc: 
Sent: Saturday, April 6, 2013 8:02 PM
Subject: Re: update a row only if any column has changed, in a very  large table

 2013/04/06 13:56 -0700, Rajeev Prasad 
I have a table with around 2,000,000 records (15 columns). I have to sync this 
from an outside source once every day. not all records are changed/removed 
/new-added everyday. so what is the best way to update only those which have 
changed/added/or deleted?

i can use update_or_create but that will update (re-write the row) even if 
nothing has changed in the row/record. wont that be an overhead? how can i 
escape that? what would be the fastest and least resources consuming way to do 
this table update?

I also have another table with 500,000 rows and i wish to implement the same 
solution to that too.

I earlier posted this on DBIx list, as i thought i could use DBIx tools to 
manage this. but based on response, it seems that MySQL tools would be more 
helpful in doing it in most efficent way. Plz. advice how can i address this.

I also considered to delete and simply recreate the table each day. but 
changes/add and delete are not too many (may be a few hundreds.. max)

Sounds like a case for replication (look it up: 
http://dev.mysql.com/doc/refman/5.5/en/replication.html
http://dev.mysql.com/doc/refman/5.5/en/replication-formats.html). There is 
statement-replication, and row-replication. The former replicates all 
operations on the database, in the form wherin they were made. False changes 
(changing a field to its former value), too, are recorded. The latter records 
only those changes to a table that are real changes. (In MySQL 
statement-replication is of earlier implementation.)

After changes are recorded, they are passed from the master --the wellspring of 
the changes-- to the slave --the taker of them.

These are not tools, as such: replication is something implemented in the 
database-management system. If both your databases are in MySQL you can get 
help here.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Update and lock question.

[Andrés Tello]

Thanks Urvashi.

Based on your answer, instead of the data I looked into the index, and it
appears that it was an index issue...

I think I have nailed the wait lock contdition due a updating indexes
unnecesarely...


On Thu, Apr 4, 2013 at 10:52 AM, Urvashi Pathak urvashi_pat...@symantec.com
 wrote:



 Hi Andrés,

 Select for update makes sure that no other process can change the data
 between you selected it for update and then actually changed it and commit
 it.
 If you do not use select for update  then it is possible that some other
 process can change the data  in the mean time between you selected and
  actually changes it. In this case you not see the result you actually
 intend to have.

 Innodb will only lock whole table only if there is no where clause in the
 update statement, which I sure you do not have. Innodb follows row level
 locking.


 -Urvi

 -Original Message-
 From: Andrés Tello [mailto:mr.crip...@gmail.com]
 Sent: Thursday, April 04, 2013 9:08 AM
 To: mysql
 Subject: Update and lock question.

 I'm doing some tests, but have a questions about locking.

 In a innodb table, if you issue an select for update lock for a row,
 supposedly, it only locks that row, but if you don't issue a select for
 update, and trow the update... does it locks the hole table?

 The update goes over an indexed field, or the effect of locking the hole
 table is due I'm updating an indexed field?

 This is because I'm running into dead locks, but I know there is no select
 for update to the row being updated.


 Thanks.

Re: Update One of Three tables in a single query

[Andrew Moore]

What's your use case? I've not heard of a like this done at table/sql level.

You could use stored procedures  dynamic SQL to achieve it though.


On Thu, Nov 29, 2012 at 5:44 PM, Chris W 4rfv...@cox.net wrote:

 I have three tables, TableA, TableB, and TableC each has a unique ID
 field, idA, idB, idC respectively each table also has a field called
 'Status'.  The id field is always greater than zero.  Now suppose I have
 three variables A, B, and C.   Two of them are zero and the other is a
 valid ID for the corresponding table.  Only I don't know which one.  Is
 there a way to write a single update statement that will update only the
 one table that has the matching ID?  I have tired a few different ideas but
 none seem worth mentioning here since they all either update too many
 records or don't update any records.

 Thanks for the help.

 Chris W

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql

RE: Update One of Three tables in a single query

[Rick James]

Some combo of IN, EXISTS, UNION?  It will probably be verbose.

 -Original Message-
 From: Andrew Moore [mailto:eroomy...@gmail.com]
 Sent: Thursday, November 29, 2012 11:32 AM
 To: Chris W
 Cc: MYSQL General List
 Subject: Re: Update One of Three tables in a single query
 
 What's your use case? I've not heard of a like this done at table/sql
 level.
 
 You could use stored procedures  dynamic SQL to achieve it though.
 
 
 On Thu, Nov 29, 2012 at 5:44 PM, Chris W 4rfv...@cox.net wrote:
 
  I have three tables, TableA, TableB, and TableC each has a unique ID
  field, idA, idB, idC respectively each table also has a field called
  'Status'.  The id field is always greater than zero.  Now suppose I
 have
  three variables A, B, and C.   Two of them are zero and the other is
 a
  valid ID for the corresponding table.  Only I don't know which one.
  Is there a way to write a single update statement that will update
  only the one table that has the matching ID?  I have tired a few
  different ideas but none seem worth mentioning here since they all
  either update too many records or don't update any records.
 
  Thanks for the help.
 
  Chris W
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:http://lists.mysql.com/mysql
 
 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: update doesn't

[Mogens Melander]

On Sun, August 19, 2012 18:19, william drescher wrote:
 On 8/17/2012 12:13 PM, Rik Wasmus wrote:
 I get 1 row affected, but the status does not change when I look
 at the row.

 If I set it to 'X' it does change.

 To make it even more wacky, if I (using phpMyAdmin) change it to
 'H' it will change and the row is shown change, but when I go to
 examine the row (using the pencil icon=Edit) it changes back to 'W'.

 Either there is something really strange or my mysql is possessed.

 I am using Server version: 5.1.63-0ubuntu0.10.04.

 Anyone have any thoughts about this or suggestions on how to
 debug it?

 1) One thing that _could_ do this is a trigger. Does SHOW TRIGGERS; show
 any
 that could be doing this?

 2) However, in 99.999% of cases, it is just a logic error in the
 application
 (be it your application or PHPMyAdmin), not anything in MySQL. Can you
 connect
 with the command line client, run the UPDATE statement, en then check
 what the
 SELECT shows? If it shows a correct result... the problem ain't in MySQL
 itself.

 mysql select status from tasks;
 ++
 | status |
 ++
 | W  |
 ++
 1 row in set (0.00 sec)

 mysql update tasks set status= 'H';
 Query OK, 1 row affected (0.00 sec)
 Rows matched: 1 Changed 1 Warnings: 0

 mysql select status from tasks;
 ++
 | status |
 ++
 | W  |
 ++
 1 row in set (0.00 sec)

 whoops

 bill


Maybe a SHOW CREATE TABLE `tasks`\g could shed some light.

-- 
Mogens Melander
+66 8701 33224

-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: update doesn't

[william drescher]

On 8/20/2012 10:09 AM, Mogens Melander wrote:


On Sun, August 19, 2012 18:19, william drescher wrote:

On 8/17/2012 12:13 PM, Rik Wasmus wrote:

I get 1 row affected, but the status does not change when I look
at the row.

If I set it to 'X' it does change.

To make it even more wacky, if I (using phpMyAdmin) change it to
'H' it will change and the row is shown change, but when I go to
examine the row (using the pencil icon=Edit) it changes back to 'W'.

Either there is something really strange or my mysql is possessed.

I am using Server version: 5.1.63-0ubuntu0.10.04.

Anyone have any thoughts about this or suggestions on how to
debug it?


1) One thing that _could_ do this is a trigger. Does SHOW TRIGGERS; show
any
that could be doing this?

2) However, in 99.999% of cases, it is just a logic error in the
application
(be it your application or PHPMyAdmin), not anything in MySQL. Can you
connect
with the command line client, run the UPDATE statement, en then check
what the
SELECT shows? If it shows a correct result... the problem ain't in MySQL
itself.


mysql select status from tasks;
++
| status |
++
| W  |
++
1 row in set (0.00 sec)

mysql update tasks set status= 'H';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed 1 Warnings: 0

mysql select status from tasks;
++
| status |
++
| W  |
++
1 row in set (0.00 sec)

whoops

bill



Maybe a SHOW CREATE TABLE `tasks`\g could shed some light.

I can't figure out how to get puTTY to log the session so I can't 
cut and paste the output, but here is a phpMySQL export of the 
table with the extra stuff typed in from the create table


task_id mediumint(9) NOT NULL AUTO_INCREMENT
status char(1) NOT NULL DEFAULT ''
priority char(1) NOT NULL
due_date_time datetimeNOT NULL
hold_date_time datetime NOT NULL
review_date_time datetime  Default NULL
requestor varchar(10) NOT NULL
performer varchar(10) NOT NULL
repeat_frequency char(1) NOT NULL
repeat_time char(2) NOT NULL
repeat_from char(1) NOT NULL
task_title varchar(60) NOT NULL
description text Yes NULL
history text Yes NULL
function_to_run varchar(80) DEFAULT NULL
last_access datetime NOT NULL
completed datetime NOT NULL
notify tinyint(1) DEFAULT NULL

PRIMARY kEY('TASK_ID')
UNIQUE KEY 'performer' ('performer', 'status', priority', 
due_date_time', 'task_id')
UNIQUE KEY 'requestor' ('requestor', prirority','due_date_time', 
'task_id')

ENGINE=InnoDB AUTO_INCREMENT=312 DEFAULT CHARSET=ascii

As a workaround I changed status to task_status and now it 
works just fine.



bill


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: update doesn't

[william drescher]

On 8/17/2012 12:13 PM, Rik Wasmus wrote:

I get 1 row affected, but the status does not change when I look
at the row.

If I set it to 'X' it does change.

To make it even more wacky, if I (using phpMyAdmin) change it to
'H' it will change and the row is shown change, but when I go to
examine the row (using the pencil icon=Edit) it changes back to 'W'.

Either there is something really strange or my mysql is possessed.

I am using Server version: 5.1.63-0ubuntu0.10.04.

Anyone have any thoughts about this or suggestions on how to
debug it?


1) One thing that _could_ do this is a trigger. Does SHOW TRIGGERS; show any
that could be doing this?

2) However, in 99.999% of cases, it is just a logic error in the application
(be it your application or PHPMyAdmin), not anything in MySQL. Can you connect
with the command line client, run the UPDATE statement, en then check what the
SELECT shows? If it shows a correct result... the problem ain't in MySQL
itself.


mysql select status from tasks;
++
| status |
++
| W  |
++
1 row in set (0.00 sec)

mysql update tasks set status= 'H';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed 1 Warnings: 0

mysql select status from tasks;
++
| status |
++
| W  |
++
1 row in set (0.00 sec)

whoops

bill





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: update doesn't

[Johnny Withers]

The client indicates a warning after the update. Issue a show warnings
after the update.
On Aug 19, 2012 11:19 AM, william drescher will...@techservsys.com
wrote:

 On 8/17/2012 12:13 PM, Rik Wasmus wrote:

 I get 1 row affected, but the status does not change when I look
 at the row.

 If I set it to 'X' it does change.

 To make it even more wacky, if I (using phpMyAdmin) change it to
 'H' it will change and the row is shown change, but when I go to
 examine the row (using the pencil icon=Edit) it changes back to 'W'.

 Either there is something really strange or my mysql is possessed.

 I am using Server version: 5.1.63-0ubuntu0.10.04.

 Anyone have any thoughts about this or suggestions on how to
 debug it?


 1) One thing that _could_ do this is a trigger. Does SHOW TRIGGERS; show
 any
 that could be doing this?

 2) However, in 99.999% of cases, it is just a logic error in the
 application
 (be it your application or PHPMyAdmin), not anything in MySQL. Can you
 connect
 with the command line client, run the UPDATE statement, en then check
 what the
 SELECT shows? If it shows a correct result... the problem ain't in MySQL
 itself.

  mysql select status from tasks;
 ++
 | status |
 ++
 | W  |
 ++
 1 row in set (0.00 sec)

 mysql update tasks set status= 'H';
 Query OK, 1 row affected (0.00 sec)
 Rows matched: 1 Changed 1 Warnings: 0

 mysql select status from tasks;
 ++
 | status |
 ++
 | W  |
 ++
 1 row in set (0.00 sec)

 whoops

 bill





 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql

Re: update doesn't

[william drescher]

On 8/19/2012 1:25 PM, Johnny Withers wrote:

The client indicates a warning after the update. Issue a show warnings
after the update.


actually, it doesn't.
but I did a show warnings and it replied: Empty Set (0.00 sec)
I also did a show triggers and it replied: Empty Set (0.00 sec)


On Aug 19, 2012 11:19 AM, william drescher will...@techservsys.com
wrote:


On 8/17/2012 12:13 PM, Rik Wasmus wrote:


I get 1 row affected, but the status does not change when I look

at the row.

If I set it to 'X' it does change.

To make it even more wacky, if I (using phpMyAdmin) change it to
'H' it will change and the row is shown change, but when I go to
examine the row (using the pencil icon=Edit) it changes back to 'W'.

Either there is something really strange or my mysql is possessed.

I am using Server version: 5.1.63-0ubuntu0.10.04.

Anyone have any thoughts about this or suggestions on how to
debug it?



1) One thing that _could_ do this is a trigger. Does SHOW TRIGGERS; show
any
that could be doing this?

2) However, in 99.999% of cases, it is just a logic error in the
application
(be it your application or PHPMyAdmin), not anything in MySQL. Can you
connect
with the command line client, run the UPDATE statement, en then check
what the
SELECT shows? If it shows a correct result... the problem ain't in MySQL
itself.

  mysql select status from tasks;

++
| status |
++
| W  |
++
1 row in set (0.00 sec)

mysql update tasks set status= 'H';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed 1 Warnings: 0

mysql select status from tasks;
++
| status |
++
| W  |
++
1 row in set (0.00 sec)

whoops

bill





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql








--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: update doesn't

[william drescher]

On 8/19/2012 5:56 PM, william drescher wrote:


  mysql select status from tasks;

++
| status |
++
| W  |
++
1 row in set (0.00 sec)

mysql update tasks set status= 'H';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed 1 Warnings: 0

mysql select status from tasks;
++
| status |
++
| W  |
++
1 row in set (0.00 sec)

whoops

bill





further information - interesting, it will accept X but not H
  mysql select status from tasks;
++
| status |
++
| W  |
++
1 row in set (0.00 sec)

mysql update tasks set status= 'X';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed 1 Warnings: 0

mysql select status from tasks;
++
| status |
++
|  X |
++
1 row in set (0.00 sec)


mysql update tasks set status= 'H';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed 1 Warnings: 0

mysql select status from tasks;
++
| status |
++
| W  |
++
1 row in set (0.00 sec)

whoops


However, If I fully qualify the col name it works.


   mysql select status from tasks;
++
| status |
++
| W  |
++
1 row in set (0.00 sec)

mysql update Information_server.tasks set status= 'H';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed 1 Warnings: 0

mysql select status from tasks;
++
| status |
++
| H  |
++
1 row in set (0.00 sec)

I suspect there is a problem naming a col status

bill



bill





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: update doesn't

[Rik Wasmus]

 I get 1 row affected, but the status does not change when I look
 at the row.
 
 If I set it to 'X' it does change.
 
 To make it even more wacky, if I (using phpMyAdmin) change it to
 'H' it will change and the row is shown change, but when I go to
 examine the row (using the pencil icon=Edit) it changes back to 'W'.
 
 Either there is something really strange or my mysql is possessed.
 
 I am using Server version: 5.1.63-0ubuntu0.10.04.
 
 Anyone have any thoughts about this or suggestions on how to
 debug it?

1) One thing that _could_ do this is a trigger. Does SHOW TRIGGERS; show any 
that could be doing this?

2) However, in 99.999% of cases, it is just a logic error in the application 
(be it your application or PHPMyAdmin), not anything in MySQL. Can you connect 
with the command line client, run the UPDATE statement, en then check what the 
SELECT shows? If it shows a correct result... the problem ain't in MySQL 
itself.
-- 
Rik Wasmus

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: update query

[Ananda Kumar]

Do you just want to replace current value in client column to NEW.
You can write a stored proc , with a cursor and loop through the cursor,
update each table.

regards
anandkl

On Mon, Apr 30, 2012 at 2:47 PM, Pothanaboyina Trimurthy 
skd.trimur...@gmail.com wrote:

 Hi all,
  i have one database with 120 tables and each table contains one
 common column that is client now i want to update all the tables
 column client = NEW. is it possible to write a single query to
 update this one.

 please help me.

 thanks in advance

 Thanks  Kind Regards,
 Trimurthy.p

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql

RE: update query

[Rick James]

How many rows in each table?
If only one row, why is the schema designed that way?
If multiple rows, why are you changing _all_ rows that way?

I am questioning the schema design that would lead to your question.

Follow on to Ananda's answer:  See
   information_schema.TABLES  WHERE TABLE_SCHEMA = 'dbname'
   information_schema.COLUMNS  WHERE COLUMN_NAME = 'client'

 -Original Message-
 From: Ananda Kumar [mailto:anan...@gmail.com]
 Sent: Monday, April 30, 2012 2:26 AM
 To: Pothanaboyina Trimurthy
 Cc: mysql@lists.mysql.com
 Subject: Re: update query
 
 Do you just want to replace current value in client column to NEW.
 You can write a stored proc , with a cursor and loop through the
 cursor, update each table.
 
 regards
 anandkl
 
 On Mon, Apr 30, 2012 at 2:47 PM, Pothanaboyina Trimurthy 
 skd.trimur...@gmail.com wrote:
 
  Hi all,
   i have one database with 120 tables and each table contains one
  common column that is client now i want to update all the tables
  column client = NEW. is it possible to write a single query to
  update this one.
 
  please help me.
 
  thanks in advance
 
  Thanks  Kind Regards,
  Trimurthy.p
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:http://lists.mysql.com/mysql
 
 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: UPDATE triggers with REPLACE statements

[Hal�sz S�ndor]

; 20111219 03:42 PM -0800, Jim McNeely 
Not if you are using innoDB tables. For these, you use INSERT and UPDATE 
triggers. 

Jim McNeely
On Dec 19, 2011, at 11:58 AM, Halász Sándor wrote:

 2011/12/19 11:30 -0800, Jim McNeely 
 In the MySQL documentation, we find this tantalizing statement:
 
 It is possible that in the case of a duplicate-key error, a storage engine 
 may perform the REPLACE as an update rather than a delete plus insert, but 
 the semantics are the same. There are no user-visible effects other than a 
 possible difference in how the storage engine increments Handler_xxx status 
 variables. 
 
 Well, try it--but beware of these statements: the semantics are the same. 
 There are no user-visible effects other than a possible difference in how the 
 storage engine increments Handler_xxx status variables.
 
 If accurate, the triggers are those of DELETE  INSERT, not UPDATE.

In my experimenting I find (version 5.5.8) that DELETE  INSERT are triggered, 
not UPDATE. The statement There are no user-visible effects... is simply 
wrong. The SQL-programmer has to be ready for either outcome.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: UPDATE triggers with REPLACE statements

[Jim McNeely]

Perfect!! This is the answer I was looking for. Thanks! I didn't know about 
this.

Jim McNeely

On Dec 18, 2011, at 11:26 AM, Claudio Nanni wrote:

 Only if you can change the application you could use INSERTON DUPLICATE 
 KEY UPDATE  instead of REPLACE.
 
 Check Peter's post here: http://kae.li/iiigi
 
 Cheers
 
 Claudio
 
 
 2011/12/17 Jim McNeely j...@newcenturydata.com
 Here is a fun one!
 
 I have a set of tables that get populated and changed a lot from lots of 
 REPLACE statements. Now, I need an ON UPDATE trigger, but of course the 
 trigger never gets triggered because REPLACES are all deletes and inserts.
 
 The trigger is going to populate another table as a queue for a system to do 
 something whenever a particular field changes.
 
 SO, does anyone have some slick idea how to handle this little dilemma? I 
 have an idea but I have a feeling there is something better out there.
 
 Thanks!
 
 Jim McNeely
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql
 
 
 
 
 -- 
 Claudio

Re: UPDATE triggers with REPLACE statements

[Jim McNeely]

In the MySQL documentation, we find this tantalizing statement:

It is possible that in the case of a duplicate-key error, a storage engine may 
perform the REPLACE as an update rather than a delete plus insert, but the 
semantics are the same. There are no user-visible effects other than a possible 
difference in how the storage engine increments Handler_xxx status variables.

Does anyone know what engine this is? I can't seem to find any info via google. 
If I could live with the choice of engine, I could make this work with no extra 
programming at all.

Thanks,

Jim McNeely

On Dec 18, 2011, at 11:26 AM, Claudio Nanni wrote:

 Only if you can change the application you could use INSERTON DUPLICATE
 KEY UPDATE  instead of REPLACE.
 
 Check Peter's post here: http://kae.li/iiigi
 
 Cheers
 
 Claudio
 
 
 2011/12/17 Jim McNeely j...@newcenturydata.com
 
 Here is a fun one!
 
 I have a set of tables that get populated and changed a lot from lots of
 REPLACE statements. Now, I need an ON UPDATE trigger, but of course the
 trigger never gets triggered because REPLACES are all deletes and inserts.
 
 The trigger is going to populate another table as a queue for a system to
 do something whenever a particular field changes.
 
 SO, does anyone have some slick idea how to handle this little dilemma? I
 have an idea but I have a feeling there is something better out there.
 
 Thanks!
 
 Jim McNeely
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql
 
 
 
 
 -- 
 Claudio

Re: UPDATE triggers with REPLACE statements

[Claudio Nanni]

Good to know and good that you took time to read the manual, good approach.

But why bother with REPLACE if you will go with INSERT.ON DUPLICATE KEY
UPDATE?

The storage engine is a property of your table and you can set it and/or
change it, it is the low-level layer (physical) of the database that takes
care on how data is actually stored and retrieved.

You can check your table with:

SHOW TABLE STATUS LIKE 'your-table-name';

Manual page: http://kae.li/iiiga

Cheers

Claudio

2011/12/19 Jim McNeely j...@newcenturydata.com

 In the MySQL documentation, we find this tantalizing statement:

 It is possible that in the case of a duplicate-key error, a storage
 engine may perform the REPLACE as an update rather than a delete plus
 insert, but the semantics are the same. There are no user-visible effects
 other than a possible difference in how the storage engine increments
 Handler_xxx status variables.

 Does anyone know what engine this is? I can't seem to find any info via
 google. If I could live with the choice of engine, I could make this work
 with no extra programming at all.

 Thanks,

 Jim McNeely

 On Dec 18, 2011, at 11:26 AM, Claudio Nanni wrote:

  Only if you can change the application you could use INSERTON
 DUPLICATE
  KEY UPDATE  instead of REPLACE.
 
  Check Peter's post here: http://kae.li/iiigi
 
  Cheers
 
  Claudio
 
 
  2011/12/17 Jim McNeely j...@newcenturydata.com
 
  Here is a fun one!
 
  I have a set of tables that get populated and changed a lot from lots of
  REPLACE statements. Now, I need an ON UPDATE trigger, but of course the
  trigger never gets triggered because REPLACES are all deletes and
 inserts.
 
  The trigger is going to populate another table as a queue for a system
 to
  do something whenever a particular field changes.
 
  SO, does anyone have some slick idea how to handle this little dilemma?
 I
  have an idea but I have a feeling there is something better out there.
 
  Thanks!
 
  Jim McNeely
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:http://lists.mysql.com/mysql
 
 
 
 
  --
  Claudio




-- 
Claudio

Re: UPDATE triggers with REPLACE statements

[Jim McNeely]

With REPLACE, you just set up the query the same as an INSERT statement but 
otherwise it just works. With ON DUPLICATE UPDATE you have to set up the whole 
query with the entire text all over again as an update. The query strings for 
what I'm doing are in some cases pushing enough text in medical report fields 
that it uses a MediumText data type, and I am watchful of bandwidth and 
performance, so this seems better - I'm not sending the field names and values 
twice. It is also something I don't have to program, I can just set the engine. 
The performance bottleneck is NOT likely going to be MySQL with either engine, 
but the processes creating these queries have some limitations.

Anyway, I just thought I would share. BTW I experimented, and innoDB does 
updates and fires off update triggers for REPLACE statements, but MyISAM does 
delete/inserts.

Jim McNeely

On Dec 19, 2011, at 1:28 PM, Claudio Nanni wrote:

 Good to know and good that you took time to read the manual, good approach.
 
 But why bother with REPLACE if you will go with INSERT.ON DUPLICATE KEY
 UPDATE?
 
 The storage engine is a property of your table and you can set it and/or
 change it, it is the low-level layer (physical) of the database that takes
 care on how data is actually stored and retrieved.
 
 You can check your table with:
 
 SHOW TABLE STATUS LIKE 'your-table-name';
 
 Manual page: http://kae.li/iiiga
 
 Cheers
 
 Claudio
 
 2011/12/19 Jim McNeely j...@newcenturydata.com
 
 In the MySQL documentation, we find this tantalizing statement:
 
 It is possible that in the case of a duplicate-key error, a storage
 engine may perform the REPLACE as an update rather than a delete plus
 insert, but the semantics are the same. There are no user-visible effects
 other than a possible difference in how the storage engine increments
 Handler_xxx status variables.
 
 Does anyone know what engine this is? I can't seem to find any info via
 google. If I could live with the choice of engine, I could make this work
 with no extra programming at all.
 
 Thanks,
 
 Jim McNeely
 
 On Dec 18, 2011, at 11:26 AM, Claudio Nanni wrote:
 
 Only if you can change the application you could use INSERTON
 DUPLICATE
 KEY UPDATE  instead of REPLACE.
 
 Check Peter's post here: http://kae.li/iiigi
 
 Cheers
 
 Claudio
 
 
 2011/12/17 Jim McNeely j...@newcenturydata.com
 
 Here is a fun one!
 
 I have a set of tables that get populated and changed a lot from lots of
 REPLACE statements. Now, I need an ON UPDATE trigger, but of course the
 trigger never gets triggered because REPLACES are all deletes and
 inserts.
 
 The trigger is going to populate another table as a queue for a system
 to
 do something whenever a particular field changes.
 
 SO, does anyone have some slick idea how to handle this little dilemma?
 I
 have an idea but I have a feeling there is something better out there.
 
 Thanks!
 
 Jim McNeely
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql
 
 
 
 
 --
 Claudio
 
 
 
 
 -- 
 Claudio


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: UPDATE triggers with REPLACE statements

[Hal�sz S�ndor]

 2011/12/19 11:30 -0800, Jim McNeely 
In the MySQL documentation, we find this tantalizing statement:

It is possible that in the case of a duplicate-key error, a storage engine may 
perform the REPLACE as an update rather than a delete plus insert, but the 
semantics are the same. There are no user-visible effects other than a possible 
difference in how the storage engine increments Handler_xxx status variables. 

Well, try it--but beware of these statements: the semantics are the same. 
There are no user-visible effects other than a possible difference in how the 
storage engine increments Handler_xxx status variables.

If accurate, the triggers are those of DELETE  INSERT, not UPDATE.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: UPDATE triggers with REPLACE statements

[Jim McNeely]

Not if you are using innoDB tables. For these, you use INSERT and UPDATE 
triggers. 

Jim McNeely
On Dec 19, 2011, at 11:58 AM, Halász Sándor wrote:

 2011/12/19 11:30 -0800, Jim McNeely 
 In the MySQL documentation, we find this tantalizing statement:
 
 It is possible that in the case of a duplicate-key error, a storage engine 
 may perform the REPLACE as an update rather than a delete plus insert, but 
 the semantics are the same. There are no user-visible effects other than a 
 possible difference in how the storage engine increments Handler_xxx status 
 variables. 
 
 Well, try it--but beware of these statements: the semantics are the same. 
 There are no user-visible effects other than a possible difference in how the 
 storage engine increments Handler_xxx status variables.
 
 If accurate, the triggers are those of DELETE  INSERT, not UPDATE.
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql
 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: UPDATE triggers with REPLACE statements

[Hal�sz S�ndor]

 2011/12/19 13:55 -0800, Jim McNeely 
Anyway, I just thought I would share. BTW I experimented, and innoDB does 
updates and fires off update triggers for REPLACE statements, but MyISAM does 
delete/inserts. 

Thank you. Which version?

Well, then the documentation is wrong: it is indeed visible to the user which 
happens.

And what would innoDB do in this case, UNIQUE index X1 and UNIQUE index X2, 
with a VARCHAR f, too,

X1 X2 f
 1  2 a
 2  3 b

and one REPLACEs with (2, 2, 'b')? (Earlier in the section such a situation is 
referred to.) Delete one and UPDATE the other?

User beware!


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: UPDATE triggers with REPLACE statements

[Claudio Nanni]

Only if you can change the application you could use INSERTON DUPLICATE
KEY UPDATE  instead of REPLACE.

Check Peter's post here: http://kae.li/iiigi

Cheers

Claudio


2011/12/17 Jim McNeely j...@newcenturydata.com

 Here is a fun one!

 I have a set of tables that get populated and changed a lot from lots of
 REPLACE statements. Now, I need an ON UPDATE trigger, but of course the
 trigger never gets triggered because REPLACES are all deletes and inserts.

 The trigger is going to populate another table as a queue for a system to
 do something whenever a particular field changes.

 SO, does anyone have some slick idea how to handle this little dilemma? I
 have an idea but I have a feeling there is something better out there.

 Thanks!

 Jim McNeely
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql




-- 
Claudio

Re: UPDATE triggers with REPLACE statements

[Hal�sz S�ndor]

 2011/12/16 16:00 -0800, Jim McNeely 
I have a set of tables that get populated and changed a lot from lots of 
REPLACE statements. Now, I need an ON UPDATE trigger, but of course the trigger 
never gets triggered because REPLACES are all deletes and inserts.

The trigger is going to populate another table as a queue for a system to do 
something whenever a particular field changes.

SO, does anyone have some slick idea how to handle this little problem? I have 
an idea but I have a feeling there is something better out there.

Exactly what do you mean? You want the DELETE-trigger to talk to the 
INSERT-trigger for to learn whether the field changed? Try user-defined 
variable.

I do not like that, but the only other that comes to mind is no better, a tiny 
table that exists only for the DELETE-trigger to save the field value, and the 
INSERT-trigger to look at it.

Here is a trigger that inserts into a table PaimentLog after there is inserting 
into a table GiftAndShare:

CREATE TRIGGER newGiftAft AFTER INSERT ON GiftAndShare FOR EACH ROW BEGIN
...
INSERT INTO PaimentLog
SELECT NEW.MemberID, Surname, GivenName, NEW.Why, NEW.Amount, NEW.Date, 
NEW.Method, NEW.byWhom
FROM Nam WHERE MemberID = NEW.MemberID AND Rank = 0;
END


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: UPDATE with variable

[Johan De Meersman]

- Original Message -
 From: Halász Sándor h...@tbbs.net

 mysql update address set membersince = (select membersince from
 address where memberid = 1258) where memberid = 1724;

IIRC, subselects are allowed, except for selects that reference the table 
you're updating.


--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Update table on lost connection

[Hank]

Check out the GET_LOCK and RELEASE_LOCK virtual lock functions in MySQL.

-Hank



On Wed, Sep 28, 2011 at 9:15 AM, Alex Schaft al...@quicksoftware.co.za wrote:
 Hi,

 We're busy moving legacy apps from foxpro tables to mysql. User logins were
 tracked via a record in a table which the app then locked, preventing
 multiple logins for the same user code.

 I want to simulate this via a locked column in a mysql table, but would
 need the field to be cleared if the server loses the connection to the
 client. How would I do this, or is there an alternative?

 Thanks,
 Alex




 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:    http://lists.mysql.com/mysql?unsub=hes...@gmail.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Update on inner join - looks good to me, where did I go wrong?

[Dotan Cohen]

On Sat, Sep 10, 2011 at 01:48, Carsten Pedersen cars...@bitbybit.dk wrote:
 `userTable.userid` = `userTable`.`userid`


Thank you Carsten. That was indeed the problem! Have a peaceful weekend.

-- 
Dotan Cohen

http://gibberish.co.il
http://what-is-what.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Update on inner join - looks good to me, where did I go wrong?

[Dotan Cohen]

Now that I've got the syntax right, MySQL is complaining that a field
does not exist, which most certainly does:

mysql UPDATE
-  `userTable`
- INNER JOIN `anotherTable`
-   ON `userTable.userid`=`anotherTable.userid`
- SET `userTable.someField`=Jimmy Page
- WHERE `userTable.someField`=Jim Morrison
-   AND `anotherTable.date`  NOW();
ERROR 1054 (42S22): Unknown column 'userTable.someField' in 'field list'
mysql
mysql SELECT count(someField) FROM userTable;
+---+
| count(someField) |
+---+
|  5076 |
+---+
1 row in set (0.00 sec)

mysql

What could be the issue here? Thanks!


-- 
Dotan Cohen

http://gibberish.co.il
http://what-is-what.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Update on inner join - looks good to me, where did I go wrong?

[Carsten Pedersen]

`userTable.userid` = `userTable`.`userid`

/ Carsten

On 09-09-2011 23:01, Dotan Cohen wrote:

Now that I've got the syntax right, MySQL is complaining that a field
does not exist, which most certainly does:

mysql  UPDATE
 -   `userTable`
 -  INNER JOIN `anotherTable`
 -ON `userTable.userid`=`anotherTable.userid`
 -  SET `userTable.someField`=Jimmy Page
 -  WHERE `userTable.someField`=Jim Morrison
 -AND `anotherTable.date`  NOW();
ERROR 1054 (42S22): Unknown column 'userTable.someField' in 'field list'
mysql
mysql  SELECT count(someField) FROM userTable;
+---+
| count(someField) |
+---+
|  5076 |
+---+
1 row in set (0.00 sec)

mysql

What could be the issue here? Thanks!




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: update and times

[Simcha Younger]

On Wed, 06 Oct 2010 17:48:55 -0400
kalin m ka...@el.net wrote:

 
 
 Simcha Younger wrote:

  executing this query didn't update the record.
 
  why?
  
  The two values you have here are equal:
  sample data : 12862162510269684
  query: where unix_time   12862162510269684
  and therefore the 'less than' query did not match that row.
 

 sorry...  not following  the value in the table was 
 12862162385941345. the time in the query was 12862162510269684.

Sorry, I misread your question.

did you check before you ran the query that this is the only matching record 
for your condition?
It is possible that the limit 1 is preventing the update because you have more 
than one record less than the timestamp in the query.

You might also want to add to your where condition:
AND `updated` = 0; so it will skip rows which have already been updated.

-- 
Simcha Younger sim...@syounger.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: update and times

[kalin m]

Simcha Younger wrote:

On Mon, 04 Oct 2010 16:11:08 -0400
kalin m ka...@el.net wrote:

  

  
what i'm trying to do is update the column only of one of those times 
isn't yet passed. and it works. except sometimes... 


like these 2 unix times:

this was in the table under unix time: 12862162385941345...

this 12862162510269684 got passed in the update command as in:

update the_table set updated = 1 where unix_time  12862162510269684 
limit 1;


executing this query didn't update the record.

why?


The two values you have here are equal:
sample data : 12862162510269684
query: where unix_time   12862162510269684
and therefore the 'less than' query did not match that row.

  
sorry...  not following  the value in the table was 
12862162385941345. the time in the query was 12862162510269684.


mysql select 12862162385941345  12862162510269684;
+---+
| 12862162385941345  12862162510269684 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

Re: update and times

[Simcha Younger]

On Mon, 04 Oct 2010 16:11:08 -0400
kalin m ka...@el.net wrote:

 

 
 what i'm trying to do is update the column only of one of those times 
 isn't yet passed. and it works. except sometimes... 
 
 like these 2 unix times:
 
 this was in the table under unix time: 12862162385941345...
 
 this 12862162510269684 got passed in the update command as in:
 
 update the_table set updated = 1 where unix_time  12862162510269684 
 limit 1;
 
 executing this query didn't update the record.
 
 why?
The two values you have here are equal:
sample data : 12862162510269684
query: where unix_time   12862162510269684
and therefore the 'less than' query did not match that row.


-- 
Simcha Younger sim...@syounger.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: update and times

[Gavin Towey]

Those unix_time values don't seem to correspond to the dates you have.

select NOW(), UNIX_TIMESTAMP(NOW());
+-+---+
| NOW()   | UNIX_TIMESTAMP(NOW()) |
+-+---+
| 2010-10-04 13:18:08 |1286223488 |
+-+---+

1286223428 vs 12862162510269684

Your value has far too many digits. That's also beyond the range of a 32 bit 
int.  Are you using BIGINT, or VARCHAR?

I suspect the issues is due because of something different about the values you 
have in your table.  Try posting the SHOW CREATE TABLE table \G output, and a 
sample INSERT statement to populate the table.  That way someone can try to 
reproduce the behavior you're seeing.




-Original Message-
From: kalin m [mailto:ka...@el.net]
Sent: Monday, October 04, 2010 1:11 PM
To: [MySQL]
Subject: update and times



hi all...

i'm doing tests with a table that gets updated based on random unix
times it contains. there is a column that has a bunch or random times
that look like:

+-+---+
 | date_time  | unix_time|
+-+---+
 | 2010-10-01 10:24:52 | 12859430921341418 |
 | 2010-10-01 21:18:13 | 12859822937839442 |
 | 2010-10-01 16:08:00 | 12859636809115039 |
 | 2010-10-01 19:47:43 | 12859768633824661 |
 | 2010-10-01 16:48:30 | 12859661104829142 |
 | 2010-10-01 15:25:37 | 12859611374324533 |
 | 2010-10-01 12:27:28 | 12859504483288358 |
+-+---+


what i'm trying to do is update the column only of one of those times
isn't yet passed. and it works. except sometimes...

like these 2 unix times:

this was in the table under unix time: 12862162385941345...

this 12862162510269684 got passed in the update command as in:

update the_table set updated = 1 where unix_time  12862162510269684
limit 1;

executing this query didn't update the record.

why?


thanks...



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com


This message contains confidential information and is intended only for the 
individual named.  If you are not the named addressee, you are notified that 
reviewing, disseminating, disclosing, copying or distributing this e-mail is 
strictly prohibited.  Please notify the sender immediately by e-mail if you 
have received this e-mail by mistake and delete this e-mail from your system. 
E-mail transmission cannot be guaranteed to be secure or error-free as 
information could be intercepted, corrupted, lost, destroyed, arrive late or 
incomplete, or contain viruses. The sender therefore does not accept liability 
for any loss or damage caused by viruses or errors or omissions in the contents 
of this message, which arise as a result of e-mail transmission. [FriendFinder 
Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: update and times

[kalin m]

right  the unix times in the example table were just that - examples 
from a few days ago...


the example with the query was a 'real one' something that happened today...

it's a 64 bit machine. the unix times are stored in a bigint column.  
the times in the column and the update statement are the same length...


here is the information for that column/table:

+---+-+--+-+---++
| Field | Type| Null | Key | Default   | 
Extra  |

+---+-+--+-+---++
| id| int(10) unsigned| NO   | PRI | NULL  | 
auto_increment |
| normal_time| datetime | NO   | |   
||
| unix_time| bigint(20) unsigned | NO   | UNI | 0 
||
| updated | tinyint(1)  | NO   | | 0 
||

+---+-+--+-+---++


at the end what is compared is two long int values - 12862162510269684 
and 12862162385941345 in the update query





Gavin Towey wrote:

Those unix_time values don't seem to correspond to the dates you have.

select NOW(), UNIX_TIMESTAMP(NOW());
+-+---+
| NOW()   | UNIX_TIMESTAMP(NOW()) |
+-+---+
| 2010-10-04 13:18:08 |1286223488 |
+-+---+

1286223428 vs 12862162510269684

Your value has far too many digits. That's also beyond the range of a 32 bit 
int.  Are you using BIGINT, or VARCHAR?

I suspect the issues is due because of something different about the values you 
have in your table.  Try posting the SHOW CREATE TABLE table \G output, and a 
sample INSERT statement to populate the table.  That way someone can try to 
reproduce the behavior you're seeing.




-Original Message-
From: kalin m [mailto:ka...@el.net]
Sent: Monday, October 04, 2010 1:11 PM
To: [MySQL]
Subject: update and times



hi all...

i'm doing tests with a table that gets updated based on random unix
times it contains. there is a column that has a bunch or random times
that look like:

+-+---+
 | date_time  | unix_time|
+-+---+
 | 2010-10-01 10:24:52 | 12859430921341418 |
 | 2010-10-01 21:18:13 | 12859822937839442 |
 | 2010-10-01 16:08:00 | 12859636809115039 |
 | 2010-10-01 19:47:43 | 12859768633824661 |
 | 2010-10-01 16:48:30 | 12859661104829142 |
 | 2010-10-01 15:25:37 | 12859611374324533 |
 | 2010-10-01 12:27:28 | 12859504483288358 |
+-+---+


what i'm trying to do is update the column only of one of those times
isn't yet passed. and it works. except sometimes...

like these 2 unix times:

this was in the table under unix time: 12862162385941345...

this 12862162510269684 got passed in the update command as in:

update the_table set updated = 1 where unix_time  12862162510269684
limit 1;

executing this query didn't update the record.

why?


thanks...



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com


This message contains confidential information and is intended only for the 
individual named.  If you are not the named addressee, you are notified that 
reviewing, disseminating, disclosing, copying or distributing this e-mail is 
strictly prohibited.  Please notify the sender immediately by e-mail if you 
have received this e-mail by mistake and delete this e-mail from your system. 
E-mail transmission cannot be guaranteed to be secure or error-free as 
information could be intercepted, corrupted, lost, destroyed, arrive late or 
incomplete, or contain viruses. The sender therefore does not accept liability 
for any loss or damage caused by viruses or errors or omissions in the contents 
of this message, which arise as a result of e-mail transmission. [FriendFinder 
Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com
  


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Update Table

[Nigel Wood]

On Mon, 2010-09-27 at 11:25 +0100, Willy Mularto wrote:
 Hi,
 I work on MySQL 5 with PHP 5 and use Apache 2 as the webserver. I have a 
 simple query that searches matched row id and update the field via  HTTP GET 
 query. On a low load it succeed update the row. But on high traffic sometimes 
 it failed to update some  rows. No errors return by the script. What usually 
 cause this and how to solve this problem? Thanks
 
 
1.) Are you sure the script is executed under those conditions? Is
Apache refusing the request because to many children have been forked?

2.) Are you sure the script will report if MySQL fails with too many
connections?

 
 sangprabv
 sangpr...@gmail.com
 http://www.petitiononline.com/froyo/
 
 
 



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Update record count

[Johan De Meersman]

On Fri, Sep 17, 2010 at 3:51 AM, Shawn Green (MySQL) 
shawn.l.gr...@oracle.com wrote:


 So if 10 rows of A match your conditions, 1 row from B match your
 conditions, and 10 rows from C match your conditions, then this query
 produces 10*1*10 total row combinations.


Umm. It's friday, so I may be rather off it, too, but aren't the conditions
inclusive ? They're AND, not OR. A Cartesian join on a(10 rows), b(1 row)
and c(10 rows) would produce as you say.

I would say that the number of rows is the number where
 * c.f3 is x, y or z
 * AND where b.f4 = yen
 * FROM the set produced by the join condition.


Check the number of rows in c that match f3 in (x, y, z).
Match those with rows in b as per join. This will yield the same or less
rows.
Filter those that match b.f4 = yen. This again will yield same or less.
Match those with a as per join. This, too, will yield same or less, and will
be your final number of rows.


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

RE: Update record count

[Jerry Schwartz]

-Original Message-
From: Shawn Green (MySQL) [mailto:shawn.l.gr...@oracle.com]
Sent: Thursday, September 16, 2010 9:51 PM
To: Jerry Schwartz
Cc: mysql@lists.mysql.com
Subject: Re: Update record count

On 9/16/2010 5:12 PM, Jerry Schwartz wrote:
 I should be able to figure this out, but I'm puzzled. Here's a simplified
 example:

 UPDATE a JOIN b ON a.kb = b.kb JOIN c ON b.kc = c.kc
 SET a.f1 = NOW(),
   b.f2 = NOW()
 WHERE c.f3 IN ('x', 'y', 'z')
 AND b.f4 = 'yen';

 It seems to me that if there are 3 rows found in `c` that match a total of 
 10
 rows in `a` that each, in turn, matches 1 row in `b`, then the total number
of
 qualifying would be 10 + 10 - 20.

 That should also be the number of rows changed.

 Somehow the numbers reported by MySQL don't seem to match up in my real 
 case,
 even though the results seem to be what I want. The numbers reported were 
 way
 too high, and I don't understand it.

 I can supply more information, if necessary, but have I gone off the rails
 somehow?


Look at this like the database sees the problem:

SELECT a.f1, b.f2, c.f3, b.f4
FROM a JOIN b ON a.kb = b.kb JOIN c ON b.kc = c.kc
WHERE c.f3 IN ('x', 'y', 'z')
AND b.f4 = 'yen';


What you should be able to notice:

* Each matching row from a is combined with each matching row from b

* Each combination of (a,b) rows is combined with each matching row from c

So if 10 rows of A match your conditions, 1 row from B match your
conditions, and 10 rows from C match your conditions, then this query
produces 10*1*10 total row combinations.

That should explain why your numbers are higher than expected.

[JS] Thanks --- I think. I realize that left out some crucial details, and 
also made a mistake when posing my example. Let me use some more suggestive 
field names.

UPDATE `prod` JOIN `prod_price` ON `prod`.`prod_id` = `prod_price`.`prod_id`
   JOIN `pub` ON `prod`.`pub_id` = `pub`.`pub_id`
SET `prod`.`prod_changed` = 1,
   `prod_price`.`prod_price_tax` = .7
WHERE `pub`.`pub_id `IN ('x', 'y', 'z')
   AND `prod_price`.`prod_price_curr` = 'yen';

- `prod`.`prod_id` is a unique key for `prod`, but is not unique in 
`prod_price`
- `pub`.`pub_id` is a unique key for `pub`, but is not unique in `prod`

Assume that

- For `pub`.`pub_id` = 'x' we match 3 rows in `prod`:
* `prod`.`prod_id` = 'a'
* `prod`.`prod_id` = 'b'
* `prod`.`prod_id` = 'c'

- For `pub`.`pub_id` = 'y' we match 5 rows in `prod`:
* `prod`.`prod_id` = 'm'
* `prod`.`prod_id` = 'n'
* `prod`.`prod_id` = 'o'

- For `pub`.`pub_id` = 'z' we match 2 rows in `prod`:
* `prod`.`prod_id` = 'q'
* `prod`.`prod_id` = 'r

That should give us a total of 10 rows for this part of our search. Now assume 
that

- For each value of `prod`.`prod_id` there are 2 matching rows in 
`prod_price`, BUT for each value of `prod`.`prod_price` only one row matches 
the WHERE condition `prod_price`.`prod_price_curr` = 'yen'. For that reason we 
have a 1:1 relationship between the rows in `prod` and the remaining rows from 
`prod_price`.

So are you saying that the count of rows selected would be

3 x 10 x 10

even though the actual number of rows we're working on is only 10 (10 rows 
from `prod` and 1 row from `prod_price` for each row from `prod`)?

I know that when it reports the number of rows affected, it adds up the 
numbers from each table; but a SELECT based upon the criteria used in my 
UPDATE reports the number of records I would expect.

Regards,
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: Update query problem

[Travis Ard]

Try using the IS NULL operator instead of !

-Travis

-Original Message-
From: Andy Wallace [mailto:awall...@ihouseweb.com] 
Sent: Thursday, September 16, 2010 10:47 AM
To: mysql@lists.mysql.com
Subject: Update query problem

So I'm having a problem with an update query. I have three tables:

Table: A
Columns:   acnt, name, company, email, domain

Table: AM
Columns:   acnt, m_id

Table: M
Columns:   m_id, name, company, email, domain

and I want to conditionally update the columns in one to values from the
other. i.e., I want to put the value of A.name into M.name, but only
if M.name is currently NULL, AND A.name has a usable value (not an empty
string).

This is what I came up with, but it doesn't work - it only replaces the
values where the column in M is not null.


update  A
join   AM on A.acnt = AM.acnt
joinM on AM.m_id = M.m_id
SET M.name= IF( (!M.nameAND A.name != ''),A.name,M.name),
 M.company = IF( (!M.company AND A.company != ''), A.company,
M.company),
 M.email   = IF( (!M.email   AND A.email != ''),   A.email,   M.email),
 M.domain  = IF( (!M.domain  AND A.domain != ''),  A.domain,  M.domain)

Any thoughts?

THanks,
andy

-- 
Andy Wallace
iHOUSEweb, Inc.
awall...@ihouseweb.com
(866) 645-7700 ext 219
--
There are two ways to build software:
Make it so simple that there are obviously no bugs,
or make it so complex that there are no obvious bugs.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=travis_...@hotmail.com



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Update record count

[Shawn Green (MySQL)]

On 9/16/2010 5:12 PM, Jerry Schwartz wrote:
I should be able to figure this out, but I'm puzzled. Here's a simplified 
example:


UPDATE a JOIN b ON a.kb = b.kb JOIN c ON b.kc = c.kc
SET a.f1 = NOW(),
  b.f2 = NOW()
WHERE c.f3 IN ('x', 'y', 'z')
AND b.f4 = 'yen';

It seems to me that if there are 3 rows found in `c` that match a total of 10 
rows in `a` that each, in turn, matches 1 row in `b`, then the total number of 
qualifying would be 10 + 10 - 20.


That should also be the number of rows changed.

Somehow the numbers reported by MySQL don't seem to match up in my real case, 
even though the results seem to be what I want. The numbers reported were way 
too high, and I don't understand it.


I can supply more information, if necessary, but have I gone off the rails 
somehow?




Look at this like the database sees the problem:

SELECT a.f1, b.f2, c.f3, b.f4
FROM a JOIN b ON a.kb = b.kb JOIN c ON b.kc = c.kc
WHERE c.f3 IN ('x', 'y', 'z')
AND b.f4 = 'yen';


What you should be able to notice:

* Each matching row from a is combined with each matching row from b

* Each combination of (a,b) rows is combined with each matching row from c

So if 10 rows of A match your conditions, 1 row from B match your 
conditions, and 10 rows from C match your conditions, then this query 
produces 10*1*10 total row combinations.


That should explain why your numbers are higher than expected.

Regards,
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: update replace() regex

[Dan Nelson]

In the last episode (Jul 08), Egor Shevtsov said:
 I wonder if possible at all to use replace() together with regex in update
 statement.  I want to be able to update a field of string values and
 replace a 'dash' character with a space in the string.  Something like:
 UPDATE table SET column = replace(column, regex '%-%', ' ') where id = xxx;

You don't need a regex for that.  REPLACE(column, '-', ' ') should do what
you want.

-- 
Dan Nelson
dnel...@allantgroup.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: update replace() regex

[Egor Shevtsov]

Thanks Dan,
I tested it just now. It works perfectly.

Dan Nelson wrote:

In the last episode (Jul 08), Egor Shevtsov said:
  

I wonder if possible at all to use replace() together with regex in update
statement.  I want to be able to update a field of string values and
replace a 'dash' character with a space in the string.  Something like:
UPDATE table SET column = replace(column, regex '%-%', ' ') where id = xxx;



You don't need a regex for that.  REPLACE(column, '-', ' ') should do what
you want.

  

Re: UPDATE and simultaneous SELECT ... similar to RETURNING?

[Baron Schwartz]

Dante,

On Tue, Dec 22, 2009 at 3:53 PM, Dante Lorenso da...@lorenso.com wrote:
 All,

 There was a feature of another DB that I have grown extremely accustomed to
 and would like to find the equivalent in MySQL:

 UPDATE mytable SET
  mycolumn = mycolumn + 1
 WHERE mykey = 'dante'
 RETURNING mycolumn;

I know what you're talking about.  It doesn't exist in MySQL and I
would not expect it to be added soon.  (Probably not ever, but that's
just a guess.)

- Baron

-- 
Baron Schwartz
Percona Inc: Services and Support for MySQL
http://www.percona.com/

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Update Doesn't Update!

[carsten]

On Fri, 11 Dec 2009 04:38:01 -0500, Victor Subervi

victorsube...@gmail.com

wrote:

 Hi;

 

 mysql update products set sizes=('Small', 'Large') where ID=0;

 Query OK, 0 rows affected, 1 warning (0.00 sec)

 Rows matched: 1  Changed: 0  Warnings: 1



Warnings: 1



do a SHOW WARNINGS immediately after you execute the stmt.



/ Carsten





 

 mysql select sizes, colorsShadesNumbersShort from products where ID=0;

 +---+--+

 | sizes | colorsShadesNumbersShort |

 +---+--+

 |   |  |

 +---+--+

 1 row in set (0.00 sec)

 

 Huh?

 TIA,

 Victor

 

 

 !DSPAM:451,4b221339930275276717544!

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Update Doesn't Update!

[Jørn Dahl-Stamnes]

On Friday 11 December 2009 10:38, Victor Subervi wrote:
 Hi;

 mysql update products set sizes=('Small', 'Large') where ID=0;
 Query OK, 0 rows affected, 1 warning (0.00 sec)
 Rows matched: 1  Changed: 0  Warnings: 1
  
Look at the message, 0 rows changed and 1 warning.
You cannot have ID=0 if ID is an index.

-- 
Jørn Dahl-Stamnes
homepage: http://www.dahl-stamnes.net/dahls/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Update Doesn't Update!

[Victor Subervi]

On Fri, Dec 11, 2009 at 4:48 AM, Jørn Dahl-Stamnes
sq...@dahl-stamnes.netwrote:

 On Friday 11 December 2009 10:38, Victor Subervi wrote:
  Hi;
 
  mysql update products set sizes=('Small', 'Large') where ID=0;
  Query OK, 0 rows affected, 1 warning (0.00 sec)
  Rows matched: 1  Changed: 0  Warnings: 1
   
 Look at the message, 0 rows changed and 1 warning.
 You cannot have ID=0 if ID is an index.


Yikes! Then how do I update this table? I will need to update every variable
*except* the ID, which is the primary key and an auto_increment.
V

Re: Update Doesn't Update!

[Victor Subervi]

On Fri, Dec 11, 2009 at 4:48 AM, Jørn Dahl-Stamnes
sq...@dahl-stamnes.netwrote:

 On Friday 11 December 2009 10:38, Victor Subervi wrote:
  Hi;
 
  mysql update products set sizes=('Small', 'Large') where ID=0;
  Query OK, 0 rows affected, 1 warning (0.00 sec)
  Rows matched: 1  Changed: 0  Warnings: 1
   
 Look at the message, 0 rows changed and 1 warning.
 You cannot have ID=0 if ID is an index.


Yikes! Then how do I update this table? I will need to update every variable
*except* the ID, which is the primary key and an auto_increment.
V

Re: Update Doesn't Update!

[Martijn Tonies]

mysql update products set sizes=('Small', 'Large') where ID=0;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 1

 
Look at the message, 0 rows changed and 1 warning.
You cannot have ID=0 if ID is an index.


Are you serious??

With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!

Database questions? Check the forum:
http://www.databasedevelopmentforum.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: Update Doesn't Update!

[misiaQ]

If ID column is primary key and auto increment as you said, it cant be equal
to zero.
You got a query which reads:
UPDATE columns WHERE false

There is no chance for any updates.
http://dev.mysql.com/doc/refman/5.0/en/update.html

Regards,
m

-Original Message-
From: Victor Subervi [mailto:victorsube...@gmail.com] 
Sent: 11 December 2009 10:06
Cc: mysql@lists.mysql.com
Subject: Re: Update Doesn't Update!

On Fri, Dec 11, 2009 at 4:48 AM, Jørn Dahl-Stamnes
sq...@dahl-stamnes.netwrote:

 On Friday 11 December 2009 10:38, Victor Subervi wrote:
  Hi;
 
  mysql update products set sizes=('Small', 'Large') where ID=0;
  Query OK, 0 rows affected, 1 warning (0.00 sec)
  Rows matched: 1  Changed: 0  Warnings: 1
   
 Look at the message, 0 rows changed and 1 warning.
 You cannot have ID=0 if ID is an index.


Yikes! Then how do I update this table? I will need to update every variable
*except* the ID, which is the primary key and an auto_increment.
V


--
Szef przynudza? Zagraj sobie!
Sprawdz  http://link.interia.pl/f24e4


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Update Doesn't Update!

[Victor Subervi]

On Fri, Dec 11, 2009 at 4:43 AM, cars...@bitbybit.dk wrote:


 On Fri, 11 Dec 2009 04:38:01 -0500, Victor Subervi
 victorsube...@gmail.com
 wrote:
  Hi;
 
  mysql update products set sizes=('Small', 'Large') where ID=0;
  Query OK, 0 rows affected, 1 warning (0.00 sec)
  Rows matched: 1  Changed: 0  Warnings: 1

 Warnings: 1

 do a SHOW WARNINGS immediately after you execute the stmt.


mysql update products set sizes=('Small', 'Large') where SKU='prodSKU1';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 1

mysql show warnings;
+-+--++
| Level   | Code | Message|
+-+--++
| Warning | 1265 | Data truncated for column 'sizes' at row 1 |
+-+--++
1 row in set (0.00 sec)


What do? How do I enter multiple values?
TIA,
V

Re: Update Doesn't Update!

[carsten]

On Fri, 11 Dec 2009 10:48:59 +0100, Jørn Dahl-Stamnes

sq...@dahl-stamnes.net wrote:

 On Friday 11 December 2009 10:38, Victor Subervi wrote:

 Hi;



 mysql update products set sizes=('Small', 'Large') where ID=0;

 Query OK, 0 rows affected, 1 warning (0.00 sec)

 Rows matched: 1  Changed: 0  Warnings: 1

   

 Look at the message, 0 rows changed and 1 warning.

 You cannot have ID=0 if ID is an index.



Then how did he manage to get a matched row?



Of course you can have ID=0.



/ Carsten



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Update Doesn't Update!

[carsten]

On Fri, 11 Dec 2009 05:09:52 -0500, Victor Subervi

victorsube...@gmail.com

wrote:



 mysql update products set sizes=('Small', 'Large') where

SKU='prodSKU1';

 Query OK, 0 rows affected, 1 warning (0.00 sec)

 Rows matched: 1  Changed: 0  Warnings: 1

 

 mysql show warnings;

 +-+--++

 | Level   | Code | Message|

 +-+--++

 | Warning | 1265 | Data truncated for column 'sizes' at row 1 |

 +-+--++

 1 row in set (0.00 sec)

 

 

 What do? How do I enter multiple values?



Impossible to say, until you let us know how you defined the column in the

first place...



/ Carsten



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Update Doesn't Update!

[Mark Goodge]

Jørn Dahl-Stamnes wrote:

On Friday 11 December 2009 10:38, Victor Subervi wrote:

Hi;

mysql update products set sizes=('Small', 'Large') where ID=0;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 1

  
Look at the message, 0 rows changed and 1 warning.
You cannot have ID=0 if ID is an index.


You can, but not if it's an auto-increment field.

Mark



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Update Doesn't Update!

[Yang Wang]

first desc products

or try to

update products set sizes='Small' where
SKU='prodSKU1';




Best Regards!
Yang Wang
 
Tel.: 0769-21687397
Fax.: 0769-21685577
Email: yw...@lfm-agile.com.hk
- Original Message - 
From: cars...@bitbybit.dk
To: Victor Subervi victorsube...@gmail.com
Cc: mysql@lists.mysql.com
Sent: Friday, December 11, 2009 6:13 PM
Subject: Re: Update Doesn't Update!


 
 On Fri, 11 Dec 2009 05:09:52 -0500, Victor Subervi
 victorsube...@gmail.com
 wrote:
 
 mysql update products set sizes=('Small', 'Large') where
 SKU='prodSKU1';
 Query OK, 0 rows affected, 1 warning (0.00 sec)
 Rows matched: 1  Changed: 0  Warnings: 1
 
 mysql show warnings;
 +-+--++
 | Level   | Code | Message|
 +-+--++
 | Warning | 1265 | Data truncated for column 'sizes' at row 1 |
 +-+--++
 1 row in set (0.00 sec)
 
 
 What do? How do I enter multiple values?
 
 Impossible to say, until you let us know how you defined the column in the
 first place...
 
 / Carsten
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=yw...@lfm-agile.com.hk

Re: Update Doesn't Update!

[Chris Knipe]

Quoting cars...@bitbybit.dk:


Of course you can have ID=0.


Definately agree

mysql DESCRIBE test;
+-+-+--+-+-++
| Field   | Type| Null | Key | Default | Extra  |
+-+-+--+-+-++
| autoinc | int(11) | NO   | PRI | NULL| auto_increment |
| value   | varchar(10) | NO   | | NULL||
+-+-+--+-+-++
2 rows in set (0.00 sec)

mysql SELECT * FROM test;
+-++
| autoinc | value  |
+-++
|   0 | 1234567890 |
+-++
1 row in set (0.00 sec)

mysql UPDATE test SET value='a' WHERE autoinc='0';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql SELECT * FROM test;
+-+---+
| autoinc | value |
+-+---+
|   0 | a |
+-+---+
1 row in set (0.00 sec)

However, what I believe the problem is:
mysql UPDATE test set value='12345678901' WHERE autoinc='0';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql SELECT * FROM test;
+-++
| autoinc | value  |
+-++
|   0 | 1234567890 |
+-++
1 row in set (0.00 sec)

the value of value is too long for the varchar(10) in the table.  It  
thus generates the warning, and truncate the field.


The poster's table needs to be updated therefor to accept longer  
variables in the sizes column.



--

Regards,
Chris.



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Update Doesn't Update!

[Victor Subervi]

On Fri, Dec 11, 2009 at 5:13 AM, cars...@bitbybit.dk wrote:


 On Fri, 11 Dec 2009 05:09:52 -0500, Victor Subervi
 victorsube...@gmail.com
 wrote:

  mysql update products set sizes=('Small', 'Large') where
 SKU='prodSKU1';
  Query OK, 0 rows affected, 1 warning (0.00 sec)
  Rows matched: 1  Changed: 0  Warnings: 1
 
  mysql show warnings;
  +-+--++
  | Level   | Code | Message|
  +-+--++
  | Warning | 1265 | Data truncated for column 'sizes' at row 1 |
  +-+--++
  1 row in set (0.00 sec)
 
 
  What do? How do I enter multiple values?

 Impossible to say, until you let us know how you defined the column in the
 first place...


Sorry. It's an enum of which the elements I am trying to add into a row are
elements of the same enum; that is, a subset.
V

Re: Update Doesn't Update!

[carsten]

On Fri, 11 Dec 2009 05:28:41 -0500, Victor Subervi

victorsube...@gmail.com

wrote:

 On Fri, Dec 11, 2009 at 5:13 AM, cars...@bitbybit.dk wrote:

 



 On Fri, 11 Dec 2009 05:09:52 -0500, Victor Subervi

 victorsube...@gmail.com

 wrote:



  mysql update products set sizes=('Small', 'Large') where

 SKU='prodSKU1';

  Query OK, 0 rows affected, 1 warning (0.00 sec)

  Rows matched: 1  Changed: 0  Warnings: 1

 

  mysql show warnings;

  +-+--++

  | Level   | Code | Message|

  +-+--++

  | Warning | 1265 | Data truncated for column 'sizes' at row 1 |

  +-+--++

  1 row in set (0.00 sec)

 

 

  What do? How do I enter multiple values?



 Impossible to say, until you let us know how you defined the column in

 the

 first place...



 

 Sorry. It's an enum of which the elements I am trying to add into a row

are

 elements of the same enum; that is, a subset.



You're using the wrong type. RTFM re. the difference between enums and

sets. 



/ Carsten

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Update Doesn't Update!

[Victor Subervi]

On Fri, Dec 11, 2009 at 5:33 AM, cars...@bitbybit.dk wrote:


 On Fri, 11 Dec 2009 05:28:41 -0500, Victor Subervi
 victorsube...@gmail.com
 wrote:
  On Fri, Dec 11, 2009 at 5:13 AM, cars...@bitbybit.dk wrote:
 
 
  On Fri, 11 Dec 2009 05:09:52 -0500, Victor Subervi
  victorsube...@gmail.com
  wrote:
 
   mysql update products set sizes=('Small', 'Large') where
  SKU='prodSKU1';
   Query OK, 0 rows affected, 1 warning (0.00 sec)
   Rows matched: 1  Changed: 0  Warnings: 1
  
   mysql show warnings;
   +-+--++
   | Level   | Code | Message|
   +-+--++
   | Warning | 1265 | Data truncated for column 'sizes' at row 1 |
   +-+--++
   1 row in set (0.00 sec)
  
  
   What do? How do I enter multiple values?
 
  Impossible to say, until you let us know how you defined the column in
  the
  first place...
 
 
  Sorry. It's an enum of which the elements I am trying to add into a row
 are
  elements of the same enum; that is, a subset.

 You're using the wrong type. RTFM re. the difference between enums and
 sets.


k. Thanks,
V

Re: Update Doesn't Update!

[Johan De Meersman]

On Fri, Dec 11, 2009 at 11:19 AM, Mark Goodge m...@good-stuff.co.uk wrote:

 Jørn Dahl-Stamnes wrote:

 On Friday 11 December 2009 10:38, Victor Subervi wrote:

 Hi;

 mysql update products set sizes=('Small', 'Large') where ID=0;
 Query OK, 0 rows affected, 1 warning (0.00 sec)
 Rows matched: 1  Changed: 0  Warnings: 1

  
 Look at the message, 0 rows changed and 1 warning.
 You cannot have ID=0 if ID is an index.


 You can, but not if it's an auto-increment field.


Also, not *entirely* correct, although you have to jump through a few hoops:
it can occur if the field was changed to auto_increment *after* the 0 was
put in there.

Yes, I inherited a database like that once, and yes, it fucks up your day.


 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be

Re: Update Doesn't Update!

[Victor Subervi]

On Fri, Dec 11, 2009 at 8:43 AM, Johan De Meersman vegiv...@tuxera.bewrote:

 On Fri, Dec 11, 2009 at 11:19 AM, Mark Goodge m...@good-stuff.co.uk
 wrote:

  Jørn Dahl-Stamnes wrote:
 
  On Friday 11 December 2009 10:38, Victor Subervi wrote:
 
  Hi;
 
  mysql update products set sizes=('Small', 'Large') where ID=0;
  Query OK, 0 rows affected, 1 warning (0.00 sec)
  Rows matched: 1  Changed: 0  Warnings: 1
 
   
  Look at the message, 0 rows changed and 1 warning.
  You cannot have ID=0 if ID is an index.
 
 
  You can, but not if it's an auto-increment field.
 

 Also, not *entirely* correct, although you have to jump through a few
 hoops:
 it can occur if the field was changed to auto_increment *after* the 0 was
 put in there.

 Yes, I inherited a database like that once, and yes, it fucks up your day.


I'm lost. I set up this database originally with auto_increment and the
first value was 0. I thought that was always the case. Is there a problem
here?
V

Re: Update Doesn't Update!

[Johan De Meersman]

On Fri, Dec 11, 2009 at 6:40 PM, Victor Subervi victorsube...@gmail.comwrote:


 I'm lost. I set up this database originally with auto_increment and the
 first value was 0. I thought that was always the case. Is there a problem
 here?


Yes, that should not have happened. For autoincrement fields, both NULL and
0 are magic values to get the next number from the sequence at insert
time.

Re: update fields with a prefix - ?? how to

[Johan De Meersman]

Something in the ilk of
update *table* set *field* = concat(prefix_, *field*) where *condition *
should do the trick.

On Mon, Oct 19, 2009 at 4:56 PM, lejeczek pelj...@yahoo.co.uk wrote:

 dear all, a novice here
 quickie regarding query syntax - is it possible to take fields values from
 one column
 and update the same column with new values like this: prefix_OldValue
 column: one, two, three - column: prefix_one, prefix_two, ...
 can this be done with one query and with on use of abstractions, no php or
 similar scripting techincs?

 cheers, lejeczek

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be




-- 
That which does not kill you was simply not permitted to do so for the
purposes of the plot.

Re: update client

[Walter Heck - OlinData.com]

Check out replication.

On Sun, Aug 23, 2009 at 09:00, m. zamil mza...@saudi.net.sa wrote:

 Hello all,
 due to connection state, I need to keep an updated copy of the database on
 the client.
 How can I accomplish this?
 TIA
 Mos




-- 
Walter Heck, Engineer @ Open Query (http://openquery.com)
Affordable Training and ProActive Support for MySQL  related technologies

Follow our blog at http://openquery.com/blog/
OurDelta: free enhanced builds for MySQL @ http://ourdelta.org

Re: Update Syntax

[Michael Dykman]

from: http://dev.mysql.com/doc/refman/5.1/en/insert.html:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
SET col_name={expr | DEFAULT}, ...
[ ON DUPLICATE KEY UPDATE
  col_name=expr
[, col_name=expr] ... ]


The ON DUPLICATE KEY predicate tells you that if you create a unique
key for when ever your 'search' criteria is, you can get this
behaviour like so:

INSERT INTO mytable SET col1 = val1, ...  ON DUPLIATE KEY UPDATE

 - michael dykman

On Sun, Jul 26, 2009 at 1:11 PM, Victor Subervivictorsube...@gmail.com wrote:
 Hi;
 I would like to test the following:

 update maps set map where site=mysite;

 to see if there is such an entry in maps. If there is, then update. If there
 is not, then I would like to execute an insert statement. How do I do that?
 TIA,
 Victor




-- 
 - michael dykman
 - mdyk...@gmail.com

Don’t worry about people stealing your ideas. If they’re any good,
you’ll have to ram them down their throats!

   Howard Aiken

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Update Syntax

[Darryle Steplight]

Hi Vicor,
Look into INSERT ON DUPLICATE or REPLACE statements. You need to
have a primary key or unique key for these too work.

On Sun, Jul 26, 2009 at 1:11 PM, Victor Subervivictorsube...@gmail.com wrote:
 Hi;
 I would like to test the following:

 update maps set map where site=mysite;

 to see if there is such an entry in maps. If there is, then update. If there
 is not, then I would like to execute an insert statement. How do I do that?
 TIA,
 Victor




-- 
A: It reverses the normal flow of conversation.
Q: What's wrong with top-posting?
A: Top-posting.
Q: What's the biggest scourge on plain text email discussions?

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Update Syntax

[Victor Subervi]

Perfect. Thank you.
Victor

On Sun, Jul 26, 2009 at 2:18 PM, Darryle Steplight dstepli...@gmail.comwrote:

 Hi Vicor,
Look into INSERT ON DUPLICATE or REPLACE statements. You need to
 have a primary key or unique key for these too work.

 On Sun, Jul 26, 2009 at 1:11 PM, Victor Subervivictorsube...@gmail.com
 wrote:
  Hi;
  I would like to test the following:
 
  update maps set map where site=mysite;
 
  to see if there is such an entry in maps. If there is, then update. If
 there
  is not, then I would like to execute an insert statement. How do I do
 that?
  TIA,
  Victor
 



 --
 A: It reverses the normal flow of conversation.
 Q: What's wrong with top-posting?
 A: Top-posting.
 Q: What's the biggest scourge on plain text email discussions?

RE: Update email address domain

[Nathan Sullivan]

John,

I think this should work:

UPDATE members SET email=REPLACE(email, SUBSTRING(email,INSTR(email,'@')+1), 
'Thanks_in_advance.com.com')


Regards,
Nathan

-Original Message-
From: John Furlong [mailto:john.furl...@rakutenusa.com] 
Sent: Monday, June 29, 2009 12:54 PM
To: mysql@lists.mysql.com
Subject: Update email address domain

I'm trying to mask the email addresses for a development database. I need to 
make all of the domains exactly the same. What is the best way to do this? We 
have about 67000 distinct domains.

I was able to use substring to get the list of domains, but am not sure how to 
turn that into an update statement

SELECT SUBSTRING(email,INSTR(email,'@')+1) AS domain FROM members limit 5;

+--+
| domain   |
+--+
| aol.com  |
| verizon.net  |
| netzero.com  |
| yahoo.com|
| comcast.net  |
+--+
5 rows in set (0.00 sec)

So the full email address will end up as
b...@thanks_in_advance.commailto:b...@thanks_in_advance.com
j...@thanks_in_advance.com
jack@ Thanks_in_advance.commailto:%20thanks_in_adva...@netzero.com
a...@thanks_in_advance.commailto:a...@thanks_in_advance.com
j...@thanks_in_advance.com




John F

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: Update email address domain

[John Furlong]

Nathan,

That was exactly what I was looking for, thanks for your help.

John

-Original Message-
From: Nathan Sullivan [mailto:nsulli...@cappex.com] 
Sent: Monday, June 29, 2009 2:55 PM
To: John Furlong; mysql@lists.mysql.com
Subject: RE: Update email address domain

John,

I think this should work:

UPDATE members SET email=REPLACE(email, SUBSTRING(email,INSTR(email,'@')+1), 
'Thanks_in_advance.com.com')


Regards,
Nathan

-Original Message-
From: John Furlong [mailto:john.furl...@rakutenusa.com] 
Sent: Monday, June 29, 2009 12:54 PM
To: mysql@lists.mysql.com
Subject: Update email address domain

I'm trying to mask the email addresses for a development database. I need to 
make all of the domains exactly the same. What is the best way to do this? We 
have about 67000 distinct domains.

I was able to use substring to get the list of domains, but am not sure how to 
turn that into an update statement

SELECT SUBSTRING(email,INSTR(email,'@')+1) AS domain FROM members limit 5;

+--+
| domain   |
+--+
| aol.com  |
| verizon.net  |
| netzero.com  |
| yahoo.com|
| comcast.net  |
+--+
5 rows in set (0.00 sec)

So the full email address will end up as
b...@thanks_in_advance.commailto:b...@thanks_in_advance.com
j...@thanks_in_advance.com
jack@ Thanks_in_advance.commailto:%20thanks_in_adva...@netzero.com
a...@thanks_in_advance.commailto:a...@thanks_in_advance.com
j...@thanks_in_advance.com




John F


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Update with value form another table

[Perrin Harkins]

On Fri, May 22, 2009 at 1:22 PM, Chris W 4rfv...@cox.net wrote:
 Of course study to profile is a one to many relationship.  How do I run an
 update to set  p.`Date` equal to s.`Date`?

This is covered in the docs for UPDATE.  Read that and come back if
you're stuck.

- Perrin

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: UPDATE ... where max(datecolumn)

[Baron Schwartz]

Is there ever more than one order per day?

I also think you might mean most current order per product, but you
didn't say that.

On Tue, Jan 27, 2009 at 8:38 AM, Adria Stembridge
adrya.stembri...@gmail.com wrote:
 I have a table like this:

 ID   PRODUCT  DATEORDERED
 152005-02-18
 222008-03-13
 322008-11-21
 462009-01-21

 A field has been added for current order to this table:

 ID   PRODUCT  DATEORDERED FLAGCURRENT

 I need to update all records in the table (about 400,000) so that the most
 current order has FLAGCURRENT=1 (else 0).   Is there an optimal way of doing
 this with a single query?




-- 
Baron Schwartz, Director of Consulting, Percona Inc.
Our Blog: http://www.mysqlperformanceblog.com/
Our Services: http://www.percona.com/services.html

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: UPDATE jujitsu?

[Jim Lyons]

How about this?

update t set onoffflag = if (name  'China', onoffflag, ( if (location =
'Table', 1, 0)  ));

This leaves any onoffflag untouched if name is not China, which I assume you
wanted to do.



On Thu, Jan 8, 2009 at 2:18 PM, Christoph Boget
christoph.bo...@gmail.comwrote:

 Consider the folowing dataset:

 +++-+-+---+
 | id| Name | Location| OnOffFlag |  Description |
 +++-+-+---+
 |  1 | Paper| Cabinet |  0 | Blah|
 |  2 | Plastic   | Cabinet |  0 | Blah|
 |  3 | China| Cabinet |  1 | Blah|
 |  4 | Glass| Cabinet |  0 | Blah|
 |  5 | China| Table |  0 | Blah|
 |  6 | China| Cabinet |  1 | Blah|
 +++-+-+---+

 Is there a way to, using a single query, set the OnOffFlag to 1 for
 the record that matches [Name=China AND Location=Table] at the same
 time setting the OnOffFlag to 0 for records that match [Name=China AND
 Location!=Table]?  I know I can do it in 2 queries but I am curious to
 know if it can actually be done in 1.

 thnx,
 Chris

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=jlyons4...@gmail.com




-- 
Jim Lyons
Web developer / Database administrator
http://www.weblyons.com

Re: Update Problem when ORing w/ Long.MIN_VALUE

[Daniel Doubleday]

For the curious: As usual select is not broken.

Lesson learned: Always watch out for warnings: 
http://bugs.mysql.com/bug.php?id=41007

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Update Problem when ORing w/ Long.MIN_VALUE

[Daniel Doubleday]

Hi Gautam

nope yours is not a bug. That's all fine. Hex numbers are 64 bit  
unsigned.

So for -1 you have to insert cast(0x as signed).

Cheers,
Daniel


Hi Daniel,

I can see the problem without using update. However, I am a newbie  
at

mysql,
so can't say for certain if it's a bug:

mysql drop table if exists foo;
mysql create table foo (id int signed, val bigint signed);
mysql insert into foo values (0x, 0x), (-1,  
-1);

mysql select hex(id), hex(val) from foo;

+--+--+
| hex(id)  | hex(val) |
+--+--+
| 7FFF | 7FFF |
|  |  |
+--+--+
2 rows in set (0.00 sec)


Regards
Gautam

Daniel Doubleday wrote:
 Hi everybody -

 I'm experiencing some really weird update behaviour (mysql 5.0) when
 or'ing results from subselects using Long.MIN_VALUE.
 But before I post a bug report I wanted to ask if I'm missing  
something.



 drop table if exists foo;
 drop table if exists bar;

 create table foo (fooid int, fooval bigint);
 create table bar (barid int, barval bigint);

 insert into foo values (1, null), (2, null);
 insert into bar values (1, 123), (2, 345);

 update foo set fooval = (select barval from bar where barid =  
fooid) |

 0x8000;

 select * from foo;

 # +---+-+
 # | fooid | fooval  |
 # +---+-+
 # | 1 | 9223372036854775807 |
 # | 2 | 9223372036854775807 |
 # +---+-+
 # 2 rows in set (0.00 sec)

 # Oops result is Long.MAX_VALUE (as if subselect result was 0 - bit
 logic result is always unsigned bigint)
 # Same thing when you replace subselect by multi table update syntax

 update foo, bar set fooval = barval | 0x8000 where  
fooid =

 barid;

 select * from foo;

 # +---+-+
 # | fooid | fooval  |
 # +---+-+
 # | 1 | 9223372036854775807 |
 # | 2 | 9223372036854775807 |
 # +---+-+
 # 2 rows in set (0.00 sec)

 # and it seems that its all about MSB sign bit, cause thats fine:

 update foo, bar set fooval = barval | 0x7000 where  
fooid =

 barid;

 select * from foo;

 # +---+-+
 # | fooid | fooval  |
 # +---+-+
 # | 1 | 8070450532247928955 |
 # | 2 | 8070450532247929177 |
 # +---+-+
 # 2 rows in set (0.00 sec)


 # and casting the or result! does the trick too though I dont
 understand why ...

 update foo set fooval = cast((select barval from bar where barid =
 fooid) | 0x8000 as signed);

 select * from foo;

 # +---+--+
 # | fooid | fooval   |
 # +---+--+
 # | 1 | -9223372036854775685 |
 # | 2 | -9223372036854775463 |
 # +---+--+
 # 2 rows in set (0.00 sec)







--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Update Problem when ORing w/ Long.MIN_VALUE

[Gautam Gopalakrishnan]

Hi Daniel,

I can see the problem without using update. However, I am a newbie at 
mysql,

so can't say for certain if it's a bug:

mysql drop table if exists foo;
mysql create table foo (id int signed, val bigint signed);
mysql insert into foo values (0x, 0x), (-1, -1);
mysql select hex(id), hex(val) from foo;

+--+--+
| hex(id)  | hex(val) |
+--+--+
| 7FFF | 7FFF |
|  |  |
+--+--+
2 rows in set (0.00 sec)


Regards
Gautam

Daniel Doubleday wrote:

Hi everybody -

I'm experiencing some really weird update behaviour (mysql 5.0) when 
or'ing results from subselects using Long.MIN_VALUE.

But before I post a bug report I wanted to ask if I'm missing something.


drop table if exists foo;
drop table if exists bar;

create table foo (fooid int, fooval bigint);
create table bar (barid int, barval bigint);

insert into foo values (1, null), (2, null);
insert into bar values (1, 123), (2, 345);

update foo set fooval = (select barval from bar where barid = fooid) | 
0x8000;


select * from foo;

# +---+-+
# | fooid | fooval  |
# +---+-+
# | 1 | 9223372036854775807 |
# | 2 | 9223372036854775807 |
# +---+-+
# 2 rows in set (0.00 sec)

# Oops result is Long.MAX_VALUE (as if subselect result was 0 - bit 
logic result is always unsigned bigint)

# Same thing when you replace subselect by multi table update syntax

update foo, bar set fooval = barval | 0x8000 where fooid = 
barid;


select * from foo;

# +---+-+
# | fooid | fooval  |
# +---+-+
# | 1 | 9223372036854775807 |
# | 2 | 9223372036854775807 |
# +---+-+
# 2 rows in set (0.00 sec)

# and it seems that its all about MSB sign bit, cause thats fine:

update foo, bar set fooval = barval | 0x7000 where fooid = 
barid;


select * from foo;

# +---+-+
# | fooid | fooval  |
# +---+-+
# | 1 | 8070450532247928955 |
# | 2 | 8070450532247929177 |
# +---+-+
# 2 rows in set (0.00 sec)


# and casting the or result! does the trick too though I dont 
understand why ...


update foo set fooval = cast((select barval from bar where barid = 
fooid) | 0x8000 as signed);


select * from foo;

# +---+--+
# | fooid | fooval   |
# +---+--+
# | 1 | -9223372036854775685 |
# | 2 | -9223372036854775463 |
# +---+--+
# 2 rows in set (0.00 sec)






--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: UPDATE rows based on multiple WHERE values?

[Waynn Lue]

Thanks, I'll take a look at that.
Appreciate the help,
Waynn
On Sat, Jul 12, 2008 at 9:02 PM, Rob Wultsch [EMAIL PROTECTED] wrote:

  On Sat, Jul 12, 2008 at 8:01 PM, Waynn Lue [EMAIL PROTECTED] wrote:
  Is there any way to have an UPDATE statement change a column value based
 on
  the WHERE statement?
 
  Essentially, I want to do something like this
 
  UPDATE Actions SET ActionsSent = foo WHERE ActionsReceived = bar
 
  where foo and bar change for multiple sets of values.  Is there a way to
 do
  this in one SQL statement, or do I have to execute an UPDATE statement
 per
  pair of values?
 
  Thanks,
  Waynn
 

 Take a look at CASE
 http://dev.mysql.com/doc/refman/5.0/en/case-statement.html

 mysql CREATE TABLE `t2` (
-   `col1` varchar(255) default NULL,
-   `col2` varchar(255) default NULL
- );
 Query OK, 0 rows affected (0.13 sec)

 mysql INSERT INTO t2 (col1,col2) VALUES(null, 'stuff'),(null,
 'foo'),(null, 'bar');
 Query OK, 3 rows affected (0.05 sec)
 Records: 3  Duplicates: 0  Warnings: 0

 mysql UPDATE t2 SET col1 = CASE  `col2`
- WHEN  'stuff' THEN 'blah'
- WHEN  'foo' THEN 'darn foo'
- ELSE 'not blah'
- END;
 Query OK, 3 rows affected (0.06 sec)
 Rows matched: 3  Changed: 3  Warnings: 0

 mysql SELECT * FROM t2;
 +--+---+
 | col1 | col2  |
 +--+---+
 | blah | stuff |
 | darn foo | foo   |
 | not blah | bar   |
 +--+---+
 3 rows in set (0.00 sec)

 --
 Rob Wultsch
 [EMAIL PROTECTED]

Re: Update replace / Operand should contain 1 column(s) 1241

[Rob Wultsch]

On Tue, Jul 15, 2008 at 7:59 AM, Guenter Ladstaetter
[EMAIL PROTECTED] wrote:
 UPDATE `phpbt_bug`
 SET title = REPLACE (phpbt_bug.title,ö,ö)
 WHERE bug_id IN
 (select bug_id, title from phpbt_bug where `title` LIKE %ö%);


 The error message is: Operand should contain 1 column(s) 1241

You have multiple problems.
1. IN() subqueries only allow one column, and you have two. Take a
look at http://dev.mysql.com/doc/refman/5.0/en/comparisons-using-subqueries.html
. Please note that in general sub queries are a less than good idea in
MySQL
2. Currently, you cannot update a table and select from the same
table in a subquery.
http://dev.mysql.com/doc/refman/5.0/en/update.html

mysql create table phpbt_bug(title varchar(255),bug_id int);
Query OK, 0 rows affected (0.25 sec)

mysql UPDATE `phpbt_bug`
- SET title = REPLACE (phpbt_bug.title,ö,A¶)
- WHERE bug_id IN
- (select bug_id, title from phpbt_bug where `title` LIKE %ö%);
ERROR 1241 (21000): Operand should contain 1 column(s)

So you would want to get rid of title to deal with that error.

mysql UPDATE `phpbt_bug`
- SET title = REPLACE (phpbt_bug.title,ö,A¶)
- WHERE bug_id IN
- (select bug_id from phpbt_bug where `title` LIKE %ö%);
ERROR 1093 (HY000): You can't specify target table 'phpbt_bug' for
update in FROM clause

Now your just SOL with this strategy. Lets get rid of the subquery,
which should generally be avoided in mysql anyways.

mysql UPDATE `phpbt_bug`
- SET title = REPLACE (phpbt_bug.title,ö,A¶)
- WHERE `title` LIKE %ö%;
Query OK, 0 rows affected (0.34 sec)
Rows matched: 0  Changed: 0  Warnings: 0

And this works mysql versions going back forever. You should not be
gaining anything from the where clause, one way or another every row
will need to be examined.

mysql UPDATE `phpbt_bug`
- SET title = REPLACE (phpbt_bug.title,ö,A¶);
Query OK, 0 rows affected (0.00 sec)

Note the rows affected. If the update does not change anything in a
row the number will not be incremented.

-- 
Rob Wultsch

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: UPDATE rows based on multiple WHERE values?

[Rob Wultsch]

On Sat, Jul 12, 2008 at 8:01 PM, Waynn Lue [EMAIL PROTECTED] wrote:
 Is there any way to have an UPDATE statement change a column value based on
 the WHERE statement?

 Essentially, I want to do something like this

 UPDATE Actions SET ActionsSent = foo WHERE ActionsReceived = bar

 where foo and bar change for multiple sets of values.  Is there a way to do
 this in one SQL statement, or do I have to execute an UPDATE statement per
 pair of values?

 Thanks,
 Waynn


Take a look at CASE http://dev.mysql.com/doc/refman/5.0/en/case-statement.html

mysql CREATE TABLE `t2` (
-   `col1` varchar(255) default NULL,
-   `col2` varchar(255) default NULL
- );
Query OK, 0 rows affected (0.13 sec)

mysql INSERT INTO t2 (col1,col2) VALUES(null, 'stuff'),(null,
'foo'),(null, 'bar');
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql UPDATE t2 SET col1 = CASE  `col2`
- WHEN  'stuff' THEN 'blah'
- WHEN  'foo' THEN 'darn foo'
- ELSE 'not blah'
- END;
Query OK, 3 rows affected (0.06 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql SELECT * FROM t2;
+--+---+
| col1 | col2  |
+--+---+
| blah | stuff |
| darn foo | foo   |
| not blah | bar   |
+--+---+
3 rows in set (0.00 sec)

-- 
Rob Wultsch
[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Update with select

[Rolando Edwards]

You are better off with an UPDATE JOIN

UPDATE pdata A,pdata B
SET A.pvalue = B.pvalue
WHERE A.pentrytime = 117540
AND   B.pentrytime = 1207022400;

Give it a try !!!

-Original Message-
From: Albert E. Whale [mailto:[EMAIL PROTECTED]
Sent: Friday, May 02, 2008 4:06 PM
To: mysql@lists.mysql.com
Subject: Update with select

I am trying to update a field on a record in a table.  Here is the
statement I created:

UPDATE pdata SET pvalue = ( SELECT pvalue
FROM pdata
WHERE pentrytime =1207022400 )
WHERE pentrytime =117540;

However, I get the following error:

|#1093 - You can't specify target table 'pdata' for update in FROM clause

What can I use to fix this?
|
--
Albert E. Whale, CHS CISA CISSP
Sr. Security, Network, Risk Assessment and Systems Consultant

ABS Computer Technology, Inc. http://www.ABS-CompTech.com - Email,
Internet and Security Consultants
SPAMZapper http://www.Spam-Zapper.com - No-JunkMail.com
http://www.No-JunkMail.com - *True Spam Elimination*.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]