Help with some T-SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Help with some T-SQL

Author

Topic

Volcomgnu
Starting Member

8 Posts

Posted - 2004-05-06 : 16:32:53

So I have a descent size table. In this table are two columns that I want to deal with, "ABC" and "Part_ID". I need to delete all of the information in the "ABC" column and then replace only the "Part_ID"'s that start "5" with new "ABC" fields.

Any help with the T-SQL?

That is a little confusing. Let me know if anyone can help. Thanks in advance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-06 : 16:33:53

Could you show us a data example? What it would look like before and after?

Tara

cas_o
Posting Yak Master

154 Posts

Posted - 2004-05-06 : 16:41:09

UPDATE descent_sized_table SET ABC = new_value where left(Part_ID,1) = '5'

replace descent_sized_table with the table name and new_value with the new value desired for the ABC column.

This syntax assumes that Part_ID is a char or varchar column.

;-]... Quack Waddle

Volcomgnu
Starting Member

8 Posts

Posted - 2004-05-06 : 16:46:30

Tduggan:

ABC | PART_ID
---------------------
A 500001
A 500002
A 500003
B 500004

Volcomgnu
Starting Member

8 Posts

Posted - 2004-05-06 : 16:47:17

I need to delete everything in the ABC column first...then repleace certain values in the ABC field based on the PART_ID starting with a 5.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-06 : 16:56:52

Using the given sample data, what would it look like after the data is updated. Does cas_o's query work for you?

Tara

Volcomgnu
Starting Member

8 Posts

Posted - 2004-05-07 : 11:23:40

CAS-O,
Could you explain what the last part of that statement does. (From "left" over.)

Thanks :)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-07 : 12:05:16

Just says that if the first position (from the left) in Part_ID equals 5, then you've found a match.

Tara

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-07 : 12:21:48

>> I need to delete everything in the ABC column first
You mean set to blank?
>> repleace certain values in the ABC field based on the PART_ID starting with a 5
What do you actually want in the ABC column?

update tbl set ABC = case when PART_ID like '5%' the right(PART_ID,1) else '' end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Volcomgnu
Starting Member

8 Posts

Posted - 2004-05-12 : 18:23:13

I want every row in the column set to null.
Then I want "A" "B" or "C" in the ABC column.

quote:
Originally posted by nr

>> I need to delete everything in the ABC column first
You mean set to blank?
>> repleace certain values in the ABC field based on the PART_ID starting with a 5
What do you actually want in the ABC column?

update tbl set ABC = case when PART_ID like '5%' the right(PART_ID,1) else '' end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources