Trancate table from command line?

Is there a way to use mysqladmin (or mysql) to truncate a table as a
one-off command from the command line?

I have an issue with importing data from one database into another, but
the second database might have columns that the first did not.

On database 1, I use mysqldump to grab certain tables, and when I try to
simply "overlay" those tables onto the new database, I get an error

ERROR 1062 (23000) at line 24: Duplicate entry '378-4' for key 1

So we're thinking we should truncate the table first, but it appears
this must be done manually in the mysql> shell, which won't work... this
operation needs to be scripted, or to be presented as one command line
that can be copy-and-pasted.

How can I do something like :

mysqladmin -h db_server -u user -pPASSWORD truncate table
table1 ?

--
************************************************************ ***********
* John Oliver http://www.john-oliver.net/ *
* *
************************************************************ ***********

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

Re: Trancate table from command line?

On Tue, Mar 16, 2010 at 02:12:42PM -0700, John Oliver wrote:
> Is there a way to use mysqladmin (or mysql) to truncate a table as a
> one-off command from the command line?
>
> I have an issue with importing data from one database into another, but
> the second database might have columns that the first did not.
>
> On database 1, I use mysqldump to grab certain tables, and when I try to
> simply "overlay" those tables onto the new database, I get an error
>
> ERROR 1062 (23000) at line 24: Duplicate entry '378-4' for key 1
>
> So we're thinking we should truncate the table first, but it appears
> this must be done manually in the mysql> shell, which won't work... this
> operation needs to be scripted, or to be presented as one command line
> that can be copy-and-pasted.
>
> How can I do something like :
>
> mysqladmin -h db_server -u user -pPASSWORD truncate table
> table1 ?

Just to leave an answer behind for the next character who runs into
this...

for i in table1 table2 table3 ; do mysql -h db_server -u user -pPASSWORD
-e "truncate table $i" DATABASE; done

--
************************************************************ ***********
* John Oliver http://www.john-oliver.net/ *
* *
************************************************************ ***********

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

Re: Trancate table from command line?

--000e0cdfd3121c4df80481f8c8e3
Content-Type: text/plain; charset=ISO-8859-1

If you use *nix, you can use this form:

*echo* "TRUNCATE TABLE you_table_name;" | *mysql* -A your_db_name -ulogin
-ppassword

And:

*cat* your_file.sql | *mysql* -A your_db_name -ulogin -ppassword


2010/3/17 John Oliver

> Is there a way to use mysqladmin (or mysql) to truncate a table as a
> one-off command from the command line?
>
> I have an issue with importing data from one database into another, but
> the second database might have columns that the first did not.
>
> On database 1, I use mysqldump to grab certain tables, and when I try to
> simply "overlay" those tables onto the new database, I get an error
>
> ERROR 1062 (23000) at line 24: Duplicate entry '378-4' for key 1
>
> So we're thinking we should truncate the table first, but it appears
> this must be done manually in the mysql> shell, which won't work... this
> operation needs to be scripted, or to be presented as one command line
> that can be copy-and-pasted.
>
> How can I do something like :
>
> mysqladmin -h db_server -u user -pPASSWORD truncate table
> table1 ?
>
> --
> ************************************************************ ***********
> * John Oliver http://www.john-oliver.net/ *
> * *
> ************************************************************ ***********
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=ekilimchuk@gmail.com
>
>


--
Best regards,

Eugene Kilimchuk

--000e0cdfd3121c4df80481f8c8e3--

RE: Trancate table from command line?

--_90f3b203-c114-4cba-8a70-82137a7df581_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


John

I think you can do this way on shell:
mysql -h db_server -u user -pPASSWORD -e "truncate table
table1"








Carlos Eduardo Caldi



> Date: Tue=2C 16 Mar 2010 14:12:42 -0700
> From: joliver@john-oliver.net
> To: mysql@lists.mysql.com
> Subject: Trancate table from command line?
>=20
> Is there a way to use mysqladmin (or mysql) to truncate a table as a
> one-off command from the command line?
>=20
> I have an issue with importing data from one database into another=2C but
> the second database might have columns that the first did not.
>=20
> On database 1=2C I use mysqldump to grab certain tables=2C and when I try=
to
> simply "overlay" those tables onto the new database=2C I get an error=20
>=20
> ERROR 1062 (23000) at line 24: Duplicate entry '378-4' for key 1
>=20
> So we're thinking we should truncate the table first=2C but it appears
> this must be done manually in the mysql> shell=2C which won't work... thi=
s
> operation needs to be scripted=2C or to be presented as one command line
> that can be copy-and-pasted.
>=20
> How can I do something like :
>=20
> mysqladmin -h db_server -u user -pPASSWORD truncate table
> table1 ?
>=20
> --=20
> ************************************************************ ***********
> * John Oliver http://www.john-oliver.net/ *
> * *
> ************************************************************ ***********
>=20
> --=20
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dce_caldi@hotmail.=
com
>=20
=20
____________________________________________________________ _____
N=E3o deixe rastros ao navegar na Internet. Instale Gr=E1tis o Internet Exp=
lorer 8 agora.
http://go.microsoft.com/?linkid=3D9707132=

