Update Table from Another Table

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)

Update Table from Another Table

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2007-03-15 : 09:11:01

I am getting info from one table, CalibrationReview, that is not in
another table, tblEquipments.

SELECT EquipmentNumber, Model, SerialNumber, Make, CalLabName, CalDate
FROM CalibrationReview
WHERE NOT EXISTS (SELECT AssignedID FROM tblEquipments WHERE AssignedID = CalibrationReview.EquipmentNumber)

Now, I need to take these rows and INSERT them into tblEquipments,
but with some conditions.

tblEquipments has some contraints, so, the following needs to be done:

Using dbo.CalibrationReview.EquipmentNumber, get CalibrationMaster.TestTechnology where dbo.CalibrationReview.EquipmentNumber = dbo.CalibrationMaster.EquipmentID

Then take CalibrationMaster.TestTechnology and read tblTestTechnology.testTechnology and get tblTestTechnology.id

So,

tblEquipments.testTechnology = tblTestTechnology.id OR 1 if not found
WHERE dbo.CalibrationReview.EquipmentNumber = dbo.CalibrationMaster.EquipmentID and CalibrationMaster.TestTechnology = tblTestTechnology.testTechnology

And similar for CalibrationReview.CalLabName.

tblEquipments.calLab = tblLabs.ID where tblLabs.LabName = CalibrationReview.CalLabName

I hope this is clear as I can write this in code behind, but it's
much better using sql simply because it's faster and only needs to
be run once.

Inheriting databases and combining all of them to develop a large
.Net management system is fun, huh?

Thanks for any input,

Zath

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-15 : 09:20:42

The basic idea is

UPDATE t1
SET t1.TargetCol1 = t2.SourceCol1,
t2.TargetCol2 = t2.SourceCol2
FROM Table1 AS t1
INNER JOIN Table2 AS t2 ON t2.PrimaryCol = t1.PrimaryCol

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-15 : 09:23:54

Also, change INNER JOIN to LEFT JOIN when join condition is based on optionality like in the case of join between tblEquipments and tblTestTechnology.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2007-03-15 : 09:57:55

Sorry, but I know some may be getting a little frustrated with me.
Just remember way back when you were struggling with sql

But this is where I am and need more input:

UPDATE tblEquipments
SET AssignedID = CalibrationReview.EquipmentNumber,
ModelNumber = CalibrationReview.Model,
SerialNumber = CalibrationReview.SerialNumber,
testTechnology = (tblTestTechnology.id OR 1 if not found
WHERE dbo.CalibrationReview.EquipmentNumber = dbo.CalibrationMaster.EquipmentID
AND CalibrationMaster.TestTechnology = tblTestTechnology.testTechnology),
calLab = (tblLabs.ID where tblLabs.LabName = CalibrationReview.CalLabName)

WHERE(SELECT EquipmentNumber, Model, SerialNumber, Make, CalLabName, CalDate
FROM CalibrationReview
WHERE NOT EXISTS (SELECT AssignedID FROM tblEquipments WHERE AssignedID = CalibrationReview.EquipmentNumber))

Thanks,

Zath

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-15 : 10:02:37

can you explain the section in red ? What are you trying to achieve here ?


UPDATE tblEquipments
SET    AssignedID     = CalibrationReview.EquipmentNumber,
       ModelNumber    = CalibrationReview.Model,
       SerialNumber   = CalibrationReview.SerialNumber,
       testTechnology = (tblTestTechnology.id OR 1 if not found
                         WHERE dbo.CalibrationReview.EquipmentNumber = dbo.CalibrationMaster.EquipmentID
                         AND   CalibrationMaster.TestTechnology = tblTestTechnology.testTechnology),
       calLab         = (tblLabs.ID where tblLabs.LabName = CalibrationReview.CalLabName)
WHERE (SELECT EquipmentNumber, Model, SerialNumber, Make, CalLabName, CalDate
FROM  CalibrationReview
WHERE NOT EXISTS (SELECT AssignedID FROM tblEquipments WHERE AssignedID = CalibrationReview.EquipmentNumber))

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2007-03-15 : 10:06:15

Those are the items I don't know the syntax for.

Oh, just nevermind.

I'm doing it with code behind.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-15 : 10:17:06

How do you even include the tblLabs table?

Peter Larsson
Helsingborg, Sweden

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2007-03-15 : 10:27:00

I'm not sure of the syntax on this one.

I'm doing an insert and am given a basic example for update.

I'm just getting frustrated with it and need to move on.

Zath

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-15 : 10:31:02

This is the part given me trouble

