Correlated update statement - 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 2000 Forums

Transact-SQL (2000)

Correlated update statement

Author

Topic

Bmxpert
Starting Member

3 Posts

Posted - 2006-01-26 : 12:31:27

Hi all,

Can somebody tell me how I can get the following statement to work in SQL Server:


UPDATE Inschrijving I
SET I.opleidingId = SO.opleidingId
			FROM StudentOpleiding SO
			WHERE SO.studentId = I.studentId
			AND SO.opleidingId IN ('MAAF', 'MSCM', 'MPMP', 'MSO', 'MBPI')
WHERE I.opleidingId = 'MSCmw'

I presume it doesn't work because SQL Server does not allow aliasses in UPDATE statements. However, when I try this, I also get an error:


UPDATE Inschrijving
SET Inschrijving.opleidingId = SO.opleidingId
			FROM StudentOpleiding SO
			WHERE SO.studentId = Inschrijving.studentId
			AND SO.opleidingId IN ('MAAF', 'MSCM', 'MPMP', 'MSO', 'MBPI')
WHERE Inschrijving.opleidingId = 'MSCmw'

Basically I want to update all the "Inschrijving.opleidingId"s that have the 'MSCmw' value to any of these values ('MAAF', 'MSCM', 'MPMP', 'MSO', 'MBPI') depending on wich is stored in "StudentOpleiding". I am certain for that every 'MSCmw' value only one ('MAAF', 'MSCM', 'MPMP', 'MSO', 'MBPI') will be found.

any help would be appreciated
thx

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-26 : 12:43:58

C whether this helps ( taken from BOL)

Use Pubs
UPDATE titles
SET t.ytd_sales = t.ytd_sales + s.qty
FROM titles t, sales s
WHERE t.title_id = s.title_id
AND s.ord_date = (SELECT MAX(sales.ord_date) FROM sales)

or

Use Pubs
UPDATE titleauthor
SET title_id = titles.title_id
FROM titles INNER JOIN titleauthor
ON titles.title_id = titleauthor.title_id
INNER JOIN authors
ON titleauthor.au_id = authors.au_id
WHERE titles.title = 'Net Etiquette'
AND au_lname = 'Locksley'

Bmxpert
Starting Member

3 Posts

Posted - 2006-01-27 : 03:59:30

Hi all

I also came accross this solution searching the web. The problem is that my query is of another kind. I want to establish the correlation between my update statement and the subselect in the where clausule of the subselect statement. So i want to make a refence to my update table in the where clausule of my subselect.

I hope this clarifies what i'm looking for...

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-01-27 : 04:11:15

Try this ..

UPDATE Ins
SET Ins.opleidingId = SO.opleidingId
FROM Inschrijving Ins
inner join
StudentOpleiding SO
ON SO.studentId = Ins.studentId
WHERE SO.opleidingId IN ('MAAF', 'MSCM', 'MPMP', 'MSO', 'MBPI')
AND Ins.opleidingId = 'MSCmw'

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-01-27 : 04:29:32

Hi
There's a few problems with your SQL statement.
You just need to join your tables (on studentId presumably) then update the value in Inschrijving.opleidingId with that in StudentOpleiding.opleidingId.

UPDATE
	I
SET 
	I.opleidingId = SO.opleidingId
FROM
	dbo.Inschrijving AS I
	JOIN dbo.StudentOpleiding AS SO
		ON SO.studentId = I.studentId
WHERE
	I.opleidingId = 'MSCmv'

I'm assuming there will not ever be a value other than one of those in your delimited list in StudentOpleiding.opleidingId? If this is not the case, you just need to add the constraint to your WHERE clause.

Mark

Bmxpert
Starting Member

3 Posts

Posted - 2006-01-27 : 04:42:04

Thx


UPDATE I
SET I.opleidingId = SO.opleidingId
	FROM Inschrijving I
	JOIN StudentOpleiding SO ON SO.studentId = I.studentId
	WHERE SO.opleidingId IN ('MAAF', 'MSCM', 'MPMP', 'MSO', 'MBPI')
	AND I.opleidingId = 'MSCmw';

worked like a charm!

So basically I made the following mistake:

From the where clausule of my subselect statement i referenced to my update table. While actually i should have referenced from my update statement to a table in my subselect.

Thx again

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-01-27 : 11:06:11

[code]
UPDATE Inschrijving I
SET I.opleidingId = (select SO.opleidingId
FROM StudentOpleiding SO
WHERE SO.studentId = I.studentId
AND SO.opleidingId IN ('MAAF', 'MSCM', 'MPMP', 'MSO', 'MBPI'))
WHERE I.opleidingId = 'MSCmw'
[/code]
That works too, but join solution is better.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources