Alter Table Mysql gives Error

Question

Alter Table Mysql gives Error

Navigation

#1 by (4 votes)
#2 by (1 votes)
#3 by (0 votes)

2

I have the following query for mysql but in doing it gives me error.

ALTER TABLE conexion ADD
panel_web varchar (255),
panel_web1 varchar (255),
panel_web2 varchar (255),
puerto_panel varchar (255);

The question is, how do I add several columns using the same query? (That same works well in SQL server 2008 r2 but in mySQL it does not work)

I appreciate the support.

I tried this one that they told me here but it does not turn out to be an error

ALTER TABLE conexion ADD
panel_web varchar (255) AFTER 
panel_web1 varchar (255)AFTER 
panel_web2 varchar (255) AFTER 
puerto_panel varchar (255);

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'varchar (255) AFTER panel_web2 varchar (255) AFTER

mysql

asked by Juan Carlos Villamizar Alvarez 24.05.2017 в 15:05

source

3 answers

Using many JFrame? (the ones Netbeans brings by default) How do you perform this json on vb.net?

score 4 · Answer 1

Try adding the syntax ADD COlUM to each new column you want to add

ALTER TABLE conexion
ADD COLUMN panel_web varchar (255),
ADD COLUMN panel_web1 varchar (255),
ADD COLUMN panel_web2 varchar (255),
ADD COLUMN puerto_panel varchar (255);

here is the official documentation to do ALTER TABLE of mysql

score 1 · Answer 2

Only as a complement:

I would do the ADD COLUMN rigorously indicating how I want my columns to be , indicating for example if I want them to accept or not null values, if I want them to be UNSIGNED , etc. Using the AFTER is also a good choice, to be sure that the columns will be inserted where I want them to be inserted.

Example:

Demo REXTESTER

CREATE TABLE IF NOT EXISTS prueba_add_column
    ('id' int, 'nombre' varchar(20))
;

INSERT INTO prueba_add_column
    ('id', 'nombre')
VALUES
    (1, 'Nombre 1'),
    (2, 'Nombre 2')
;

-- Select antes de add column

SELECT * FROM prueba_add_column;

ALTER TABLE prueba_add_column
ADD COLUMN 'apellido' VARCHAR(20) NOT NULL AFTER 'nombre',
ADD COLUMN 'edad' SMALLINT(2) NOT NULL AFTER 'apellido',
ADD COLUMN 'status' INT(10) UNSIGNED NOT NULL AFTER 'edad';

UPDATE prueba_add_column SET 'apellido'='Apellido1' WHERE 'id'=1;

-- Select después de add column


SELECT * FROM prueba_add_column;

Result

-- Select antes de add column

    id  nombre
1   1   Nombre 1
2   2   Nombre 2


-- Select después de add column

    id  nombre      apellido    edad    status
1   1   Nombre 1    Apellido1   0       0
2   2   Nombre 2                0       0

score 0 · Answer 3

A database stores its data in tables.

A table is a data structure that organizes the data in columns and rows; each column is a field (or attribute) and each row is a record. The intersection of a column with a row, contains a specific data, a single value.

Each record contains one data for each column of the table.

Each field (column) must have a name. The name of the field refers to the information it will store.

Each field (column) must also define the type of data it will store.

nombre  clave
MarioPerez  Marito
MariaGarcia Mary
DiegoRodriguez  z8080

When creating a table, we must resolve what fields (columns) it will have and what type of data each of them will store, that is, its structure.

The table must be defined with a name that identifies it and with which we will access it.

We create a table called "users", type:

create table usuarios (
     nombre varchar(30),
     clave varchar(10)
);

To use ALTER TABLE:

ALTER TABLE 'images' 
ADD 'alias' VARCHAR(140) NULL 
AFTER 'title';

And you can also execute several transactions with:

START TRANSACTION;
TRUNCATE TABLE accounts;
TRUNCATE TABLE accounts_1;
TRUNCATE TABLE accounts_2;
COMMIT;