w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
MySQL: Insert into one table if not exists; if it does, drop it and insert it into another table
I will answer this without actual code because I am in a hurry. You already have this mostly setup and I like your layout. Try to insert into friendReq and get the number of rows inserted. If one row inserted then done, if 0 rows inserted, then delete opposite friend request and insert into friends. This solution is not 100% mysql but will take php as well.

Categories : Mysql

Select data from one table and insert into another existing table, which doesn't exist in the table
I think you are looking for this INSERT INTO table4( id1, id3) SELECT id1, id3 FROM table2 INNER JOIN table1 ON table1.id2 = table2.id2 Left JOIN table3 ON table2.name2 = table3.name3 WHERE name2 LIKE 'input' and table3.name3 is null or something similar. Left (outer join) gets all the records in table2 whether they exist or not. If they don't table3.name3 will be null, so those are the chaps you want.

Categories : Mysql

SQL Insert from table to table prevented by duplicate primary key from source table
You'll need to declare the columns except the ID on both the INSERT and the SELECT, since the number of fields need to match, and id (as you noticed) can't be inserted as is into the destination table. INSERT INTO DestTable (field1, field2, field3) SELECT field1, field2, field3 FROM SourceTable; An SQLfiddle to test with. EDIT: You could do it in a bit more hacky way to simplify the insert. You can create a trigger that simply forces the primary key to NULL on insert. CREATE TRIGGER t_DT BEFORE INSERT ON DestTable FOR EACH ROW SET NEW.id = NULL; then a copy from table to table can be done as simply; INSERT INTO DestTable SELECT * FROM SourceTable; Another SQLfiddle.

Categories : Mysql

Does INSERT IGNORE lock the table even if it ignores the insert?
It depends on the engine - MyIsam and InnoDb behave diferrently. For MyIsam tables, if a record already exist in the table (even if it is not commited yet), a regular INSERT of the same record (the same unique key) in the other session reports a duplctate key error - so INSERT IGNORE just ignores the error and proceed further. On InnoDB table, if the record is not locked, the regular INSERT will immediately report the duplicate key error (INSERT IGNORE will skip the error and go on). But if the record is locked by the other session (for example the record is inserted but not commited yet, or the record is locked by an UPDATE or DELETE or SELECT FOR UPDATE command), the INSERT command will "hang" and will be waiting until the other session will remove the lock (by COMMIT or ROLLBACK). Then

Categories : Mysql

before insert trigger for insert duplicate rows into another table
As far as you trigger concerned there are several problems: you don't have ; after insert statement IF statement should end with END IF and a semicolon, not just END you have to change a delimiter with DELIMITER command use EXISTS() rather then COUNT() That being said your trigger might look like DELIMITER $$ CREATE TRIGGER tblspmaster_noduplicate BEFORE INSERT ON tblspmaster FOR EACH ROW BEGIN IF (EXISTS(SELECT * FROM tblspmaster WHERE sp = NEW.sp)) THEN INSERT INTO tblspduplicate (sp,FileImported,AMZFileName) VALUES (NEW.sp, NEW.FileImported, NEW.AMZFileName); END IF; END$$ DELIMITER ; Here is SQLFiddle demo Use IGNORE clause in your LOAD DATA INFILE statement. MySql will treat errors (violating unique constraint) as warnings effectively discarding duplicates.

Categories : Mysql

How to get the last insert id from a table with auto_increment that is not part of the last insert in the transaction?
In the second step you describe, you insert multiple records into Evaluation_Details using the last_insert_id() from the Evaluation table insert. Since you have this id, you can store it in a variable and use it to perform your last query

Categories : Mysql

Select from a table and insert to another table and deleting it from the source table
Have a look at: MySQL Trigger - delete after update What you want is to trigger a delete after your insert. DELIMITER $$ CREATE TRIGGER trigger1 AFTER INSERT ON tbl_friends FOR EACH ROW BEGIN DELETE FROM tbl_requests WHERE ID = new.id; END $$

Categories : Mysql

Insert Returning last ID with PDO
Your looking for this, if you cannot get lastInsertId to work, this will do the job, a couple of extra lines tho: foreach ($stmt as $row) { $sheet_id_post = $row[0]; echo $sheet_id_post; } You can always msg me. Or head to php website look for a similar loop and alter it, my answer is very similar to yours and when I first did this I just went to php.net :-)

