Update with a WHERE clause 2

[FowlerConsulting]

I need to update the contents table for every child entity of parent entity, and the parent entity itself.

I am an Oracle DBA, and I have to do somthing like this in MS SQL:

UPDATE Contents
	Font = 'Arial'
WHERE Contents.Entity_ID IN
	(SELECT Contents.Entity_ID 
	FORM Contents, Entities
      	WHERE Contents.Entity_ID = Entities.Entity_ID 
	AND Entities.Parent_ID = 94)

Here are the tables:

[Entities]
Entity_ID
Parent_ID
Creator_ID
Entity_Name
Entity_Name_Abr
Entity_Type
Entity_Status
Public_Entity
Time_Stamp

[Contents]
Entity_ID
Font
BGColor
Time_Stamp

Thanks

Steven Fowler
info@fowlerconsulting.com

http://www.fowlerconsulting.com

 

[foxdev]

If the command you typed works in Oracle (with the exception of your misspelling of "FROM", of course), then I think all you need to do to get it to work in SQL Server is to add the required SET keyword, as in:

[tt]UPDATE Contents
Set Font = 'Arial'
WHERE Contents.Entity_ID IN
(SELECT Contents.Entity_ID
FROM Contents, Entities
WHERE Contents.Entity_ID = Entities.Entity_ID
AND Entities.Parent_ID = 94)[/tt]


Robert Bradley

http://www.foxdev.com/

 

[christine1]

I'm not clear on your question, but I have a few of my own.

Which column of which tables are you updating?

In other words is this what you want to do, if so you were only missing your SET clause:

UPDATE Contents
SET Font = 'Arial'
WHERE Contents.Entity_ID IN
(SELECT Contents.Entity_ID
FROM Contents, Entities
WHERE Contents.Entity_ID = Entities.Entity_ID
AND Entities.Parent_ID = 94)


Let me know how it turns out.

Christine1

 

[FowlerConsulting]

Here is what I got to work, thanks for the help!

UPDATE Contents SET
	Font_Face = @FontFace,
	BGColor = @BGColor,
	Font_Color = @FontColor,
	Link = @LinkColor,
	ALink = @ALinkColor,
	VLink = @VLinkColor
WHERE 
Entity_ID IN (SELECT Entity_ID FROM Entities  WHERE Parent_ID = 94) or
Entity_ID IN (SELECT Entity_ID FROM Entities  WHERE Parent_ID = 0 and Entity_ID = 94)

Steven Fowler
info@fowlerconsulting.com

http://www.fowlerconsulting.com

 

[christine1]

Put the word SET in front of all columns you want to update.

Don't forget to declare your variables.

Good Luck!

Christine1

 

[TomSark]

And.. SQL Server can do some cool things that Oracle cant such as updating based on a join condition.

You'll find that transact-sql has an more powerful programming features over Oracles PL/SQL...

Tom

 

[FowlerConsulting]

Yes tom, here is the final SP

CREATE PROCEDURE sp_Update_Site_Colors
	@Partner_ID	int,
	@FontFace	char(50),
	@BGColor	char(6),
	@FontColor	char(6),
	@LinkColor	char(6),
	@ALinkColor	char(6),
	@VLinkColor	char(6)
AS
SET NOCOUNT ON
UPDATE Contents SET
	Font_Face = @FontFace,
	BGColor = @BGColor,
	Font_Color = @FontColor,
	Link = @LinkColor,
	ALink = @ALinkColor,
	VLink = @VLinkColor
WHERE 
Entity_ID IN (SELECT Entity_ID FROM Entities  WHERE Parent_ID = @Partner_ID) or
Entity_ID IN (SELECT Entity_ID FROM Entities  WHERE Parent_ID = 0 and Entity_ID = @Partner_ID)

Steven Fowler
info@fowlerconsulting.com

http://www.fowlerconsulting.com

 

[foxdev]

You'll find that transact-sql has an more powerful programming features over Oracles PL/SQL

Golly, Tom...I've had to work with Oracle a bit, and I want to stay as far away from it as I can because I enjoy working with MSQSLS much, much better. Given my bias towards SQLS, I still find that I have to quibble with your statement.

Robert Bradley

http://www.foxdev.com/

 

[TomSark]

We can debate this probably for a long time, but Microsoft has put a lot of cool functions in T-SQL that Oracle severly lacks... Microsoft even has a more cool stuff that Sybase does (which uses T-SQL also). Just compare function lists in PL/SQL vs. T-SQL. Msoft has tons more...

Oracle does have 2 functions that Msoft doesn't have, that do make things useful.. One of them is the connect by..using functionality for self-referencing tables that retrieves a hierarchical relationship, but I wouldn't use Oracle for just that...

Tom

 

[foxdev]

You're right, it could be a long and inconclusive debate - and I'd find myself in the odd and undesirable position of trying to defend Oracle, which I'd just as soon never see again.

One example from my thought-bin: Oracle supports user-defined functions; MSSQLS won't get that until SQLS2000.

Further, in a review in PC Magazine about a year ago, their only major complaint with SQLS was its relatively sparse programming language in comparison to many of its competitors. I believe this was the 7/99 "Mission Critical" database review.

Robert Bradley

http://www.foxdev.com/

 

[TomSark]

Since SQL 2000 is out now, and it is blowing the pants off of Oracle , I don't believe that there are too many issues like this.

Oh well, I would like to end this debate now. LOL

Robert, sorry to put you in the position where you have to defend Oracle! LOL

Tom