Syntax question - 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

General SQL Server Forums

New to SQL Server Programming

Syntax question

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-28 : 11:26:28

having trouble piecing this together.

In my CIVIL db, i have the following tables:

Subjects
Receipts
GCSOReceipts
GCSOServed

I need to populate the Receiptno field in Subjects by concatenating a field from GCSO receipts with some other data. So far, i've tried:


Update civil.dbo.receipts
set receiptno='GCSOCIV'+civil.dbo.GCSOReceipt.RecptUniqueID+'000ENF'

No dice

How might i get this done?

thanks

james

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-28 : 11:29:44

Might this work???:

UPDATE civil.dbo.SUBJECTS
SET receiptno = 'GCSO00CIV'+(SELECT(RecptUniqueID) FROM GCSOServed)+'000ENF'

total shot in the dark.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-28 : 11:30:46

whats the columns through which tables are related? it should be like below

make sure you put correct condition


Update t
set t.receiptno='GCSOCIV'+ g.RecptUniqueID+'000ENF'
from civil.dbo.receipts r
inner join civil.dbo.GCSOReceipt g
on <some relating condition here>

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-28 : 11:41:44

that's the problem at this point. The Subjects table is empty right now, needing to get populated throught this script. What i'm inclined to do at this point is create the same columns in the GCSOReceipts and GCSOServed that exist in the Subjects and Receipts table, set the values in those new columns and insert them back into their final tables.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-28 : 11:46:49

sorry you last explanation doesnt make sense

your intial post told you want to update civil.dbo.receipts using civil.dbo.GCSOReceipt. it didnt even have reference of Subjects. can you clarify which table you're trying to update?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-28 : 11:50:12

Sorry. Civil.dbo.GCSOReceipts has data that will populate civl.dbo.receipts. Civil.dbo.GCSOServed has data that will populate civil.dbo.subjects. There are a few columns in both GCSO named tables will populate other tables as well, but the bulk will be in what i outlined here.

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-28 : 11:54:34

Do you have a table Called MURDER1?

SELECT * FROM MURDER1 WHERE Lunatic = 'George Zimmerman'

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-28 : 11:55:09

so does that mean you need two updates? or are they two inserts? As per your current explanation i feel like they're inserts

few things to clarify

1. do the tables civl.dbo.receipts and civil.dbo.subjects have some data now?
2. Are you trying to add some more info into existing rows? in which case its an update
3. Is the attempt to add new rows as such into tables? if yes, its insert
4. what are fk columns within tables? ie how are they related? what all columns you need data to be populated from Civil.dbo.GCSOReceipts?

In any case, best bet would be to post some sample data from tables as well as their structure and explain how you want data to be populated

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-28 : 12:05:38

Dude,

don't get me politically ranting here. I do enough of that on Google+!

quote:
Originally posted by X002548

Do you have a table Called MURDER1?

SELECT * FROM MURDER1 WHERE Lunatic = 'George Zimmerman'

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-28 : 12:06:33

Visakh:

thanks. I jumped into this one too quickly without planning. I know what i have to do in this case and am on it.

Thanks for the wake up call.

-james

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-28 : 12:11:28

quote:
Originally posted by WJHamel

Visakh:

thanks. I jumped into this one too quickly without planning. I know what i have to do in this case and am on it.

Thanks for the wake up call.

-james

No problem . Take your time
Make sure you think in suggested lines and come back with required information for us to help you better and faster

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources