Insert Into SQL 1

[teksecrets]

Hi,
How would I go about inserting a number to a new field/column in my table.

I have a column named Number which I'd like to add 10 to all records. It's about 1000 records which I need to add 10 to the Number column.

For example,
Number column is empty now. I'd like to add 10 to that entire column.

Thanks.

 

[sunila7]

Hi,

use update stmnt to do this...

Update tbltobeupdated set NumberCol = 10


Sunil

 

[teksecrets]

Hi,
Is it possible to update a field/column from a select statement? I want to use the results of my select statement to update a field.

Thanks.

 

[swampBoogie]

Could you be a bit more specific?

It is possible to write queries as

update t
set t.c = q.c
from t inner join q on t.pk = q.pk

 

[SQLSister]

you can use a select statement in an update like this example from Books Online:
UPDATE authors
SET state = 'ZZ'
FROM (SELECT TOP 10 * FROM authors ORDER BY au_lname) AS t1
WHERE authors.au_id = t1.au_id

 

[teksecrets]

I'd like to select part of a field in table1 and then insert it into another field in the same table1.

for example:
IN table 1, I have IP address: 129.34.2.32 in field1
I'd like to select part of this ip address 34.2 in field1 and insert that into field 2 of the same table.

So, field 1 should show: 129.34.2.32
field 2 should show 34.2 only.

The Select statement that I'm using to get part of the IP address is: Select Substring (IP, 4, 6) from table 1.

Thanks.

 

[SQLSister]

Update tbltobeupdated
set field2 = Substring (IP, 4, 6)

 

[sunila7]

Hi,

Try this

Update Tbl Set field2 = Substring (IPfieldName, 4, 6)

Use cheyney thread183-510490 suggestion to get part of IP address... as if the IP address is 2.4.5.6, the way u r using substring wont work...

Sunil

 

[teksecrets]

Thanks sunila7. That worked, except for 1 thing.
Since I'm asking for 6 spaces for IP address, for those with only 5 leaves a . at the end for the 6th space.

For example,
1.12.123.12 = 12.123
4.4.567.12 = 4.567.

How can I eliminate the last period at the end with this syntax --

Update Tbl Set field2 = Substring (IPfieldName, 4, 6) ?

Thanks,