INSERT VALUE INTO ROW AND COLUMN

Question

INSERT VALUE INTO ROW AND COLUMN

ny66

SSC-Addicted

Points: 499
More actions
August 23, 2010 at 9:35 am

#226492

if i have rows that are missing a specifc value, how do i insert into a specific row a certain value
3302DENIM6P1000000100 --- row 1
8P000000 ----- row 2
10P000000 ---- row 3
12P000000 ---- row 4
14P000000 --- row 5
16P000000 ---- row 6
18P6000000600 --- row 7
i would like to insert the 3302 and denim value into rows 2-7???

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 734794 More actions · Answer 1

First, there are no rows in SQL Server as in numbered rows. Any row can occur in any order and without an ORDER BY in your query, no order is guaranteed.

To change a particular row, you would use an UPDATE statement.

UPDATE MyTable

SET col2 = 3301

where col1 = '8P'

Henry Treftz SSCarpal Tunnel Points: 4207 More actions · Answer 2

Is the first column a primary key? If so you could just do an update...

DBA in Unit 7 SSCarpal Tunnel Points: 4059 More actions · Answer 3

Will this work for you?

UPDATE YOURTABLE

SET COLUMN_NAME_for_3302=3302,

COLUMN_NAME_ for_DENIM ='DENIM'

WHERE COLUMN_NAME_for_6P

IN ('8P','10P','12P','16P','18P')

*Assuming the values in column for 6P, 8P, ...2nP are unique for the rows in your table*

ny66 SSC-Addicted Points: 499 More actions · Answer 4

i dont want to update all the values in the column, only certain ones.

if you look at the data below, you will see there are only certain rows that i need to populated with certain values, other rows other values(the first rows between 3302 and 3303 need to have 3302 and denim added)

3303 has different values and so on. that is why i was asking about rows.

3302 DENIM 6P 100 0 0 0 0 100

8P 0 0 0 0 0 0

10P 0 0 0 0 0 0

12P 0 0 0 0 0 0

14P 0 0 0 0 0 0

16P 0 0 0 0 0 0

18P6000000600

3303DENIM6P000000

8P000000

10P000000

12P000000

14P000000

16P000000

18P2500000250

9300BLACK6P8000020-20780

8P00018-18982

10P6000045-45555

12P00057-57943

14P1000049-4951

16P5000053-53447

18P9000021-21879

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 5

Create a SELECT....FROM clause that will produce the rows you want. Then change that to an UPDATE statement with the same WHERE clause. If you can select the set, then that criteria will be what you want for your update.

ny66 SSC-Addicted Points: 499 More actions · Answer 6

they are not unique values unfortunately, with the primary key suggestion, is there a way to create a unique record for each row that is added to the table such as REC_1, REC_2 etc that i could add in as a column and use that to update with?

auto generated primary key?

ny66 SSC-Addicted Points: 499 More actions · Answer 7

used this to add a record number

DECLARE @counter int

SET @counter = 0

UPDATE DBO.NEWDUNDD_SEAXX

SET @counter = rec_numb= @counter + 1