Categories : SQL

trigger mysql on insert, insert pk in other table
If the table prices_history has foreign key on product, the record on product must exists before storing the corresponding prices_history Maybe your trigger should be fired AFTER INSERT, not BEFORE INSERT ?

Categories : Mysql

ASP function not returning mssql insert id
You are assigning the function to the actual Field object, which gets invalid when you're getting out of the scope it was declared in. This should work fine: sqlInsert = CStr(rs("NewID").Value) And also close the recordset afterwards and Set to Nothing.

Categories : Sql Server

C++ Safe returning a pointer to an iterater after map insert?
Well I'd say that depends on is your map alive longer than anything that could use (and store) the pointer. If yes, (ie, it's in some sort of singleton), you could go with it, but not very safe anyway since any code could delete the pointer. The best option is to store boost::shared_ptr (or std:: since c++11) instead of raw pointers in your mapn and return only a weak_ptr after insertion. That way, you're sure no other code can delete your pointer as long as the map holds it, and that no one can use a pointer that has been erased from the map.

Categories : C++

why does returning a map.insert().second introduce unreachable memory?
std::map::insert() is a no-op if the key already exists in the map. If you try to insert a duplicate key, the first version of your code will leak the object it has allocated using new. The second version does not have this problem since you don't call new unless you've established that the key doesn't exist in the map. Two good ways to fix the leak are: store the objects by value; store smart pointers to objects.

Categories : C++

SQL Server : Insert Multiple Duplicate Records into Child table from Parent table
declare @Product table(Sno int, Productid char(3)) declare @ProductImages Table(Sno int identity(1,1), ProductId char(3), ImageId varchar(20)) insert @Product values(1, 'ABC'),(2, 'XYZ'),(3, 'PQR') insert @ProductImages (ProductId, ImageId) select p.ProductId,p.ProductId+ '_' +a.plus from @Product p cross apply (values ('Small'), ('Big'), ('Biggest')) a(plus) select * from @ProductImages EDIT for sql 2000 insert @Product values(1, 'ABC') insert @Product values(2, 'XYZ') insert @Product values(3, 'PQR') insert @ProductImages (ProductId, ImageId) select p.ProductId,p.ProductId+ '_' +a.plus from @Product p join (select 'Small' plus union all select 'Big' union all select 'Biggest') a(plus) on 1 = 1 Result: Sno ProductId ImageId 1 ABC ABC_Small 2 ABC ABC_Big 3 AB

Categories : Sql Server

