I have a question, what kind of Query or function do I have to use to UPDATE multiple record that I just want to add a letter at the end of the existing OrderNum.
Before
ID NAME OrderNum
1 Pete WEB123
2 Paul WEB124
3 Sam WEB125
4 Tim WEB126
After
ID NAME OrderNum
1 Pete WEB123A
2 Paul WEB124A
3 Sam WEB125A
4 Tim WEB126A
Problem is very simple
I want to update sum of a field from another table to first table
TABLE ONE:
==========
ItemID
QtyInStock
Table TWO:
==========
BatchID
ItemID
Qty
I want to Update the QtyInStock of First Table with Sum(Batch.Qty)
here is the query i am writing but giving error
UPDATE ITEMS
SET
INSTOCKQTY=CASE WHEN QtyInBatch>1 THEN QTYINBATCH ELSE 0 END
FROM ITEMS, (
SELECT ITEMS.ITEMID, SUM(Batch.Qty) AS QtyInBatch FROM Batch INNER JOIN Items ON Batch.ItemID = Items.ItemID GROUP BY ITEMS.ITEMID
)
appericiating anyones help in advance
FAZEEL AMJAD
Systems Engineer
Crystal Technologies
Got a beginner question here...
Let's say I have a database table that houses server information with four columns: make, model, serial #, ip address. And assume there are ten rows with that information filled out. How could I display all the rows of information on a single webpage (ASP.NET), with all the fields being editable; and a single save button that would send any changes to the database (in reality I guess it would be sending all rows and fields to the database, and just overwrite the previous data).
Could a page such as that be created using FrontPage 2003 or Dreamweaver MX 2004?
This would be strictly for updating information. I would have a separate form for adding a new entry.
Thanks for your help.
I have an 'update' query that looks like this:
update wce_contact
set blank = 'missing'
where website in ('www.name1.co.uk','www.name2.co.uk','www.name3.co.uk')
I know this query will set 'blank' to missing when it matches the above websites. However if i wanted to set blank to 'missing' where mail1date is not null and mail2date is not null (keep going to mail18date not null) how exactly would i go about this?
I guess it would be a case of adding another bracket somewhere but im unsure?
Hi All,
I want to know that how we will be able to update multiple rows in single transaction.
e.g If original database is
S_No Data
1 -
2 -
3 -
4 -
After
S_No Data
1 1
2 3
3 6
4 10
I need to update more than one record at once. I have ~ 100 records that I have to update and don't want to execute query 100 times.
My query looks like this:
Update Table1
Set Table1.field1 = ( select Table2.field1 from Table2 where Table2.field2 IN ('a', 'b', 'c')
where Table1.field2 IN ( 'a', 'b', 'c')
It obviously failed because subquery returned more than one value and error message stated that I can't use '=' operator in this case.
My question: how could I update the same column from many records in one execution?
I am renovating an existing application and am converting the existing passwords into hashed values using SHA1. I know how to compute the hashed values as a byte array for each record. What I don't know how to do easily is update all of the records i a single call to the database. Normally, I would just do the following:UPDATE HashedPassword = someValue WHERE UserID = 101;
UPDATE HashedPassword = someOtherValue WHERE UserID = 102;
...
What I don't know is what someValue and someOtherValue should be. How do I convert my byte array into string representation that SQL will accept? I usually execute multiple statements using Dim oCmd as New SqlCommand(sSQL, MyConn) and then call oCmd.ExecuteNonQuery().
Alternatively, I found the following code that uses the byte array directly but only shows a single statement. How could I use it to execute multiple statements as shown above?'FROM http://aspnet.4guysfromrolla.com/articles/103002-1.2.aspx
'2. Create a command object for the query
Dim strSQL as String = _
"INSERT INTO UserAccount(Username,Password) " & _
"VALUES(@Username, @Password)"
Dim objCmd as New SqlCommand(strSQL, objConn)
'3. Create parameters
Dim paramUsername as SqlParameter
paramUsername = New SqlParameter("@Username", SqlDbType.VarChar, 25)
paramUsername.Value = txtUsername.Text
objCmd.Parameters.Add(paramUsername)
Dim paramPwd as SqlParameter
paramPwd = New SqlParameter("@Password", SqlDbType.Binary, 16)
paramPwd.Value = hashedBytes
objCmd.Parameters.Add(paramPwd)
'Insert the records into the database
objConn.Open()
objCmd.ExecuteNonQuery()
objConn.Close()
I have two tables that can be created with sample data using the DDL at the bottom of this post. What I'm looking to do is update the QtyReceived column in tblPurchaseOrderLineDetail from the Qty column in tblReceivedItems. However, the tricky part that I can't figure out is splitting these quantities out over multiple lines. I should only be allowed to receive up to the QtyOrdered column in tblPurchaseOrderLineDetail.
For a specific example from the sample data we'll look at PurchaseOrderDetailID 28526. From the tblReceivedItems, there are three records with quantities of 48, 48, and 20. From the tblPurchaseOrderLineDetail there are three records of QtyOrdered of 55, 45, and 20. What I would like to happen is fulfill the records in the tblPurchaseOrderLineDetail sequentially (essentially in order of ExpectedDate). So, the QtyReceived would be 55, 45, and 16 for the corresponding records. If there is already a quantity in the QtyReceived column, but it's less than the QtyOrdered column, the quantity needs to be added to the column (not overwritten).
DDL To CREATE Sample Tables and Data:
CREATE TABLE [dbo].[tblReceivedItems](
[ID] [int] IDENTITY(1,1) NOT NULL,
[PurchaseOrderDetailID] [int] NULL,
[Qty] [int] NULL)
SET IDENTITY_INSERT [dbo].[tblReceivedItems] ON
INSERT [dbo].[tblReceivedItems] ([ID], [PurchaseOrderDetailID], [Qty]) VALUES (1, 28191, 48)
[code]....
I have this update statement that works for one record. How do I write it to include multiple records at once. Please see sample below.
update
mklopt
set
FRMDAT =
'12/31/2014'
where
JOBCOD =
'PH14789'
I also want to include the following instead of running it one at a time
PH17523
PH17524
PH17525
PH17553
PH17555
PH17556
PH17557
PH17558
PH17571
PH17573
PH17574
PH17575
PH17576
PH17577
PH1757
I have a After insert, update trigger. When I update multiple records with unique constraints column in it update fails. But if this a single record update it works.
Could like to know the reason.
IF EXISTS (SELECT 1 FROM RoleUser WHERE User_Id = 12346 AND Role_Code = 'CRC')
UPDATERoleUser
SETAccess= 1,
Worklist= 0,
Supervisor= 0
WHERERole_Code= 'CRC'
[Code] ....
I have two records in the source with information ID, RevisionID, Description, Region
There are two lookup files one with ID,Description amd other with ID, Region
I wish to update my two source records with performing lookup with these two files.To get the correct description and region data. How to do this in ssis DFT.
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
ID effdate termdate
556868 1999-01-01 1999-06-30
556868 1999-07-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-01-31
556872 2004-02-01 2004-02-29
output should be ......
ID effdate termdate
556868 1999-01-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-02-29
I need a little help here..I want to transfer ONLY new records AND update any modified recordsfrom Oracle into SQL Server using DTS. How should I go about it?a) how do I use global variable to get max date.Where and what DTS task should I use to complete the job? Data DrivenQuery? Transform data task? How ? can u give me samples. Perhaps youcan email me the Demo Package as well.b) so far, what I did was,- I have datemodified field in my Oracle table so that I can comparewith datelastrun of my DTS package to get new records- records in Oracle having datemodified >Max(datelastrun), and transferto SQL Server table.Now, I am stuck as to where should I proceed - how can I transfer theserecords?Hope u can give me some lights. Thank you in advance.
View 2 Replies View RelatedHere is my requirement, How to handle using SSIS.
My flatfile will have multiple columns like :
ID key1 key2 key3 key 4
I have SP which accept 3 parameters ID, Key, Date
NOTE: Key is the coulm name from the Excel. So my sp call look like
sp_insert ID, Key1, date
sp_insert ID, Key2,date
sp_insert ID, Key3,date
I'm new to this forum.
This 'problem' has occured many times, but I've always found a way around it.
I have pages with datagrids, in which a user can edit a certain fields and then update the tables with new data. Lets say when a user edit a Name field and a money field. If he/she left those two fields blank, the table is automatically updated with a <null> (for the name field) and a 0 (for the money field.) Both these columns were set up to allow Null values.
Anyone has an idea why they were updated that way? And is there like a standard on how the data types are updated if a field is left blank?
Thank you very much.
Hey all,I am having some serious trouble getting the correct syntax for a select statement to work the way I need it, any help I could get on this would be greatly appreciated.I have a table called Units which stores computers and a table called Software which stores software. I have a bit field in Units called OEM, when this is set to true I don't want the select statement to pull this unit down when I am assigning software to other units.Here is my select statement: SELECT Software.SID, Software.SN, Software.Name, Users.First + ' ' + Users.Last AS 'Assigned User', Units.Make + '-' + Units.Model AS 'Assigned Unit' FROM dbo.Software LEFT JOIN dbo.Units ON Software.SN = Units.SN LEFT JOIN dbo.Users ON Units.UID = Users.UID WHERE (Units.OEM = 'FALSE') AND (Software.SN LIKE '%' + @SearchString + '%')Everything works as expected, unless of course the unit has no software assigned to it yet, it won't return it because its not tied to a Units.OEM field. Is there anyway to have it return ALL records that even arn't joined OR are joined but have OEM set to false?Thanks, let me know if I need to clear anything up.-Matthew
View 2 Replies View Relatedi want to select all the user tables within the database and then all the records with in each table.
plz tell me one query to do this.
ex: suppose x and y are user tables and x contain 10 records and y contains 20 records . i want a query which displays all 30 records together.
What is the simplist/correct way to delete multiple records from multiple tables. Any help appreciated. Thanks! (Yes, I'm totally new to this.)
delete dbo.tblcase
where dbo.tblcase.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')
delete dbo.tblcaseclient
where dbo.tblcaseclient.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')
delete dbo.tblcaseinformation
where dbo.tblcaseinformation.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')
delete dbo.tblcaselawyer
where dbo.tblcaselawyer.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')
delete dbo.tblcaseprosecutor
where dbo.tblcaseprosecutor.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')
I need to update multiple columns in a table with multiple condition.
For example, this is my Query
update Table1
set weight= d.weight,
stateweight=d.stateweight,
overallweight=d.overallweight
from
(select * from table2)d
where table1.state=d.state and
table1.month=d.month and
table1.year=d.year
If table matches all the three column (State,month,year), it should update only weight column and if it matches(state ,year) it should update only the stateweight column and if it matches(year) it should update only the overallweight column
I can't write an update query for each condition separately because its a huge select
hi,friends
we show record from multiple table using single 'selectcommand'.
like....
---------
select *
from cust_detail,vend_detail
---------
i want to insert value in multiple database table(more than one) using single 'insert command'.
is it possible?
give any idea or solution.
i want to update value in multiple database table(more than one) using single 'update command'
i want to delete value in multiple database table(more than one) using singl 'delete command'
it is possible?
give any idea or solution.
it's urgent.
thanks in advance.
UPDATE #TempTableESR SET CTRLBudEng = (SELECT SUM(Salaries) from ProjectBudget WHERE Project = @Project)UPDATE #TempTableESR SET CTRLBudTravel = (SELECT SUM(Travels) from ProjectBudget WHERE Project = @Project)UPDATE #TempTableESR SET CTRLBudMaterials = (SELECT SUM(Materials) from ProjectBudget WHERE Project = @Project)UPDATE #TempTableESR SET CTRLBudOther = (SELECT SUM(Others) from ProjectBudget WHERE Project = @Project)UPDATE #TempTableESR SET CTRLBudContingency = (SELECT SUM(Contingency) from ProjectBudget WHERE Project = @Project)above is the UPDATE command i am using in one of my stored procedures. I have to SELECT from my ProjectBudget table 5 times to update my #TempTableESR table. is there an UPDATE command i can use which would let me update multiple fields in a table using one SELECT command?
View 1 Replies View RelatedI'm trying to add a sum function to my query to sum the 12 records I get back in order to see if my total charges match the amounts within a different database table. Unfortunately, now when I run the query using the sum function, I am getting very large numbers back for the charge amt and I can't figure out why. Here is the query I've written:
IF OBJECT_ID('TEMPDB..#TMP1C') IS NOT NULL DROP TABLE #TMP1C
IF OBJECT_ID('TEMPDB..#TMP2C') IS NOT NULL DROP TABLE #TMP2C
select prin_sbb, sub_acct_no_sbb, res_name_sbb, ext_stat_sbb, cur_bal_sbb, VIP_FLG_SBB
INTO #TMP1C
from Vantage.dbo.SBB_BASE (NOLOCK)
where PRIN_SBB in (6000,7500)
[Code] .....
Not entirely sure what I am doing wrong, or why the numbers are coming back so high.
I'm looking for a way of taking a query which returns a set of date time fields (probable maximum of 20 rows) and looping through each value to see if it exists in a separate table.
E.g.
Query 1
Select ID, Person, ProposedEvent, DayField, TimeField
from MyOptions
where person = 'me'
Table
Select Person, ExistingEvent, DayField, TimeField
from MyTimetable
where person ='me'
Loop through Query 1 and if it finds ANY matching Dayfield AND Timefield in Query/Table 2, return the ProposedEvent (just as a message, the loop could stop there), if no match a message saying all is fine can proceed to process form blah blah.
I'm essentially wanting somebody to select a bunch of events in a form, query 1 then finds all the days and times those events happen and check that none of them exist in the MyTimetable table.
I'm having an Employee table with a Salary field. How can we increate thesalary of the employees with following conditions:1) salary between 1000 and 10000 : increase 25%2) salary between 10000 and 20000 : increase 15%3) salary between 20000 and 30000 : increase 5%Surely you can create a cursor to solve this. But the question is, Is itpossible to solve this in a single query, if no what is most optimizedway?
View 4 Replies View RelatedI want my query to list all SSNS that have more than one record in the table. I have this query:
Code:
SELECT SSN, name4, count(*) from [1099_PER]
group by SSN, name4
having count(SSN) > 1
It does retrieve the right SSNS and tells me how many times the SSN occurs in the table. However, I want my query results to display their full records.
For example
SSN NAME4 COUNT
123445555 WALTER - 4
I want the query to show me all four records for this SSN. I thought removing the count field would do this, but it still gives me only one instance of each SSN.
I want to write a function, which accept 3 parameters, 1 TableName 2 ColumnName 3 DateValue, and returns number of records in that table for that particular date(in parameter date), I have written below function but it is not returning the desired result.
CREATE FUNCTION dbo.[f_Rec_cnt]
(@InTableName NVARCHAR(100),
@InDtColName NVARCHAR(50),
@InDate NVARCHAR(50)
)
RETURNS INT
[Code] .....
I'm trying to use the NEWID function in dynamic SQL and get an errormessage Incorrect syntax near the keyword 'ORDER'. Looks like I can'tdo an insert with an Order by clause.Here's the code:SELECT @SQLString = N'INSERT INTO TMP_UR_Randoms(Admit_DOCID,Client_ID, SelectDate, SelectType,RecordChosen)'SELECT @SQLString = @SQLString + N'(SELECT TOP ' + @RequFilesST + 'Admit_DOCID, Client_ID, SelectDate, SelectType, RecordChosen FROMFD__UR_Randoms 'SELECT @SQLString = @SQLString + N'WHERE SelectType = ' +@CodeRevTypeSt + ' AND SelectDate = ''' + @TodaySt + '''' + ' ORDERBY NEWID())'execute sp_executesql @SQLStringMy goal is to get a random percentage of records.The full SP follows. In a nutshell - I pull a set of records fromFD__Restart_Prog_Admit into a temporary table called FD__UR_Randoms.I need to retain the set of all records that COULD be eligible forselection. Based on the count of those records, I calculate how manyneed to be pulled - and then need to mark those records as "chosen".I'd just as soon not use the TMP_UR_Randoms table - I went that routebecause I ran into trouble with a #Tmp table in the above SQL.Can anyone help with this? Thanks in advance.Full SQL:CREATE PROCEDURE TP_rURRandomReview @ReviewType varchar(30)--Review type will fill using Crystal Parameter (setting defaults)AS/* 6.06.2007UR Requirements:(1) Initial 4-6 month review: 15% of eligible admissions(eligible via days in program and not yet discharged) must be reviewed4-6 months after admission. This review will be done monthly -meaning we'll have a moving target of names (with overlaps) whichcould be pulled from each month. (Minimum 5 records)(2) Subsequent 6-12 month review: Out of those already reviewed(in #1), we must review 25% of them (minimum of 5 records)(3) Initial 6-12 month review: Exclude any included in 1 or 2 -review 25% of admissions in program from 6-12 months (minimum 5)*/DECLARE @CodeRevType intDECLARE @PriorRec int -- number of records already markedeligible (in case user hits button more than once on same day for sametype of review)DECLARE @CurrRec int --number of eligible admitsDECLARE @RequFiles intDECLARE @SQLString nvarchar(1000)DECLARE @RequFilesSt varchar(100)DECLARE @CodeRevTypeSt char(1)DECLARE @TodayNotime datetimeDECLARE @TodaySt varchar(10)--strip the time off todaySELECT @TodayNotime = DateAdd(day,datediff(day,0,GetDate()),0)--convert the review type to a codeSelect @CodeRevType = Case @ReviewType when 'Initial 4 - 6 Month' then1 when 'Initial 6 - 12 Month' then 2 when 'Subsequent 6 - 12 month'then 3 END--FD__UR_Randoms always gets filled when this is run (unless it waspreviously run)--Check to see if the review was already pulled for this recordSELECT @PriorRec = (Select Count(*) FROM FD__UR_Randoms whereSelectType = @CodeRevType and SelectDate = @TodayNotime)If @PriorRec 0 GOTO ENDThis--************************************STEP A: Populate FD__UR_Randomstable with records that are candidates for review************************If @CodeRevType = 1BEGININSERT INTO FD__UR_Randoms (Admit_DOCID, Client_ID, SelectDate,SelectType,RecordChosen)(SELECT pa.OP__DOCID, pa.Client_ID,Convert(varchar(10),GetDate(),101) as SelectDate, @CodeRevType, 'F'FROM dbo.FD__RESTART_PROG_ADMIT paInner join FD__Client cOn pa.Client_ID = c.Client_IDWHERE Left(c.Fullname,2) <'TT' AND (Date_Discharge IS NULL)AND(DATEDIFF(d, Date_Admission, GETDATE()) 119)AND (DATEDIFF(d, Date_Admission, GETDATE()) <= 211)AND pa.OP__DOCID not in (Select Admit_DOCID from FD__UR_Randomswhere RecordChosen = 'T'))ENDIf @CodeRevType = 2--only want those that were selected in a batch 1 - in program 6-12months; selected for first reviewBEGININSERT INTO FD__UR_Randoms (Admit_DOCID, Client_ID, SelectDate,SelectType,RecordChosen)(SELECT pa.OP__DOCID, pa.Client_ID,Convert(varchar(10),GetDate(),101) as SelectDate, @CodeRevType, 'F'FROM dbo.FD__RESTART_PROG_ADMIT paInner join FD__Client cOn pa.Client_ID = c.Client_IDWHERE Left(c.Fullname,2) <'TT' AND (Date_Discharge IS NULL)AND(DATEDIFF(d, Date_Admission, GETDATE()) 211)AND (DATEDIFF(d, Date_Admission, GETDATE()) < 364)AND pa.OP__DOCID in (Select Admit_DOCID from FD__UR_Randomswhere SelectType = 1 AND RecordChosen= 'T'))ENDIf @CodeRevType = 3--only want those that were not in batch 1 or 2 - in program 6 to 12monthsBEGININSERT INTO FD__UR_Randoms (Admit_DOCID, Client_ID, SelectDate,SelectType,RecordChosen)(SELECT pa.OP__DOCID, pa.Client_ID,Convert(varchar(10),GetDate(),101) as SelectDate, @CodeRevType, 'F'FROM dbo.FD__RESTART_PROG_ADMIT paInner join FD__Client cOn pa.Client_ID = c.Client_IDWHERE Left(c.Fullname,2) <'TT' AND (Date_Discharge IS NULL)AND(DATEDIFF(d, Date_Admission, GETDATE()) 211)AND (DATEDIFF(d, Date_Admission, GETDATE()) < 364)AND pa.OP__DOCID NOT in (Select Admit_DOCID from FD__UR_Randomswhere SelectType < 3 AND RecordChosen= 'T'))ENDSELECT @CurrRec = (Select Count(*) FROM FD__UR_Randoms whereSelectType = @CodeRevType and SelectDate = @TodayNoTime)--*************************************STEP B Pick the necessarypercentage **************************************--if code type = 1, 15% otherwise 25%If @CodeRevType = 1BEGINSELECT @RequFiles = (@CurrRec * .15)ENDELSEBEGINSELECT @RequFiles = (@CurrRec * .25)END--make sure we have at least 5If @RequFiles < 5BEGINSELECT @RequFiles = 5End--*************************************STEP C Randomly select thatmany files**************************************--convert all variables to stringsSELECT @RequFilesSt = Convert(Varchar(100),@RequFiles)SELECT @CodeRevTypeSt = Convert(Char(1),@CodeRevType)SELECT @TodaySt = Convert(VarChar(10),@TodayNoTime,101)SELECT @SQLString = N'INSERT INTO TMP_UR_Randoms(Admit_DOCID,Client_ID, SelectDate, SelectType,RecordChosen)'SELECT @SQLString = @SQLString + N'(SELECT TOP ' + @RequFilesST + 'Admit_DOCID, Client_ID, SelectDate, SelectType, RecordChosen FROMFD__UR_Randoms 'SELECT @SQLString = @SQLString + N'WHERE SelectType = ' +@CodeRevTypeSt + ' AND SelectDate = ''' + @TodaySt + '''' + ' ORDERBY NEWID())'print @SQLStringexecute sp_executesql @SQLStringSELECT * FROM TMP_UR_Randoms/*--This select statement gives me what i want but I need to somehowmark these records and/or move this subset into the temp tableSelect Top @RequFilesFROM FD__UR_RandomsWHERE SelectType = @CodeRevType and SelectDate =Convert(varchar(10),GetDate(),101))ORDER BY NewID()*/ENDTHIS:GO
View 3 Replies View RelatedI'm at a bit of a loss here. My T-SQL skills are not up to the task at hand here :(
I've got company records in one table and SIC codes that correlate to the companies linked by the company ID. So, I can run the query, but the output I get is multiple records for each company, because some companies have multiple SIC codes associated with them.
I understand how to get only one record, but what I want to do is create a result set that has all the SIC codes associated with one company. Possibly in a comma seperated list, that would count as one field.
Anyone have any idea how to bring back all the SIC codes for one company as one variable (or multiple variables, but in one record)?
Thank you very much for any help,
Mischa
I have one table1 which contain data like this
ID --- year --- months
1 --- 2015 --- null
table2 like this
ID --- Year --- months
1 --- 2015 --- 6
1 --- 2015 --- 7
I want to update the max value of month for id 1,which is 7 using update function, How can i do this.
Take a table, where not all the columns are populated:CREATE TABLE #T (A int, B int, C int, D int)INSERT #T (A,B) VALUES (1,2)INSERT #T (A,B) VALUES (3,4)INSERT #T (A,B) VALUES (5,6)INSERT #T (A,B) VALUES (7,8)INSERT #T (A,B) VALUES (9,10)The values for C and D can be computed as functions of A and B. For thisexample, let's say they are twice A and three times B, respectively:CREATE FUNCTION dbo.F(@A int,@B int)RETURNS @Tbl TABLE (X int, Y int)AS BEGININSERT @Tbl (X,Y) VALUES (@A*2, @B*3)RETURNENDNow we use the function to compute the other columns:UPDATE #T SET C=X, D=YFROM dbo.F(A,B)Right? Well, no. Instead, I get this message:Server: Msg 155, Level 15, State 1, Line 2'A' is not a recognized OPTIMIZER LOCK HINTS option.Any suggestions? I would like to use this structure, if possible.Jim GeissmanCountrywide Home Loans
View 7 Replies View Related
I have been trying to get a function to UPDATE a row in the database. I know that this is not supported but by using code from a previous post this can be accomplished [See my code below].
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1863258&SiteID=1
Code Snippet
[SqlFunction(DataAccess = DataAccessKind.Read)]
public static int GetNextValue(string dbName)
{
int rows = 0;
lock (obj)
{
SqlPipe sp = SqlContext.Pipe;
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
SqlCommand cmd = new SqlCommand("SELECT id FROM Items", conn);
conn.Open();
rows = int.Parse(cmd.ExecuteScalar().ToString()) + 1;
using (SqlConnection updateCon = new SqlConnection("Data Source=" + Environment.MachineName + ";Initial Catalog=" + dbName + ";Integrated Security=True;Pooling=False"))
{
SqlCommand updateCmd = new SqlCommand("UPDATE Items SET id=" + rows,updateCon);
updateCon.Open();
updateCmd.ExecuteScalar();
updateCmd.Dispose();
}
return rows;
}
}
I hope this is helpful.
Regards
tribal
Does SQL have a function that return "null" for records which don't exist? Per example in a FK relation ship, that not all records in the first table have a "child" in the second table, so it returns null records.
Thank you very much.