update syntax 2

[CRuser89]

hello,

I am very new to sql server. I've been programming in oracle so I know that sql server syntax is quite different. Can you please tell me if my update statement below has the correct syntax?

Thanks in advance

UPDATE employee
SET employee.id = personnel.id
FROM employee, personnel
WHERE employee.primaryKey = personnel.foreignKey

Also, can you please tell me how to set employee.id equal to the last 2 digits of the personnel id?

Thanks,

 

[SQLDenis]

One way

UPDATE e
SET e.id  = RIGHT(p.id,2)
FROM employee e 
JOIN personnel p
ON employee.primaryKey = personnel.foreignKey

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

Shouldn't the ON part also use the table aliases?

UPDATE e
SET e.id  = RIGHT(p.id,2)
FROM employee e 
JOIN personnel p
ON [!]e[/!].primaryKey = [!]p[/!].foreignKey

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

so here are 2 ways, the first one with aliases, the second one with out and also the old join syntax

UPDATE e
SET e.id  = RIGHT(p.id,2)
FROM employee e 
JOIN personnel p
ON e.primaryKey = p.foreignKey

UPDATE employee
SET employee.id  = RIGHT(personnel.id,2)
FROM employee, personnel
WHERE employee.primaryKey = personnel.foreignKey

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

yep,I realized that i forgot it and I did post a revised version(s)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[CRuser89]

Thank you both very much for your help