Insert record into transaction table for each column value change of my user table in SQL
Assuming you're using ASP.NET Web Forms. In your .aspx page TextBox1: <asp:TextBox runat="server" id="TextBox1" /><br /> TextBox2: <asp:TextBox runat="server" id="TextBox2" /><br /> <asp:Button runat="server" id="Button1" OnClick="Button1_Click" Text="Submit" /> In your .aspx.cs page protected void Button1_Click(object sender, EventArgs e) { string original_text=GetOriginalTextOfTextBox1(); if(TextBox1.Text==original_text) { //the text didn't change } else { //the text changed. need to update the transaction table and the user table. } string originaltext2=GetOriginalTextOfTextBox2(); if(TextBox2.Text==originaltext2) { //the text didn't change } e

Categories : C#

PL/pgSQL - insert multiple rows, returning the ids in a resultset
Updateable CTE is supported from PostgreSQL 9.1. you don't need use CTE (in your case) postgres=# CREATE OR REPLACE FUNCTION fx() RETURNS SETOF int AS $$ BEGIN RETURN QUERY INSERT INTO taba(a) VALUES(1),(2) RETURNING *; RETURN; END; $$ LANGUAGE plpgsql; CREATE FUNCTION postgres=# select * from fx(); fx ---- 1 2 (2 rows)

Categories : Postgresql

build dynamic sql insert query to merge multiple tables into single table using mapping table
Tim I dont think you need the mapping table at all I would use a series of UNION queries. One query per source. In the SELECT clause for each of those queries you should have a 1:1 column for each coulmn in your destination table, including mapping the value NULL where that destination column is not applicable to that source. (This is where the mapping really happens) Then your INSERT statement is a straight-forward mapping of all the UNIONED queries into the destination table. Pseudo code INSERT INTO Destination(Col1, Col2, Col3, Col4) SELECT T1.Field1 as Col1 ,NULL as Col2 ,T1.Field7 as Col3 ,T1.Field2 as Col4 FROM FirstSource T1 UNION SELECT T2.Field1 as Col1 ,Some Calulation as Col2 ,T2.Field3 as Col3 ,NULL as Col4 FROM FirstS

Categories : Mysql

Insert results of filtered table back into original table
Instead of using Filtered_table, can you not just use delete t from #temp_table t INNER JOIN FILTER ON t.ID = FILTER.ID_R WHERE NOT (FILTER.FIELD1 = [parameter1 from loop] AND FILTER.FIELD2 = [parameter2 from loop])

Categories : Sql Server

how to insert data in 1 table and update another table in a sql database using linq
One problem seems to be that you're not referencing the correct Incident record from the database. You can query the database first to get the record you want to update. If you want to do them both at the 'same time' (i.e. in the same transaction), remove the first db.SaveChanges(). Then the second one will do the insert and update at together. Also, DateTime.Now can return a different value every time it's called. If you want the time stamps to match on each record, simply call DateTime.Now once, save the result to a variable, and use it in both places. Something like this: Context db = new Context(); var now = DateTime.Now; IncidentAction act = new IncidentAction { IncidentID = ID, ... LastUpdated = DateTime.Now, }; db.IncidentActions.Add(act); Incident inc = db.Inci

Categories : C#

Populate a table with random set of rows from another table on insert using a trigger
Are you looking for this? CREATE TRIGGER tg_survey_after_insert AFTER INSERT ON survey FOR EACH ROW INSERT INTO users_of_a_survey (codsurvey, coduser) SELECT NEW.cod, cod FROM users WHERE interests = NEW.target_interests ORDER BY RAND() LIMIT 1000 ; Insert a few rows INSERT INTO survey VALUES('SURV001', 'music'); INSERT INTO survey VALUES('SURV002', 'music'); Content of users_of_a_survey after insert | CODSURVEY | CODUSER | ----------------------- | SURV001 | 3 | | SURV001 | 1 | ..................... | SURV002 | 1 | | SURV002 | 4 | ...................... Here is SQLFiddle demo. UPDATE: And if i would like to do the inverse? Delete all users from "users_of_a_survey" when I delete their survey? You can either go with a BE

Categories : Mysql

Insert data into temp table, multiple columns from one Table
The simplest way to do it in code (although it may not perform well with large data sets) is to do something like: SELECT [Date], x = MAX(CASE WHEN ID = 1 THEN PRICE END) , y = MAX(CASE WHEN ID = 3 THEN PRICE END) INTO #tmp FROM Top40 GROUP BY [Date]

Categories : SQL

Delete from source table then insert to target table if not exists
Composable DML is quite limited. You can do this if you change the definition of #Target though CREATE TABLE #Target ( column01 VARCHAR(100), employee_number VARCHAR(10) PRIMARY KEY WITH (IGNORE_DUP_KEY=ON) ) INSERT INTO #Target (column01, employee_number) VALUES ('2', '222') CREATE TABLE #Srs ( column01 VARCHAR(100), employee_number VARCHAR(10) ) INSERT INTO #Srs (column01, employee_number) VALUES ('1', '111'), ('2', '222'), ('3', '333'), ('4', '444'); WITH cteTable AS (SELECT column01, employee_number FROM #Srs) INSERT INTO #Target (column01, employee_number) SELECT * from (De

Categories : Sql Server

MySQL insert into table with auto-increment while selecting from another table
You need to explicitly list the columns in the insert statement: insert into rt_table (rt_user_id, rt_user_name, source_user_id, source_user_name, tweet_id) select rt_user_id, (select user_name from users u where u.user_id = t.rt_user_id), source_user_id, (select user_name from users u where u.user_id = t.source_user_id), tweet_id from tweets t where rt_user_id != -1; Also, I think it is better form to use explicit joins, rather than nested selects: insert into rt_table (rt_user_id, rt_user_name, source_user_id, source_user_name, tweet_id) select t.rt_user_id, u.user_name, t.source_user_id, su.user_name, t.tweet_id from tweets t left outer join users u on t.rt_user_id = u.user_id left outer join users su on t.source_user_id =

Categories : Mysql

Need to insert rows into table based on information pulled from table
I'm assuming that the source data in table B is all in one row, so your fields are customer_id, sold, purc, exch. I'm also assuming that you do want to populate table A with this data. If your table B data is in multiple rows, let me know and I'll revise this. If you just want to write a query and not populate the new table, remove the outer SELECT ... INTO statement. SELECT customer_id, trans_type, trans_status INTO A FROM ( SELECT customer_id , "SOLD" AS trans_type , SWITCH (sold = 0, "FALSE", sold > 0, "TRUE") AS trans_status FROM B UNION ALL SELECT customer_id , "PURCHASED" AS trans_type , SWITCH (purc = 0, "FALSE", purc > 0, "TRUE") AS trans_status FROM B UNION ALL SELECT customer_id

Categories : SQL

MYSQL Trigger - update values in table from other table before insert
You have the sequence of assignments: SET OP_VAR = NEW.fk_postcode; . . . SET NEW.fk_postcode = NP_VAR; The column new.fk_postcode has the same type. But the columns are, respectively, text and integer. You need to fix the type incompatibility. My guess is that fk_postcode is actually an integer and separate from the value (which would be a string).

Categories : Mysql

Hibernate one to one mapping annotation insert exception (While inserting into transnational table, master table data should not change)
The error occurs because there are 2 transactions working on the same record. If a record is read by 2 transactions, and if the record is saved by one transaction first, an optimistic locking exception is thrown in the second transaction, because the system assumes that nobody else is going to modify the record. Are you using multithreading in batch mode? There are different solutions: Make sure that records that are going to be updated, are touched by only a single transaction at any given moment Retry the transaction: Make sure that no non-transactional state is kept between the retries.

Categories : Java

Insert new table information in an existing mysql table?
Try LOAD DATA INFILE LOAD DATA INFILE '/path/to/your/file.csv' INTO TABLE ref_barangay FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY ' ' -- or IGNORE 1 LINES (@code, @name) SET brgy_name = @name, brgy_code = @code Output mysql> select * from ref_barangay; +----+--------------+-----------+ | id | brgy_name | brgy_code | +----+--------------+-----------+ | 1 | Bangued | 1001 | | 2 | Boliney | 1002 | | 3 | Bucay | 1003 | | 4 | Bucloc | 1004 | ...

Categories : Mysql

insert all rows from table into other table, single query
Have a look st INSERT ... SELECT Syntax With INSERT ... SELECT, you can quickly insert many rows into a table from one or many tables. Syntax INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)] SELECT ... [ ON DUPLICATE KEY UPDATE col_name=expr, ... ] Example INSERT INTO tbl_temp2 (fld_id) SELECT tbl_temp1.fld_order_id FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100; SQL Fiddle DEMO