WHERE NOT EXISTS (SELECT AssignedID FROM tblEquipments WHERE AssignedID = CalibrationReview.EquipmentNumber))

If you search for records NOT MATCHING OVER AssignedID,
how can you then even update with AssignedID?

Peter Larsson
Helsingborg, Sweden

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2007-03-15 : 11:02:01

I'm not updating, I'm inserting.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-15 : 11:10:09

Then you need something almost like this

INSERT	tblEquipments
	(
		EquipmentNumber,
		Model,
		SerialNumber,
		Make,
		CalLabName,
		CalDate
	)
SELECT		cr.EquipmentNumber,
		cr.Model,
		cr.SerialNumber,
		cr.Make,
		CASE WHEN e.TestTechnology IS NULL THEN 1 ELSE e.ID END,
		cr.CalDate
FROM		CalibrationReview AS cr
LEFT JOIN	tblEquipments AS e ON e.AssignedID = cr.EquipmentNumber
WHERE		e.AssignedID IS NULL

Peter Larsson
Helsingborg, Sweden

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2007-03-15 : 11:18:28

Now that's what I needed, a simple example just like that
so I can see the correct syntax.

Thanks so much!!!!!

Zath

Kristen
Test

22859 Posts

Posted - 2007-03-15 : 11:27:36

Can there be multiple CalibrationReview records for a tblEquipments record [that does not yet exist]?

if so you will need to use DISTINCT <Yuck!>, GROUP BY [if you want, say, MAX(cr.CalDate) ] or a NOT EXISTS type solution instead.

Kristen

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2007-03-15 : 12:26:44

Actually, tblEquipments has primary key = id autogenerated.

The tblEquipments.AssignedID should not be duplicated, although it
can be.

With Peso's insert example above, the last line:

WHERE e.AssignedID IS NULL

Should do the equivalent of:
WHERE NOT EXISTS (SELECT AssignedID FROM tblEquipments WHERE AssignedID = CalibrationReview.EquipmentNumber)

?????

Thanks,

Zath

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-15 : 12:29:24

Yes.

Peter Larsson
Helsingborg, Sweden

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2007-03-15 : 13:37:18

Ok, I am so close to getting this to work I can feel it.

Here's the sql:

INSERT tblEquipments
(
AssignedID,
ModelNumber,
SerialNumber,
Manufacturer,
calLab,
CalDate,
Status,
EquipmentType,
calibrationRequired,
Description
)
SELECT cr.EquipmentNumber,
cr.Model,
cr.SerialNumber,
tblM.ManufacturerID,
CASE WHEN calM.TestTechnology IS NULL THEN 1 ELSE tblT.ID END,
cr.CalDate,
tblS.StatusID,
'89',
calM.CalNotReq,
calM.Description
FROM CalibrationReview AS cr
LEFT JOIN tblEquipments AS e ON e.AssignedID = cr.EquipmentNumber
LEFT JOIN CalibrationMaster AS calM ON calM.EquipmentID = cr.EquipmentNumber
LEFT JOIN tblManufacturers AS tblM ON tblM.Manufacturer = cr.Make
LEFT JOIN tblTestTechnology AS tblT ON tblT.TestTechnology = calM.TestTechnology
LEFT JOIN tblStatus AS tblS ON tblS.Status = calM.Status
LEFT JOIN tblLocation AS tblL ON tblL.Location = calM.Status
WHERE e.AssignedID IS NULL

But am getting the error:
Violation of UNIQUE KEY constraint 'IX_tblEquipments'. Cannot insert duplicate key in object 'tblEquipments'.
The statement has been terminated.

Also, calM.CalNotReq is a bit. But should be opposite of what's read.
Is there a quick way to switch it without a case?
NOT(calM.CalNotReq) or something to that effect?

Thanks,

Zath

And below is the sql for tblEquipments just in case...

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblEquipments_tblEquipment_Type]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblEquipments] DROP CONSTRAINT FK_tblEquipments_tblEquipment_Type
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblEquipments_tblManufacturers]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblEquipments] DROP CONSTRAINT FK_tblEquipments_tblManufacturers
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_testTechnology_tblEquipments]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblEquipments] DROP CONSTRAINT FK_testTechnology_tblEquipments
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_ID_tblEquipmentRepair]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblCalibrationReview] DROP CONSTRAINT FK_ID_tblEquipmentRepair
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_CategoryId_tblEquipmentRepair]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblEquipmentRepair] DROP CONSTRAINT FK_CategoryId_tblEquipmentRepair
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblEquipments_Group_tblEquipments]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblEquipments_Group] DROP CONSTRAINT FK_tblEquipments_Group_tblEquipments
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblEquipments]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblEquipments]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblEquipment_Type]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblEquipment_Type]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblManufacturers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblManufacturers]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblTestTechnology]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblTestTechnology]
GO