--_90f3b203-c114-4cba-8a70-82137a7df581_--

RE: Trancate table from command line?

Am I missing something? Doesn't the -e parameter for the mysql CLI still work?
I use it all the time in my scripts.

mysql -ulogin -ppassword -e "truncate sometable" your_dbname

Regards,

Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341

www.the-infoshop.com

>-----Original Message-----
>From: ??????? ???????? [mailto:ekilimchuk@gmail.com]
>Sent: Wednesday, March 17, 2010 1:57 AM
>To: John Oliver
>Cc: mysql@lists.mysql.com
>Subject: Re: Trancate table from command line?
>
>If you use *nix, you can use this form:
>
>*echo* "TRUNCATE TABLE you_table_name;" | *mysql* -A your_db_name -ulogin
>-ppassword
>
>And:
>
>*cat* your_file.sql | *mysql* -A your_db_name -ulogin -ppassword
>
>
>2010/3/17 John Oliver
>
>> Is there a way to use mysqladmin (or mysql) to truncate a table as a
>> one-off command from the command line?
>>
>> I have an issue with importing data from one database into another, but
>> the second database might have columns that the first did not.
>>
>> On database 1, I use mysqldump to grab certain tables, and when I try to
>> simply "overlay" those tables onto the new database, I get an error
>>
>> ERROR 1062 (23000) at line 24: Duplicate entry '378-4' for key 1
>>
>> So we're thinking we should truncate the table first, but it appears
>> this must be done manually in the mysql> shell, which won't work... this
>> operation needs to be scripted, or to be presented as one command line
>> that can be copy-and-pasted.
>>
>> How can I do something like :
>>
>> mysqladmin -h db_server -u user -pPASSWORD truncate table
>> table1 ?
>>
>> --
>> ************************************************************ ***********
>> * John Oliver http://www.john-oliver.net/ *
>> * *
>> ************************************************************ ***********
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?unsub=ekilimchuk@gmail.com
>>
>>
>
>
>--
>Best regards,
>
>Eugene Kilimchuk




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

RE: Trancate table from command line?

>-----Original Message-----
>From: Jerry Schwartz [mailto:jschwartz@the-infoshop.com]
>Sent: Wednesday, March 17, 2010 10:07 AM
>To: '??????? ????????'; 'John Oliver'
>Cc: mysql@lists.mysql.com
>Subject: RE: Trancate table from command line?
>
>Am I missing something? Doesn't the -e parameter for the mysql CLI still
>work?
>I use it all the time in my scripts.
>
>mysql -ulogin -ppassword -e "truncate sometable" your_dbname
>
[JS] Obviously, I meant "truncate table sometable".

>Regards,
>
>Jerry Schwartz
>The Infoshop by Global Information Incorporated
>195 Farmington Ave.
>Farmington, CT 06032
>
>860.674.8796 / FAX: 860.674.8341
>
>www.the-infoshop.com
>
>>-----Original Message-----
>>From: ??????? ???????? [mailto:ekilimchuk@gmail.com]
>>Sent: Wednesday, March 17, 2010 1:57 AM
>>To: John Oliver
>>Cc: mysql@lists.mysql.com
>>Subject: Re: Trancate table from command line?
>>
>>If you use *nix, you can use this form:
>>
>>*echo* "TRUNCATE TABLE you_table_name;" | *mysql* -A your_db_name -ulogin
>>-ppassword
>>
>>And:
>>
>>*cat* your_file.sql | *mysql* -A your_db_name -ulogin -ppassword
>>
>>
>>2010/3/17 John Oliver
>>
>>> Is there a way to use mysqladmin (or mysql) to truncate a table as a
>>> one-off command from the command line?
>>>
>>> I have an issue with importing data from one database into another, but
>>> the second database might have columns that the first did not.
>>>
>>> On database 1, I use mysqldump to grab certain tables, and when I try to
>>> simply "overlay" those tables onto the new database, I get an error
>>>
>>> ERROR 1062 (23000) at line 24: Duplicate entry '378-4' for key 1
>>>
>>> So we're thinking we should truncate the table first, but it appears
>>> this must be done manually in the mysql> shell, which won't work... this
>>> operation needs to be scripted, or to be presented as one command line
>>> that can be copy-and-pasted.
>>>
>>> How can I do something like :
>>>
>>> mysqladmin -h db_server -u user -pPASSWORD truncate table
>>> table1 ?
>>>
>>> --
>>> ************************************************************ ***********
>>> * John Oliver http://www.john-oliver.net/ *
>>> * *
>>> ************************************************************ ***********
>>>
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe: http://lists.mysql.com/mysql?unsub=ekilimchuk@gmail.com
>>>
>>>
>>
>>
>>--
>>Best regards,
>>
>>Eugene Kilimchuk
>
>
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=jschwartz@the-
>infoshop.com





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