Categories : Mysql

alter table statment to insert duplicate into another table
I believe you want to use a trigger for this. Here is the MySQL reference chapter on triggers. Use a before insert trigger. In the trigger, check if the row is a duplicate (maybe count(*) where key column value = value to be inserted). If the row is a duplicate, perform an insert into your secondary table.

Categories : Mysql

Insert object into table, and have table populated by it's properties
Unless you start using Entity Framework, you'll have to join your tables with SQL commands and traverse them the hard way to populate your List for each BHA object. Example here: http://stackoverflow.com/a/6918201/1293496

Categories : C#

Insert VB.net Data Table into a Visual Foxpro table
You appear to be on the right track. A few minor issues. The connection string should have the provider as Provider=vfpoledb.1; The Data Source component of the string should point to the physical directory where the .dbf files are located and NOT the specific .DBF file itself. Data Source=Z:YourShareOnServerSomePath The query itself looks ok as you have it coded. HOWEVER, when you DO get more into .net select, insert, update, delete, you will DEFINITELY need to look into using PARAMETERIZED queries to prevent SQL-Injection (even though not practical with VFP since it would just choke on comment / hack attempts), but is still good to be in the practice. Build your Command and have "?" as a place-holder for the "parameters", then add parameters in the same order as your "?" placeme

