TSQL Update - 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 2008 Forums

Transact-SQL (2008)

TSQL Update

Author

Topic

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2011-03-23 : 17:48:35

Hi,
I have 3 table as such and need to perform an update

Table Vehicle [Primary Key =VID]
VID VName VType
1 Volvo Wagon
2 BMW SUV
3 Audi Sedan
4 Merc Van
5 Saab Sedan

Table Type [No Primary Key]
TypeID TType TGroup
11 Wagon Wagon
12 SUV SUV
13 Lux Sedan Sedan
14 Van Van
14 Mini Van Van
13 Comp Sedan Sedan

Table Vehicle_Type [Join table Vehicle + Type]
VID GroupID
1 0
2 0
3 0
4 0
5 0

Relationships
Vehicle_Type.VID = Vehicle.VID
Vehicle.VType=Type.TGroup

I need to update column GroupID in Vehicle_Type by joining the following

UPDATE Vehicle_Type set GroupID =
( SELECT TGroup from Type T INNER JOIN Vehicle V ON
V.VType=T.TGroup INNER JOIN Vehicle_Type VT ON VT.VID=V.VID)

I get an error
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I think this Update is wrong, can someone guide me.

Thank You

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-03-23 : 18:03:14

yes the update is wrong.

anyways here is an update you could try


UPDATE tgt

  SET GroupID  = TGroup
 FROM Vehicle_Type tgt
INNER JOIN Vehicle V 
   ON VT.VID=V.VID
INNER JOIN Type T 
   ON V.VType=T.TGroup

question to you: Why do you not have a primary key for group and why are you using 0 for groupID?

If you don't have the passion to help people, you have no passion

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2011-03-24 : 10:03:55

Thank You Yosiasz.It worked perfectly for me

The value '0' for the GroupId is being defaulted from the front end. That is the reason we need to update the GroupId after with the correct values.

We have gone ahead and create a PK on table Type [ Type ID + TType] column.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-03-24 : 11:08:04

Why not create a table with those group values and feed the front end with a drop down of those values. next time someone wants a new group you are going to change the front end? not sustainable design IMHO

If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources