dont know what is wrong with my query

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

General SQL Server Forums

New to SQL Server Programming

dont know what is wrong with my query

Author

Topic

daneil
Starting Member

2 Posts

Posted - 2010-08-11 : 06:06:52

Hi everyone, can someone help me check my query? i am new to sql and i am using sql express. i found some sources form the internet and come up with this query:-

UPDATE Invoices
SET Invoices_1.Corp_DeptID = Invoices.Corp_DeptID
FROM Invoices AS Invoices_1 INNER JOIN
Invoices ON Invoices_1.Booking_Ref = Invoices.Booking_Ref AND Invoices_1.Corp_DeptID <> Invoices.Corp_DeptID
WHERE (Invoices.RecType = 0) AND (Invoices.Corp_DeptID <> 0) AND (Invoices_1.Corp_DeptID = 0) AND (Invoices_1.RecType = 11)

the execution error says:
it says the multi-part "Invoices_1.Corp_DeptID" could not be bound

I have an invoice table with this 3 column - booking_ref, Corp_DeptID, RecType

i want to duplicate the same invoice table and update the duplicated table. the conditions are:-

invoice table:
rectype = 0
corp_deptid <> 0

duplicate invoice table:
rectype = 11
corp_id = 0

and the 2 table should not have the same corp_deptid and the booking_ref must be the same

someone help pleaseee =(

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-08-11 : 06:43:28

Simply change the query like below -

UPDATE Invoices
SET Invoices.Corp_DeptID = Invoices_1.Corp_DeptID
FROM Invoices AS Invoices_1 INNER JOIN
Invoices ON Invoices_1.Booking_Ref = Invoices.Booking_Ref AND Invoices_1.Corp_DeptID <> Invoices.Corp_DeptID
WHERE (Invoices.RecType = 0) AND (Invoices.Corp_DeptID <> 0) AND (Invoices_1.Corp_DeptID = 0) AND (Invoices_1.RecType = 11)

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

daneil
Starting Member

2 Posts

Posted - 2010-08-11 : 21:41:38

Thanks a lot, vaibhavktiwari83. the query you gave posted i tried already. but it change the wrong value. so i changed the condition a bit and it works fine =D cheers~!!

UPDATE Invoices
SET Corp_DeptID = Invoices_1.Corp_DeptID
FROM Invoices AS Invoices_1 INNER JOIN
Invoices ON Invoices_1.Booking_Ref = Invoices.Booking_Ref AND Invoices_1.Corp_DeptID <> Invoices.Corp_DeptID
WHERE (Invoices.RecType = 11) AND (Invoices.Corp_DeptID = 0) AND (Invoices_1.Corp_DeptID <> 0) AND (Invoices_1.RecType = 0)

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-08-12 : 02:53:46

I dont think this can be differ, I will try it out because i am surprised.

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Kristen
Test

22859 Posts

Posted - 2010-08-12 : 03:00:38

You have

UPDATE Invoices

and then

SET Invoices_1.Corp_DeptID

You need to update the columns in the table specified in the UPDATE statement. Change to

UPDATE Invoices_1

or

SET Invoices.Corp_DeptID

or just

SET Corp_DeptID

which will always use the table being updated.

For me, using "Invoices" table twice in the query I would give an alias to both tables, and use the Alias Name in the UPDATE statement (we traditionally use the alias "U" for the table being updated

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources