| Author |
Topic |
|
phanicrn
Starting Member
42 Posts |
 Posted - 2010-08-04 : 15:14:30
|
| can we update single with multiple values in a table. Let's say Table1 with columns from A to E having 6000 rows of datai want insert column D with multiple values.. can we do that. using update statement i can do for one value only. any solution or suggestion i should do..phani |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
phanicrn
Starting Member
42 Posts |
Posted - 2010-08-04 : 15:32:14
|
| can we do it like this..update table1set A = 80,100where division in ('x','y','z') |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
phanicrn
Starting Member
42 Posts |
Posted - 2010-08-04 : 16:02:34
|
| defining where clause will work..? |
|
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2010-08-04 : 16:16:10
|
quote:
Originally posted by phanicrn
defining where clause will work..?
What does 'will work' refer to? As Tara clearly pointed, "A row's column can't be equal to two different values at once"..it doesn't make sense.Why don't you clearly state your problem with some sample data and expected output...you will get a very quick solution.Refer to this link on how to provide the samehttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2010-08-04 : 19:06:35
|
quote:
Originally posted by phanicrn
can we do it like this..update table1set A = 80,100where division in ('x','y','z')
When division = 'x', what value do you want for A?When division = 'y', what value do you want for A?You need to define the criteria for what value you A to be assigned. Once you have that defined, you can probably use a CASE expression to assign the appropriate value:UPDATE dbo.table1SET A = CASE WHEN division = 'x' THEN 80 WHEN division = 'y' THEN 100 ELSE 0 ENDWHERE division IN ('x', 'y', 'z');Jeff |
|
|
|
ewomack
Starting Member
33 Posts |
Posted - 2010-08-05 : 09:00:01
|
| To show how little this makes sense, you COULD (though you wouldn't want to) make a varchar column and insert your 2 elements like this: Element1 + ', ' + Element2 and make a column that has both values in it that looks something like '80,100' but you wouldn't be able to do anything with that information once it was inserted. But if your purpose was just to make a column for whatever use, you could do this. I'm really not sure why you would, but I've seen some strange things out there.Ed Womackwww.getmilked.com |
|
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2010-08-05 : 11:48:43
|
quote:
Originally posted by phanicrn
can we do it like this..update table1set A = 80,100where division in ('x','y','z')
This is absurd. But you can write:UPDATE Table1 SET a = CASE division WHEN 'x' THEN 80 WHEN 'y' THEN 100 WHEN 'z' THEN 200 ELSE a END WHERE division IN ('x','y','z');--CELKO--Joe Celko, SQL Guru |
|
|
|
|
update single column with multiple values