Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

alter table then update in single statement

I have a requirement where I need to Alter (Add 2 columns) and then update the same table.

Here is the query I tried:

ALTER TABLE A
ADD c1 int,c2 varchar(10)

UPDATE  A set c1 = 23, c2 = 'ZZXX'

I need to run the above two queries at a time.

I am using Talend ETL tool, in this we have a component tMssqlrow, which allow us to run multiple queries (I am using 10 to 15 update queries in single component).

But the above query is not working.

I tested in DataBase Microsoft SQL. i am getting the below error :

Msg 207, Level 16, State 1, Line 5

Invalid column name 'c1'. Msg 207,

Level 16, State 1, Line 5

Invalid column name 'c2'.

can any one help me resolve this problem.

like image 611
Raghunath Avatar asked Apr 01 '13 09:04

Raghunath


People also ask

Can we use ALTER and update together in SQL?

Update command will perform on the data level. ALTER Command is used to add, delete, modify the attributes of the relations (tables) in the database. UPDATE Command is used to update existing records in a database. ALTER Command by default initializes values of all the tuple as NULL.

How do you update multiple values in a single update statement?

Or you can use INSERT ... INSERT INTO students (id, score1, score2) VALUES (1, 5, 8), (2, 10, 8), (3, 8, 3), (4, 10, 7) ON DUPLICATE KEY UPDATE score1 = VALUES(score1), score2 = VALUES(score2);

Can we change multiple columns using single update statement?

We can update multiple columns by specifying multiple columns after the SET command in the UPDATE statement. The UPDATE statement is always followed by the SET command, it specifies the column where the update is required.


2 Answers

You can't do this exactly in a single statement (or batch) and it seems the tool you are using does not support GO as a batch delimiter.

You can use EXEC to run it in a child batch though.

ALTER TABLE A
  ADD c1 INT, c2 VARCHAR(10);

EXEC('
UPDATE A
SET    c1 = 23,
       c2 = ''ZZXX'';
    ');

NB: All single quotes in the query need to be doubled up as above to escape them inside a string literal.

Or alternatively you could achieve similar results in a single statement with the aid of some default constraints.

ALTER TABLE A
  ADD c1 INT NULL CONSTRAINT DF_A_c1 DEFAULT 23 WITH VALUES, 
     c2 VARCHAR(10) CONSTRAINT DF_A_c2 NULL DEFAULT 'ZZXX' WITH VALUES;

But this is not exactly the same as the original query as the default constraints will be left behind and may need to be dropped.

like image 164
Martin Smith Avatar answered Sep 21 '22 20:09

Martin Smith


Use GO between your 2 queries.

like image 45
Nilesh Thakkar Avatar answered Sep 19 '22 20:09

Nilesh Thakkar