Categories : Vb.Net

How to insert data from one table to another table dynamically without using Triggers?
You can use a multitable INSERT statement insert all into table1 (col1, col2, col4) values (col1, col2, col4) into table2 (col1, col2, col3, col4) values (col1, col2, col3, col4) select col1, col2, col3, col4 from some_other_table

Categories : Oracle

INSERT INTO query returning "Unclosed quotation mark after the character string"
I'm guessing one of the strings you're trying to insert has an apostrophe in it. Depending on how you're creating the query you can use parameters or escape the apostrophe: INSERT INTO t_lu_Product ( ... ) VALUES(... ,'Mother''s Cookies',1);

Categories : SQL

mysql - insert into one table from another with conditions from a third table
Then add tableC after From keyword and use tableB.* instead of * INSERT INTO tableA SELECT tableB.* FROM tableB,tableC WHERE tableB.id=tableC.id;

Categories : Mysql

How to insert data from one table to anther table?
Just INSERT using named columns, and skip the identity column - it will be filled automatically: INSERT INTO tblA (num, col1, col2, col3) SELECT @Num, col1, col2, col3 FROM tblB

Categories : Sql Server

How can i create(add) a new row in an existing table AND insert a row in another table?
You need to do this with dynamic SQL, I think. CREATE PROCEDURE _InsertAttbts @Attribute VARCHAR(100) AS BEGIN IF NOT EXISTS (SELECT * FROM dbo.Attributes WHERE Attribute = @Attribute) BEGIN INSERT INTO dbo.Attributes(Attribute) VALUES(@Attribute); declare @sql nvarchar(max) = 'ALTER TABLE PRODUCTS ADD '+@Attribute+' varchar(100)'; exec sp_executesql @sql; END; END; I have no idea why you want to do this. You can get the list of columns using INFORMATION_SCHEMA.COLUMNS or one of the system tables.

Categories : Sql Server

How do I insert into a table from another table by matching on values?
whelp, messing around and I've managed to answer my own question. First, it's supposed to be an update (duh); second: update books set author_id = (select author_id from authors where books.author = authors.author_name);

Categories : SQL

Insert into another table where the columns exist in table 2
You can set default value for col 4 & col 5 to NULL and then run this query : INSERT INTO table1 (col1, col2, col3) SELECT col1, col2, col3 FROM table2; It should work but you have to explicitly specify the columns in common between the two tables.

Categories : PHP

Right Approach to Insert in Table then copy it to another table
Simplify. Most notably, as discussed in your other, related question, there is absolutely no safety in going out and seeing what the current MAX is, adding 1, and assuming that will be the next identity value generated. You can only rely on that number if you retrieve it after you have inserted, and the most reliable way to do that is to use SCOPE_IDENTITY() (or, for a multiple-row INSERT, an OUTPUT clause). CREATE PROCEDURE dbo.InsertBook @BookTitle NVARCHAR(256), @Copies INT AS BEGIN SET NOCOUNT ON; DECLARE @BookTitleID INT; INSERT dbo.BookTitles(BookTitle, Copies) SELECT @BookTitle, @Copies; SELECT @BookTitleID = SCOPE_IDENTITY(); INSERT dbo.Books(BookTitleID) SELECT @BookTitleID FROM (SELECT TOP (@Copies) rn = ROW_NUMBER() OVER (ORDER BY [object_id]) FRO

Categories : C#

Insert into a table values from another table using Oracle
I don't think it can be done with a single query. You have to write a PLSQL block for it. First list out all unique ids from table 1 Iterate it through a for-each and insert corresponding u_id in successive columns in table 2 Just a theoretical explanation.

Categories : SQL

Insert into a table with a select from another table + my own data
The proper syntax is: Insert into table1 (id,type,country,name,description,path) select id, type, country, 'My Name', 'MyDescription', 'My Path' from table2 where id = 1; The values and select syntax do not mix. To be honest, I never use values because select does everything it does, and more.

Categories : Mysql



© Copyright 2017 w3hello.com Publishing Limited. All rights reserved.