add column and set values 2

[robdunfey]

Hi,

I have a table with thousands of records. I want to add a column and set the value for that column, for each record, to a text value "ME". I would like to add the column and set the value in the same sql query? I have several copies of the same table and want to run the query on each one. So that why I want to combine the SQL in 1 query. Any help much appreciated.

Rob

 

[Golom]

'fraid not.

You need a DDL (Data Definition Language) query to add the new column

   ALTER TABLE tbl ADD COLUMN myCol Text 2

And you need a DML (Data Manipulation Language) statement to set the value

   UPDATE tbl SET myCol = 'ME'

The rules don't allow combining DDL and DML in the same statement.

 

[DanChard]

I don't think it's possible in one query as you'd need two different types of query. The first would create the column (or you could do this manually but it would have to be done on every table), the second would populate the new column for every row.

 

[robdunfey]

Thanks for the clarification.

can i put a update and select sql in the same query?

Also if I make a query to update the column in table one i want it to put "me1" in table two i want it to put "me2", so can I make the query promt me for a value to insert?

kind regards and thanks,

rob

 

[DanChard]

I thought you could using something like the following but this doesn't seem to work. Maybe someone else could suggest why...

update table_name
set new_column_name = (select old_column_name
from table_name
where record_id = 4)
where record_id = 9;

If you want to add a specific value you can use the following SQL which will prompt you for the value to enter:

insert into table_name (column_name)
values ([enter value])
where 1;

NOTE - this will update every row unless you include a proper a where condition

Hope this is of some use,
Dan