Update Query based on multiple tables and multiple criteria

Question

Post reply

Update Query based on multiple tables and multiple criteria

steve.anderson 7639

SSC Eights!

Points: 928
More actions
May 11, 2011 at 2:19 pm

#250875

Hi:
I'm fairly new to all of this, but I would like to erform the following:
UPDATE Purchasing
SET Purchasing.IsHardware = 1
FROM Purchasing as t1
INNER JOIN Vault_043011 as t2
ON t2.IsPurchased = 1 AND t2.PartDesc LIKE 'NUT%'
I don't think it likes the multiple values on the ON statement, or the LIKE.
Also, what resource would you recommended to get jump started in this?
Thanks in advanced

Viewing 15 posts - 1 through 15 (of 18 total)

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

Dan.Humphries SSChampion Points: 12125 More actions · Answer 1

try this. you can not use a like in a join. but by moving it to the where clause it should work fine.

UPDATE Purchasing

SET Purchasing.IsHardware = 1

FROM Purchasing as t1

INNER JOIN Vault_043011 as t2

ON t2.IsPurchased = 1

where t2.PartDesc LIKE 'NUT%'

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

What is the relationship between Purchasing and Vault_043011?

Why are we joining on a value as opposed to a common field?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

steve.anderson 7639 SSC Eights! Points: 928 More actions · Answer 3

Thanks. One more thought, is there a way to update the same field in another table with the same statement?

Any recommended reading?

steve.anderson 7639 SSC Eights! Points: 928 More actions · Answer 4

I'm just trying to update another table based on various criteria, obviously. What you are saying is that there must be better ways for me to do this??

Dan.Humphries SSChampion Points: 12125 More actions · Answer 5

Welsh is absolutely correct I had missed that. you have to establish the relationship between the two tables. the current clause is more of a where cluase not a join.

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

Does the following example help you?

http://howto.thibeault.cc/2008/02/join-sql-update.html

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

steve.anderson 7639 SSC Eights! Points: 928 More actions · Answer 7

I don't think the Where is being seen here. It is changing all the records.

UPDATE Purchasing

SET Purchasing.IsHardware = 1

FROM Purchasing as t1

INNER JOIN Vault_043011 as t2

ON t2.IsPurchased = 1

WHERE t2.PartDesc LIKE 'NUT%'

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 8

steve.anderson 7639 (5/13/2011)
I don't think the Where is being seen here. It is changing all the records.
UPDATE Purchasing
SET Purchasing.IsHardware = 1
FROM Purchasing as t1
INNER JOIN Vault_043011 as t2
ON t2.IsPurchased = 1
WHERE t2.PartDesc LIKE 'NUT%'

What are the common columns in the Purchasing and Vault_043011 Tables?

One table should be the parent (Primary Key or Unique Constraint and the other the child FK Constraint and the Join should be based on these two columns.

It seems that you are missing a table for you to be able to join.

Could you please include you schema and sample data?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

steve.anderson 7639 SSC Eights! Points: 928 More actions · Answer 9

Purchasing Table:

PartNumber, PK, nchar(10)

IsHardware, bit

KitAlways, bit

ChinaPN, nchar(10)

KitChina, bit

InstallTime, int

LaborClass, int

UnitCost, nchar(10)

Cault_043011 Table:

PartNo, varchar(max)

PartDesc, varchar(max)

Revision, varchar(max)

Author, varchar(max)

Stat, varchar(max)

Config, varchar(max)

LN, varchar(max)

WT, varchar(max)

Material, varchar(max)

IsPurchased, bit

IsHardware, bit

No relationship between the tables. How and why would I do this? I'm just looking to pull the PartDesc over as a lookup, using Purchasing as the main data.

Thanks!

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 10

steve.anderson 7639 (5/13/2011)
Purchasing Table:
PartNumber, PK, nchar(10)
IsHardware, bit
KitAlways, bit
ChinaPN, nchar(10)
KitChina, bit
InstallTime, int
LaborClass, int
UnitCost, nchar(10)
Cault_043011 Table:
PartNo, varchar(max)
PartDesc, varchar(max)
Revision, varchar(max)
Author, varchar(max)
Stat, varchar(max)
Config, varchar(max)
LN, varchar(max)
WT, varchar(max)
Material, varchar(max)
IsPurchased, bit
IsHardware, bit
No relationship between the tables. How and why would I do this? I'm just looking to pull the PartDesc over as a lookup, using Purchasing as the main data.

You have PartNumber and PartNo but they are different data types. They need to be the same to create a FK.

Does the Data match up in these two columns and do you have any orphans?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

steve.anderson 7639 SSC Eights! Points: 928 More actions · Answer 11

Is there a way to change the data type without having to drop and recreate tables?

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 12

steve.anderson 7639 (5/13/2011)
Is there a way to change the data type without having to drop and recreate tables?

You need to do this in a development environemnt. Watch out for truncated data, etc when changing data types.

ALTER TABLE Vault_043011 dbo.Table

ALTER COLUMN PartNo nchar(10);

If you not already started to develop the GUI I would rename the column so that they are the same in both tables.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

steve.anderson 7639 SSC Eights! Points: 928 More actions · Answer 13

steve.anderson 7639

SSC Eights!

Points: 928

May 13, 2011 at 10:08 am

#1324444

Thanks!

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 14

Some additional information is listed in the following article:

http://www.sqlinfo.net/sqlserver/sql_server_Alter_Add_column.php

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/