CREATE TABLE [dbo].[tblEquipment_Type] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[equipmentType] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblManufacturers] (
[ManufacturerID] [int] IDENTITY (1, 1) NOT NULL ,
[Manufacturer] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblTestTechnology] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[testTechnology] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblEquipments] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[AssignedID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[testTechnology] [int] NULL ,
[Manufacturer] [int] NULL ,
[ModelNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SerialNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EquipmentType] [int] NULL ,
[Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Location] [int] NULL ,
[Status] [int] NULL ,
[ModifiedDate] [datetime] NULL ,
[ModifiedBy] [int] NULL ,
[Notes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[calibrationRequired] [bit] NULL ,
[calDate] [datetime] NULL ,
[calDueDate] [datetime] NULL ,
[calLab] [int] NULL ,
[AssetNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Picture_old] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Picture] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-15 : 13:48:21

SELECT DISTINCT cr.EquipmentNumber,

Peter Larsson
Helsingborg, Sweden

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2007-03-15 : 14:06:04

Thanks again but ran into another error:

The text, ntext, or image data type cannot be selected as DISTINCT.

But I've ran into this before and simply do this:
CAST(calM.Description AS NVARCHAR) AS Description

But the ONLY field that is text is e.Description.
I test ONLY the select portion and it works ok, so it must
be Description in the INSERT portion.

So, tried a CAST and get incorrect syntax near '('

INSERT tblEquipments
(
AssignedID,
ModelNumber,
SerialNumber,
Manufacturer,
calLab,
CalDate,
Status,
EquipmentType,
calibrationRequired,
CAST(Description AS NVARCHAR) AS Description )

Kristen
Test

22859 Posts

Posted - 2007-03-15 : 16:46:19

"And below is the sql for tblEquipments just in case..."

I can't find any definition of 'IX_tblEquipments', so I can't help with:

"Violation of UNIQUE KEY constraint 'IX_tblEquipments'"

You query is significantly different to what you posted originally, and would almost certainly have elicited different advice.

I'm not sure why you are comfortable with CASTing your Description to nvarchar - do you not care if it is truncated?, or do you not have any descriptions longer than 4,000 characters? (In which case why was the column defined as (n)Text rather than (n)Varchar in the first place?)

Kristen

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2007-03-16 : 09:27:23

After some investigating, I found the reason for the error.

If there is a record in there with the same combination of
ModelNumber and SerialNumber, the insert fails due to
the constraint.

So, in the following, how to adust the where statement to ignore
any record or not get any record if in cr.SerialNumber and cr.Model match a record in e.SerialNumber and e.ModelNumber?

Thanks again for the input.

Zath

INSERT tblEquipments
(
AssignedID,
ModelNumber,
SerialNumber,
Manufacturer,
calLab,
CalDate,
Status,
EquipmentType,
calibrationRequired,
Description
)
SELECT cr.EquipmentNumber,
cr.Model,
cr.SerialNumber,
tblM.ManufacturerID,
CASE WHEN calM.TestTechnology IS NULL THEN 1 ELSE tblT.ID END,
cr.CalDate,
tblS.StatusID,
'89',
CASE WHEN calM.CalNotReq = 1 THEN 0 WHEN calM.CalNotReq = 0 THEN 1 ELSE 0 END,
calM.Description
FROM CalibrationReview AS cr
LEFT JOIN tblEquipments AS e ON e.AssignedID = cr.EquipmentNumber
LEFT JOIN CalibrationMaster AS calM ON calM.EquipmentID = cr.EquipmentNumber
LEFT JOIN tblManufacturers AS tblM ON tblM.Manufacturer = cr.Make
LEFT JOIN tblTestTechnology AS tblT ON tblT.TestTechnology = calM.TestTechnology
LEFT JOIN tblStatus AS tblS ON tblS.Status = calM.Status
LEFT JOIN tblLocation AS tblL ON tblL.Location = calM.Status
WHERE e.AssignedID IS NULL

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-16 : 09:33:18

Replace
CASE WHEN calM.CalNotReq = 1 THEN 0 WHEN calM.CalNotReq = 0 THEN 1 ELSE 0 END,

with
CASE WHEN calM.CalNotReq = 0 THEN 1 ELSE 0 END,

Peter Larsson
Helsingborg, Sweden

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources