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.
| Author |
Topic |
|
duf
Starting Member
39 Posts |
 Posted - 2012-06-06 : 17:27:35
|
| How to join such queries in one query:a = SELECT ID FROM CN WHERE KOD='123456';UPDATE OPIS SET OPIS='TEXT' WHERE KOD=a;Thanks. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
duf
Starting Member
39 Posts |
Posted - 2012-06-06 : 18:27:59
|
| And in this case:a = SELECT ID FROM CN WHERE KOD=123456';UPDATE CN SET KOD='000000' WHERE ID = a;At the same time the value is collected and update in the same table in the same query.UPDATE CN SET KOD='000000' WHERE ID=(SELECT ID FROM CN WHERE KOD=123456');This query throws an error; |
 |
|
|
vijays3
Constraint Violating Yak Guru
354 Posts |
Posted - 2012-06-06 : 19:57:58
|
quote:
Originally posted by duf
And in this case:a = SELECT ID FROM CN WHERE KOD=123456';UPDATE CN SET KOD='000000' WHERE ID = a;At the same time the value is collected and update in the same table in the same query.UPDATE CN SET KOD='000000' WHERE ID=(SELECT ID FROM CN WHERE KOD=123456');This query throws an error;
What error it is giving?It seems you have missed single qouteUPDATE CN SET KOD='000000' WHERE ID=(SELECT ID FROM CN WHERE KOD='123456');Vijay is here to learn something from you guys. |
|
|
|
duf
Starting Member
39 Posts |
Posted - 2012-06-07 : 01:48:18
|
quote:
Originally posted by vijays3
quote:
Originally posted by duf
And in this case:a = SELECT ID FROM CN WHERE KOD=123456';UPDATE CN SET KOD='000000' WHERE ID = a;At the same time the value is collected and update in the same table in the same query.UPDATE CN SET KOD='000000' WHERE ID=(SELECT ID FROM CN WHERE KOD=123456');This query throws an error;
What error it is giving?
Error: You can't specify target table 'CN' for update in FROM clause'. |
|
|
|
vijays3
Constraint Violating Yak Guru
354 Posts |
Posted - 2012-06-07 : 08:13:42
|
quote:
Originally posted by duf
quote:
Originally posted by vijays3
quote:
Originally posted by duf
And in this case:a = SELECT ID FROM CN WHERE KOD=123456';UPDATE CN SET KOD='000000' WHERE ID = a;At the same time the value is collected and update in the same table in the same query.UPDATE CN SET KOD='000000' WHERE ID=(SELECT ID FROM CN WHERE KOD=123456');This query throws an error;
What error it is giving?
Error: You can't specify target table 'CN' for update in FROM clause'.
I tried here with sample data and this working create table cn (id int,kod varchar(20))insert into cn(id,kod)select 1,'123456'union allselect 1,nullunion all select 1,nullUPDATE CN SET KOD='000000' WHERE ID=(SELECT ID FROM CN WHERE KOD='123456')SELECT * FROM CNIf possible can you provide sample data of your table so that we can try to produce same error here.Are you using SQL server or MYSQL ?I guess you are using MYSQL then go throughhttp://www.xaprb.com/blog/2006/06/23/how-to-select-from-an-update-target-in-mysql/ Vijay is here to learn something from you guys. |
|
|
|
|
|
|
Combine queries