Where issue in my Update string.

[jgraves13]

Hello,

I am receiving an error message is seem at the second Where statement, I want the update to occur when the where statement is true see below. Any ideas on how to make this happen?

UPDATE    HL_Data
SET              Signon_ID = 'New_User_ID'
WHERE     dbo.HL_Jobs.Job_Name, dbo.HL_Jobs.Job_Step, dbo.HL_Data.Category_Number, 'Permanent' AS Status
FROM         dbo.HL_Data INNER JOIN
                      dbo.HL_Jobs ON dbo.HL_Data.Type_ID = dbo.HL_Jobs.Job_Number
WHERE     (dbo.HL_Data.Signon_ID = 'Old_User_ID') AND (dbo.HL_Data.Type_Number = '3')

 

[towerbase]

Have you checked out the syntax of the UPDATE statement? Try the MySQL website:

http://dev.mysql.com/doc/refman/5.1/en/update.html

Andrew
Hampshire, UK

 

[jgraves13]

I have, but I can seem to work a statement out that is error free... I was hoping for some suggestions... I now I am close to being right, but I am just missing something, I think...

 

[towerbase]

My suggestion is that you use the syntax of the UPDATE command as described in the MySQL documentation. If your statement doesn't conform to the syntax then it is very unlikely to work.

How do you know you are close to being right? What is the latest version of your UPDATE statement?

Andrew
Hampshire, UK

 

[elsenorjose]

The WHERE clause doesn't look right.

When updating a column, you need to specify what criteria you want to meet before updating. Example:

UPDATE    HL_Data
SET       Signon_ID = 'New_User_ID'
WHERE     Signon_ID = 'Old_User_ID'

What are you attempting to do in your WHERE clause?

If you're trying to update a value based on the values in another table, you'd need something like this:

update Table1 t1
join Table2 t2 on t1.ID=t2.t1ID
join Table3 t3 on t2.ID=t3.t2ID
set t1.Value=12345
where t3.ID=54321

 

[guelphdad]

hint: you can't have two WHERE clauses but you can have multiple conditions in your single WHERE clause.